ne 7. 2. 2021 v 19:18 odesÃlatel Zhihong Yu <z...@yugabyte.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 <j...@compiler.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 >> >