public inbox for [email protected]
help / color / mirror / Atom feedRe: re-novice coming back to pgsql: porting an SQLite update statement to postgres
4+ messages / 3 participants
[nested] [flat]
* Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres
@ 2024-09-15 11:22 Alban Hertroys <[email protected]>
2024-09-15 17:13 ` Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres Willow Chargin <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Alban Hertroys @ 2024-09-15 11:22 UTC (permalink / raw)
To: Dan Kortschak <[email protected]>; +Cc: [email protected]
> 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres
2024-09-15 11:22 Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres Alban Hertroys <[email protected]>
@ 2024-09-15 17:13 ` Willow Chargin <[email protected]>
2024-09-21 03:33 ` Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres Dan Kortschak <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Willow Chargin @ 2024-09-15 17:13 UTC (permalink / raw)
To: Alban Hertroys <[email protected]>; +Cc: Dan Kortschak <[email protected]>; [email protected]
On Sun, Sep 15, 2024 at 4:23 AM Alban Hertroys <[email protected]> wrote:
>
> > 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}]');
>
> [...]
>
> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres
2024-09-15 11:22 Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres Alban Hertroys <[email protected]>
2024-09-15 17:13 ` Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres Willow Chargin <[email protected]>
@ 2024-09-21 03:33 ` Dan Kortschak <[email protected]>
2024-09-21 06:52 ` Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres Dan Kortschak <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Dan Kortschak @ 2024-09-21 03:33 UTC (permalink / raw)
To: Willow Chargin <[email protected]>; Alban Hertroys <[email protected]>; +Cc: [email protected]
Thank you both. This has been extremely helpful. I still have more work
to do but this has made it possible to start playing with something,
and reading about it when it doesn't work.
On Sun, 2024-09-15 at 10:13 -0700, Willow Chargin wrote:
> On Sun, Sep 15, 2024 at 4:23 AM Alban Hertroys <[email protected]>
> wrote:
> >
> > 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
I found that I needed to old_value::text::numeric to get Willow's code
to work, but I imagine this is due to the ancientness of the postgresql
I am using.
thanks
Dan
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres
2024-09-15 11:22 Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres Alban Hertroys <[email protected]>
2024-09-15 17:13 ` Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres Willow Chargin <[email protected]>
2024-09-21 03:33 ` Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres Dan Kortschak <[email protected]>
@ 2024-09-21 06:52 ` Dan Kortschak <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Dan Kortschak @ 2024-09-21 06:52 UTC (permalink / raw)
To: Willow Chargin <[email protected]>; Alban Hertroys <[email protected]>; +Cc: [email protected]
This is the toy with the shape of data that will be seen in the
application. The final trick was to use to_jsonb to allow the
timestamptz to be put back into the jsonb.
WITH replace AS (
SELECT jsonb($$[
{"start": "2023-06-12T19:54:39.248859996+10:00", "end": "2023-06-12T19:54:59.248859996+10:00", "data": {"item":1}},
{"start": "2023-06-12T19:54:50.248859996+10:00", "end": "2023-06-12T19:59:39.248859996+10:00", "data": {"item":2}},
{"start": "2023-06-12T19:56:39.248859996+10:00", "end": "2023-06-12T19:57:39.248859996+10:00", "data": {"item":3}},
{"start": "2023-06-12T19:54:39.248859996+10:00", "end": "2023-06-12T20:54:49.248859996+10:00", "data": {"item":4}},
{"start": "2024-06-12T19:54:39.248859996+10:00", "end": "2024-06-12T19:59:39.248859996+10:00", "data": {"item":5}}
]$$) replacements
)
SELECT
jsonb_agg(new ORDER BY idx) trimmed_replacements
FROM
replace, LATERAL (
SELECT idx, jsonb_object_agg(key,
CASE
WHEN key = 'start'
THEN to_jsonb(greatest(old::text::TIMESTAMPTZ, '2023-06-12T19:54:50+10:00'::TIMESTAMPTZ))
WHEN key = 'end'
THEN to_jsonb(least(old::text::TIMESTAMPTZ, '2023-06-12T19:58:00+10:00'::TIMESTAMPTZ))
ELSE old
END
)
FROM
jsonb_array_elements(replacements)
WITH ORDINALITY rs(r, idx),
jsonb_each(r) each(key, old)
WHERE
(r->>'start')::TIMESTAMPTZ < '2023-06-12T19:58:00+10:00'::TIMESTAMPTZ and
(r->>'end')::TIMESTAMPTZ > '2023-06-12T19:54:50+10:00'::TIMESTAMPTZ
GROUP BY idx
) news(idx, new);
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-09-21 06:52 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-09-15 11:22 Re: re-novice coming back to pgsql: porting an SQLite update statement to postgres Alban Hertroys <[email protected]>
2024-09-15 17:13 ` Willow Chargin <[email protected]>
2024-09-21 03:33 ` Dan Kortschak <[email protected]>
2024-09-21 06:52 ` Dan Kortschak <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox