Re: jsonb_array_elements_recursive()

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