MIME-Version: 1.0
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> <90CC4567-4C9E-4F83-902C-A3CE5C3C74E5@gmail.com>
In-Reply-To: <90CC4567-4C9E-4F83-902C-A3CE5C3C74E5@gmail.com>
From: Willow Chargin <postgresql@wchargin.com>
Date: Sun, 15 Sep 2024 10:13:32 -0700
Message-ID: <CAALRJs65VbrcRhunsHrSn3gMnvVQ8gHc2O9Txz1RUEg6iSJo8A@mail.gmail.com>
Subject: Re: re-novice coming back to pgsql: porting an SQLite update
 statement to postgres
To: Alban Hertroys <haramrae@gmail.com>
Cc: Dan Kortschak <dan+pgsql@kortschak.io>, pgsql-general@lists.postgresql.org
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
Archived-At: <https://www.postgresql.org/message-id/CAALRJs65VbrcRhunsHrSn3gMnvVQ8gHc2O9Txz1RUEg6iSJo8A%40mail.gmail.com>
Precedence: bulk

On Sun, Sep 15, 2024 at 4:23=E2=80=AFAM Alban Hertroys <haramrae@gmail.com>=
 wrote:
>
> > On 15 Sep 2024, at 11:07, Dan Kortschak <dan+pgsql@kortschak.io> 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}]');
>
> [...]
>
> I see basically two approaches. One is to take the objects apart [...]
>
> 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)
> ;
>
> [...]
>
> The drawback is that you have to specify all fields and types, but you do=
n=E2=80=99t need to cast the values all the time either.

Here is a variant of Alban's first method that does not require
specifying all fields and types, and so works with heterogeneous values:

    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(new_element ORDER BY idx) new_arr
        FROM t, LATERAL (
            SELECT idx, jsonb_object_agg(key, CASE
                WHEN key =3D 'a'
                    THEN least(old_value::numeric, 2)::text::jsonb
                WHEN key =3D 'b'
                    THEN greatest(old_value::numeric, -2)::text::jsonb
                ELSE old_value
            END)
            FROM
                jsonb_array_elements(arr)
                    WITH ORDINALITY old_elements(old_element, idx),
                jsonb_each(old_element) each(key, old_value)
            GROUP BY idx
        ) new_elements(idx, new_element)

I also took the liberties of using `least` / `greatest` to simplify the
clamping operations, and using `WITH ORDINALITY` / `ORDER BY` on the
array scan and re-aggregation to make the element ordering explicit
rather than relying on the query engine to not re-order the rows.

https://www.postgresql.org/docs/16/functions-conditional.html#FUNCTIONS-GRE=
ATEST-LEAST
https://www.postgresql.org/docs/16/queries-table-expressions.html#QUERIES-T=
ABLEFUNCTIONS