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
