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

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..

> 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