Thread: proposal - idea - enhancing plpgsql FOREACH for JSON, jsonb and hstore
Hi
jsonb with subscripting support can be used as a dictionary object in plpgsql.
Can be nice to have support for iteration over a set of tuples (key, value).
Some like
FOREACH fieldvar [ KEY keyvar] IN DICTIONARY sourceexpr [VALUE searchexpr]
LOOP
END LOOP;
and for JSON arrays
FOREACH var IN ARRAY jsonval
LOOP
END LOOP
Example:
dict jsonb DEFAULT '{"a", "a1", "b", "b1"}
v text; k text;
j jsonb;
BEGIN
FOREACH v KEY k IN DICTIONARY dict
LOOP
RAISE NOTICE '%=>%', k, v; -- a=>a1\nb=>b1
END LOOP;
--
FOREACH j IN DICTIONARY dict
LOOP
RAISE NOTICE '%', j; -- {"a":"a1"}\n{"b":"b1"}
END LOOP;
The goal is to support fast iteration over some non atomic objects different from arrays.
Maybe some background of XMLTABLE and JSON_TABLE functions can be used there.
Comments, notes?
Regards
Pavel
Re: proposal - idea - enhancing plpgsql FOREACH for JSON, jsonb and hstore
From
Julien Rouhaud
Date:
On Sat, Jan 23, 2021 at 07:46:01AM +0100, Pavel Stehule wrote: > Hi > > jsonb with subscripting support can be used as a dictionary object in > plpgsql. > > Can be nice to have support for iteration over a set of tuples (key, > value). > > Some like > > FOREACH fieldvar [ KEY keyvar] IN DICTIONARY sourceexpr [VALUE searchexpr] > LOOP > END LOOP; > > and for JSON arrays > > FOREACH var IN ARRAY jsonval > LOOP > END LOOP > [...] > > The goal is to support fast iteration over some non atomic objects > different from arrays. +1, it seems like something useful to have.
Greetings, * Pavel Stehule (pavel.stehule@gmail.com) wrote: > jsonb with subscripting support can be used as a dictionary object in > plpgsql. > > Can be nice to have support for iteration over a set of tuples (key, > value). Yes, I agree that this would be useful. > FOREACH fieldvar [ KEY keyvar] IN DICTIONARY sourceexpr [VALUE searchexpr] > LOOP > END LOOP; Should we be thinking about using sql/json path for what to search for instead of just fieldvar/keyvar..? Or perhaps support both.. > and for JSON arrays > > FOREACH var IN ARRAY jsonval > LOOP > END LOOP Presumably we'd also support SLICE with this? Also, I wonder about having a way to FOREACH through all objects, returning top-level ones, which a user could then call jsonb_typeof on and then recurse if an object is found, allowing an entire jsonb tree to be processed this way. Thanks, Stephen
Attachment
so 23. 1. 2021 v 19:21 odesílatel Stephen Frost <sfrost@snowman.net> napsal:
Greetings,
* Pavel Stehule (pavel.stehule@gmail.com) wrote:
> jsonb with subscripting support can be used as a dictionary object in
> plpgsql.
>
> Can be nice to have support for iteration over a set of tuples (key,
> value).
Yes, I agree that this would be useful.
> FOREACH fieldvar [ KEY keyvar] IN DICTIONARY sourceexpr [VALUE searchexpr]
> LOOP
> END LOOP;
Should we be thinking about using sql/json path for what to search
for instead of just fieldvar/keyvar..? Or perhaps support both..
I would support both. JSONPath can be specified by a special clause - I used the keyword VALUE (but can be different).
My primary inspiration and motivation is the possibility to use jsonb as a collection or dictionary in other languages. But if we implement some "iterators", then enhancing to support XMLPath or JSONPath is natural. The interface should not be too complex like specialized functions XMLTABLE or JSON_TABLE, but simple task should be much faster with FOREACH statement, because there is not an overhead of SQL or SPI.
> and for JSON arrays
>
> FOREACH var IN ARRAY jsonval
> LOOP
> END LOOP
Presumably we'd also support SLICE with this?
if we find good semantics, then why not?
Also, I wonder about having a way to FOREACH through all objects,
returning top-level ones, which a user could then call jsonb_typeof on
and then recurse if an object is found, allowing an entire jsonb tree to
be processed this way.
Probably this should be possible via JSONPath iteration.
We need similar interface like nodeTableFuncscan.c
Thanks,
Stephen