Re: jsonb_array_elements_recursive()

ne 7. 2. 2021 v 19:18 odesílatel Zhihong Yu <zyu(at)yugabyte(dot)com> napsal:

> 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.
>

only until somebody does support for hstore, xml, ... some future data type

Minimally now, we have json, jsonb types.

Regards

Pavel

>
> 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
>>
>