From: | "Joel Jacobson" <joel(at)compiler(dot)org> |
---|---|
To: | "Zhihong Yu" <zyu(at)yugabyte(dot)com> |
Cc: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "PostgreSQL Developers" <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
Subject: | Re: jsonb_array_elements_recursive() |
Date: | 2021-02-07 17:42:33 |
Message-ID: | 266f1325-11bc-4b1f-a8c6-9616cc1d242c@www.fastmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Joel Jacobson | 2021-02-07 17:43:29 | Re: jsonb_array_elements_recursive() |
Previous Message | David G. Johnston | 2021-02-07 17:37:51 | Re: jsonb_array_elements_recursive() |