From: | Dan Kortschak <dan+pgsql(at)kortschak(dot)io> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres |
Date: | 2024-09-15 09:07:53 |
Message-ID: | 93c6b90cd39ae5fdf49cbd698e8de0c6b6f9bf3f.camel@kortschak.io |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I have come to hopefully my last stumbling point.
I am unable to see a way to express something like this SQLite syntax
select json_group_array(json_replace(value,
'$.a', case
when json_extract(value, '$.a') > 2 then
2
else
json_extract(value, '$.a')
end,
'$.b', case
when json_extract(value, '$.b') < -2 then
-2
else
json_extract(value, '$.b')
end
))
from
json_each('[{"a":1, "b":-3},{"a":2, "b":-2},{"a":3, "b":-1}]');
(in the repro above, the values are integers, but in the real case,
they are timestamps)
I have worked on multiple statements around the theme of
with t as (
select jsonb($$[{"a":1, "b":-3},{"a":2, "b":-2},{"a":3, "b":-1}]$$)
arr
)
select
jsonb_array_elements(arr) as arr
from
t;
The closest that I have come is
with t as (
select jsonb($$[{"a":1, "b":-3},{"a":2, "b":-2},{"a":3, "b":-1}]$$)
arr
)
select jsonb_set(arr, '{a}', case
when (arr->>'a')::INTEGER > 2 then
2
else
(arr->>'a')::INTEGER
end
)
from (
select
jsonb_array_elements(arr) as arr
from
t
) elements;
but this is a millions miles from where I want to be (it doesn't work,
but I think the shape of the things that it's working with are maybe
heading in the right direction). I've read through the docs, but I just
don't seem able to get my head around this.
Any help would be greatful appreciated (also some reading direction so
that I'm not floundering so much).
thanks
From | Date | Subject | |
---|---|---|---|
Next Message | Alban Hertroys | 2024-09-15 11:22:41 | Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres |
Previous Message | Juan Rodrigo Alejandro Burgos Mella | 2024-09-15 02:51:49 | Re: update faster way |