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