Re: jsonb_array_elements_recursive()

ne 7. 2. 2021 v 18:43 odesílatel Joel Jacobson <joel(at)compiler(dot)org> napsal:

> On Sun, Feb 7, 2021, at 18:33, Zhihong Yu wrote:
> >Hi,
> ># SELECT '[[5,2],"a",[8,[3,2],6]]'::jsonb;
> > jsonb
> >-------------------------------
> > [[5, 2], "a", [8, [3, 2], 6]]
> >(1 row)
> >
> >unnest(array[[3,2],"a",[1,4]]) is not accepted currently.
> >
> >Would the enhanced unnest accept the above array ?
> >
> >Cheers
>
> Yes, but only if the overloaded jsonb version of unnest() exists,
> and only if it's a jsonb array, not a normal array, like Pavel explained.
>
> Your example using a PoC PL/pgSQL:
>
> CREATE FUNCTION unnest(jsonb)
> RETURNS SETOF jsonb
> LANGUAGE plpgsql
> AS $$
> DECLARE
> value jsonb;
> BEGIN
> FOR value IN SELECT jsonb_array_elements($1) LOOP
> IF jsonb_typeof(value) <> 'array' THEN
> RETURN NEXT value;
> ELSE
> RETURN QUERY
> SELECT pit.jsonb_array_elements_recursive(value);
> END IF;
> END LOOP;
> END
> $$;
>
> SELECT unnest('[[5,2],"a",[8,[3,2],6]]'::jsonb);
> unnest
> --------
> 5
> 2
> "a"
> 8
> 3
> 2
> 6
> (7 rows)
>
> Cheers,
>

just note - isn't it possible to use "not committed yet" function
json_table instead?

https://commitfest.postgresql.org/32/2902/

I understand your request - but I am afraid so we are opening a Pandora box
a little bit. There is a possible collision between Postgres first class
arrays and non atomic types. I am not sure if a functional API is enough to
cover all valuable cases. The functional API is limited and if we cross
some borders, we can get more often errors of type FUNCLOOKUP_AMBIGUOUS. So
if proposed functionality can be implemented by ANSI/SQL dedicated
function, then it can be better. Second possibility is enhancing the
PLpgSQL FOREACH statement. There we have more possibilities to design
necessary syntax, and we don't need to solve possible problems with
handling ambiguous overloaded functions. I don't afraid of semantics. The
problems can be in parser in function lookup.

Semantically - now the types can support a subscripting interface. There
can be some similarity for type's iterators over nested fields.

Regards

Pavel

> /Joel
>