Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres

On Sun, Sep 15, 2024 at 4:23 AM Alban Hertroys <haramrae(at)gmail(dot)com> wrote:
>
> > On 15 Sep 2024, at 11:07, Dan Kortschak <dan+pgsql(at)kortschak(dot)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 don’t 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 = 'a'
THEN least(old_value::numeric, 2)::text::jsonb
WHEN key = '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-GREATEST-LEAST
https://www.postgresql.org/docs/16/queries-table-expressions.html#QUERIES-TABLEFUNCTIONS