Re: jsonb_array_elements_recursive() - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: jsonb_array_elements_recursive()
Date
Msg-id CAFj8pRBvQz2jTb7uRWaC93WFiPkYkEZJV3tei+uZ_dc_-YiYYQ@mail.gmail.com
Whole thread Raw
In response to Re: jsonb_array_elements_recursive()  ("Joel Jacobson" <joel@compiler.org>)
List pgsql-hackers


ne 7. 2. 2021 v 18:43 odesílatel Joel Jacobson <joel@compiler.org> napsal:
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,

just note - isn't it possible to use "not committed yet" function json_table instead?


I understand your request - but I am afraid so we are opening a Pandora box a little bit. There is a possible collision between Postgres first class arrays and non atomic types. I am not sure if a functional API is enough to cover all valuable cases. The functional API is limited and if we cross some borders, we can get more often errors of type FUNCLOOKUP_AMBIGUOUS. So if proposed functionality can be implemented by ANSI/SQL dedicated function, then it can be better. Second possibility is enhancing the PLpgSQL FOREACH statement. There we have more possibilities to design necessary syntax, and we don't need to solve possible problems with handling ambiguous  overloaded functions. I don't afraid of semantics. The problems can be in parser in function lookup.

Semantically - now the types can support a subscripting interface. There can be some similarity for type's iterators over nested fields.

Regards

Pavel



/Joel

pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Bug in query rewriter - hasModifyingCTE not getting set
Next
From: Pavel Stehule
Date:
Subject: Re: proposal: enhancing plpgsql debug API - returns text value of variable content