Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3776.700.51\))
Subject: Re: re-novice coming back to pgsql: porting an SQLite update
 statement to postgres
From: Alban Hertroys <haramrae@gmail.com>
In-Reply-To: <93c6b90cd39ae5fdf49cbd698e8de0c6b6f9bf3f.camel@kortschak.io>
Date: Sun, 15 Sep 2024 13:22:41 +0200
Cc: pgsql-general@lists.postgresql.org
Content-Transfer-Encoding: quoted-printable
Message-Id: <90CC4567-4C9E-4F83-902C-A3CE5C3C74E5@gmail.com>
References: <1aea530e4c96109b91fdde53a29e38beb625dc77.camel@kortschak.io>
 <20240723221121.51f2b3872d0ebfc36a6fa8ff@wanadoo.fr>
 <2f11b2ba-3182-492a-ab46-23cfa5ec913c@aklaver.com>
 <CAFCRh-8d-gsERdu-6W2fb0d6HLi8_bPkS7zVqm0J4ZakpvJQYw@mail.gmail.com>
 <735913e4c9601a2a7ad57d2253d74451c42ba899.camel@kortschak.io>
 <21078220628bcebca0395a52d42b558ed965bbc0.camel@kortschak.io>
 <5c86256a4e83b1af73da22faab54889564e93129.camel@kortschak.io>
 <7320B905-E950-4035-9A13-8C9447AB52EC@gmail.com>
 <48019d21fd7c669afb83161337f7e007ae619534.camel@kortschak.io>
 <93c6b90cd39ae5fdf49cbd698e8de0c6b6f9bf3f.camel@kortschak.io>
To: Dan Kortschak <dan+pgsql@kortschak.io>
Archived-At: <https://www.postgresql.org/message-id/90CC4567-4C9E-4F83-902C-A3CE5C3C74E5%40gmail.com>
Precedence: bulk


> On 15 Sep 2024, at 11:07, Dan Kortschak <dan+pgsql@kortschak.io> =
wrote:
>=20
> I have come to hopefully my last stumbling point.
>=20
> I am unable to see a way to express something like this SQLite syntax
>=20
> 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=E2=80=99s the result of that query in SQLite?

I=E2=80=99m 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 =E2=80=9Cc=E2=80=9D 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              =
                                    =20
=
--------------------------------------------------------------------------=
------------------------------------
 [{"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=E2=80=99t need to cast the values all the time either.


The replacement approach gets a bit trickier. I don=E2=80=99t see any =
good method that would replace both =E2=80=98a=E2=80=99 and =E2=80=98b=E2=80=
=99 values if they both go outside bounds in the same object.=20

The jsonb_set function in PG doesn=E2=80=99t 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 =E2=80=98a=E2=
=80=99 or =E2=80=98b=E2=80=99. I did include a case where both =E2=80=98a=E2=
=80=99 and =E2=80=98b=E2=80=99 go out of bounds, replacing both values =
with there respective replacements, but the syntax for that approach =
doesn=E2=80=99t 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               =
                                    =20
=
--------------------------------------------------------------------------=
------------------------------------
 [{"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=E2=80=99re not familiar with dollar quoting:
=
https://www.postgresql.org/docs/16/sql-syntax-lexical.html#SQL-SYNTAX-DOLL=
AR-QUOTING


Alban Hertroys
--
There is always an exception to always.