From: | Zhihong Yu <zyu(at)yugabyte(dot)com> |
---|---|
To: | Joel Jacobson <joel(at)compiler(dot)org> |
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 18:20:29 |
Message-ID: | CALNJ-vSzDD7+MBoSCKXz323Arh8RcvVo=OgpcRaWVxLoi9e1zQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2021-02-07 18:38:39 | Re: jsonb_array_elements_recursive() |
Previous Message | Heikki Linnakangas | 2021-02-07 18:13:28 | Re: Perform COPY FROM encoding conversions in larger chunks |