Re: jsonb_array_elements_recursive()

Hi,

bq. SELECT unnest('[[5,2],"a",[8,[3,2],6]]'::jsonb);

Since the array without cast is not normal array (and would be rejected), I
wonder if the cast is needed.
Because casting to jsonb is the only legitimate interpretation here.

Cheers

On Sun, Feb 7, 2021 at 9:42 AM Joel Jacobson <joel(at)compiler(dot)org> wrote:

> 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,
>
> /Joel
>