Re: proposal - idea - enhancing plpgsql FOREACH for JSON, jsonb and hstore

so 23. 1. 2021 v 19:21 odesílatel Stephen Frost <sfrost(at)snowman(dot)net> napsal:

> Greetings,
>
> * Pavel Stehule (pavel(dot)stehule(at)gmail(dot)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
>