> On 15 Sep 2024, at 11:07, Dan Kortschak <[email protected]> wrote:
>
> 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}]');
What’s the result of that query in SQLite?
I’m guessing it would be: [{"a":1, "b":-2},{"a":2, "b":-2},{"a":2, "b":-1}]
I see basically two approaches. One is to take the objects apart and build them
back together again, the other is to attempt to only replace the values that
need replacing.
For the sake of showing how both approaches modify the original, I added an
extra field “c” to your objects that should be in the result unmodified.
The first approach rebuilds the objects:
with t as (
select jsonb($$[{"a":1, "b":-3, "c":1},{"a":2, "b":-2, "c":2},{"a":3,
"b":-1, "c":3},{"a":3, "b":-3, "c":4}]$$) arr
)
select jsonb_agg(jsonb_build_object(
'a', case when records.a > 2 then 2 else records.a end
, 'b', case when records.b < -2 then -2 else records.b end
, 'c', c
))
from t
cross join lateral jsonb_to_recordset(t.arr) records(a int, b int, c int)
;
jsonb_agg
--------------------------------------------------------------------------------------------------------------
[{"a": 1, "b": -2, "c": 1}, {"a": 2, "b": -2, "c": 2}, {"a": 2, "b": -1, "c":
3}, {"a": 2, "b": -2, "c": 4}]
(1 row)
The drawback is that you have to specify all fields and types, but you don’t
need to cast the values all the time either.
The replacement approach gets a bit trickier. I don’t see any good method that
would replace both ‘a’ and ‘b’ values if they both go outside bounds in the
same object.
The jsonb_set function in PG doesn’t seem to be able to handle setting a value
conditionally, let alone, setting multiple values conditionally in one call, so
I ended up with replacing either ‘a’ or ‘b’. I did include a case where both
‘a’ and ‘b’ go out of bounds, replacing both values with there respective
replacements, but the syntax for that approach doesn’t scale well to more
combinations of fields and boundaries to check and replace.
Hence I added the problematic case to the test string. As you can see from the
previous query, that one does handle that case properly without much extra
hassle.
with t as (
select jsonb($$[{"a":1, "b":-3, "c":1},{"a":2, "b":-2, "c":2},{"a":3,
"b":-1, "c":3},{"a":3, "b":-3, "c":4}]$$) arr
)
select jsonb_agg(
case
when (obj->>'a')::INTEGER > 2 and (obj->>'b')::INTEGER < -2
then jsonb_set(jsonb_set(obj, '{a}', '2') ,'{b}', '-2')
when (obj->>'a')::INTEGER > 2
then jsonb_set(obj, '{a}', '2')
when (obj->>'b')::INTEGER < -2
then jsonb_set(obj, '{b}', '-2')
else obj
end) newArr
from (
select jsonb_array_elements(arr) obj from t
) elements;
newarr
--------------------------------------------------------------------------------------------------------------
[{"a": 1, "b": -2, "c": 1}, {"a": 2, "b": -2, "c": 2}, {"a": 2, "b": -1, "c":
3}, {"a": 2, "b": -2, "c": 4}]
(1 row)
For understanding both queries better, it probably helps to take out the
jsonb_agg calls to see the separate objects from the array. Add the original
obj back in for comparison, if you like.
I typically use the documentation pages for the JSON functions and the one on
aggregate functions, where the JSONB aggregates are located:
https://www.postgresql.org/docs/16/functions-json.html
https://www.postgresql.org/docs/16/functions-aggregate.html
And if you’re not familiar with dollar quoting:
https://www.postgresql.org/docs/16/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING
Alban Hertroys
--
There is always an exception to always.
