Thread: jsonb_array_elements_recursive()

jsonb_array_elements_recursive()

From
"Joel Jacobson"
Date:
Hi,

A particular useful feature of jsonb arrays,
is the ability to represent multidimensional arrays without matching dimensions,
which is not possible with normal PostgreSQL arrays.

SELECT array[[5,2],1,[8,[3,2],6]];
ERROR:  multidimensional arrays must have array expressions with matching dimensions

SELECT '[[5,2],1,[8,[3,2],6]]'::jsonb;
[[5, 2], 1, [8, [3, 2], 6]]

When working with jsonb array structures,
there is already jsonb_array_elements() to expand the top-level.

Another case that I think is common is wanting to expand all levels, not just the top-level.

Maybe it's common enough to motivate a new param:

   jsonb_array_elements(from_json jsonb [, recursive boolean ])

Or as a separate function. Below is a PoC in PL/pgSQL:

CREATE OR REPLACE FUNCTION jsonb_array_elements_recursive(from_json jsonb, OUT value jsonb)
RETURNS SETOF jsonb
LANGUAGE plpgsql
AS $$
BEGIN
FOR value IN SELECT jsonb_array_elements(from_json) LOOP
  IF jsonb_typeof(value) <> 'array' THEN
    RETURN NEXT;
  ELSE
    RETURN QUERY
    SELECT * FROM jsonb_array_elements_recursive(value);
  END IF;
END LOOP;
END
$$;

# SELECT * FROM jsonb_array_elements_recursive('[[5, 2], 1, [8, [3, 2], 6]]'::jsonb);
value
-------
5
2
1
8
3
2
6
(7 rows)

I tried but failed to implement a PoC in pure SQL,
not even using the new CTE SEARCH functionality,
but maybe it's possible somehow.

/Joel

Re: jsonb_array_elements_recursive()

From
"Joel Jacobson"
Date:
Having thought about this some more,
the function name should of course be jsonb_unnest(),
similar to how unnest() works for normal arrays:

SELECT unnest(array[[3,2],[1,4]]);
unnest
--------
      3
      2
      1
      4
(4 rows)

SELECT jsonb_unnest('[[3,2],[1,4]]'::jsonb);
jsonb_unnest
--------------------
3
2
1
4
(4 rows)

Thoughts?

Re: jsonb_array_elements_recursive()

From
Pavel Stehule
Date:
Hi

ne 7. 2. 2021 v 16:59 odesílatel Joel Jacobson <joel@compiler.org> napsal:
Having thought about this some more,
the function name should of course be jsonb_unnest(),
similar to how unnest() works for normal arrays:

SELECT unnest(array[[3,2],[1,4]]);
unnest
--------
      3
      2
      1
      4
(4 rows)

SELECT jsonb_unnest('[[3,2],[1,4]]'::jsonb);
jsonb_unnest
--------------------
3
2
1
4
(4 rows)

Thoughts?

It  has  sense. Maybe it should return two columns - first path to value, and second with value. It can be used like some "reader"

Regards

Pavel


Re: jsonb_array_elements_recursive()

From
"Joel Jacobson"
Date:
On Sun, Feb 7, 2021, at 17:08, Pavel Stehule wrote:
>>ne 7. 2. 2021 v 16:59 odesílatel Joel Jacobson <joel@compiler.org> napsal:
>>
>>SELECT jsonb_unnest('[[3,2],[1,4]]'::jsonb);
>>jsonb_unnest
>>--------------------
>>3
>>2
>>1
>>4
>>(4 rows)
>
>It  has  sense. Maybe it should return two columns - first path to value, and second with value. It can be used like some >"reader"

Thanks for thinking about this.

I would expect jsonb_unnest() to have the same semantics as unnest(), but returning SETOF jsonb.

jsonb_unnest() implemented in C would of course be much more performant than the PL/pgSQL PoC.
And I think performance could be important for such a function,
so I think we should be careful adding extra complexity to such a function,
unless it can be demonstrated it is needed for a majority of cases.

/Joel

Re: jsonb_array_elements_recursive()

From
Tom Lane
Date:
"Joel Jacobson" <joel@compiler.org> writes:
> Having thought about this some more,
> the function name should of course be jsonb_unnest(),
> similar to how unnest() works for normal arrays:

Why not just unnest(), then?

            regards, tom lane



Re: jsonb_array_elements_recursive()

From
"Joel Jacobson"
Date:
On Sun, Feb 7, 2021, at 17:27, Tom Lane wrote:
>"Joel Jacobson" <joel@compiler.org> writes:
>> Having thought about this some more,
>> the function name should of course be jsonb_unnest(),
>> similar to how unnest() works for normal arrays:
>
>Why not just unnest(), then?
>
>regards, tom lane

Ahh, of course! I totally forgot about function overloading when thinking about this.

+1

/Joel

Re: jsonb_array_elements_recursive()

From
Zhihong Yu
Date:
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

On Sun, Feb 7, 2021 at 8:31 AM Joel Jacobson <joel@compiler.org> wrote:
On Sun, Feb 7, 2021, at 17:27, Tom Lane wrote:
>"Joel Jacobson" <joel@compiler.org> writes:
>> Having thought about this some more,
>> the function name should of course be jsonb_unnest(),
>> similar to how unnest() works for normal arrays:
>
>Why not just unnest(), then?
>
>regards, tom lane

Ahh, of course! I totally forgot about function overloading when thinking about this.

+1

/Joel

Re: jsonb_array_elements_recursive()

From
Pavel Stehule
Date:
Hi

ne 7. 2. 2021 v 18:31 odesílatel Zhihong Yu <zyu@yugabyte.com> napsal:
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 ?

There should be a special overwrite for json type. Json can hold an array, but from Postgres perspective, it is not an array.

But there is really one specific case. We can have an array of json(b), and inside there should be other arrays. So nesting can be across values.

Regards

Pavel

 

Cheers

On Sun, Feb 7, 2021 at 8:31 AM Joel Jacobson <joel@compiler.org> wrote:
On Sun, Feb 7, 2021, at 17:27, Tom Lane wrote:
>"Joel Jacobson" <joel@compiler.org> writes:
>> Having thought about this some more,
>> the function name should of course be jsonb_unnest(),
>> similar to how unnest() works for normal arrays:
>
>Why not just unnest(), then?
>
>regards, tom lane

Ahh, of course! I totally forgot about function overloading when thinking about this.

+1

/Joel

Re: jsonb_array_elements_recursive()

From
"David G. Johnston"
Date:
On Sunday, February 7, 2021, Zhihong Yu <zyu@yugabyte.com> 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 ?

Its not possible to even create that sql array so whether the unnest function could do something useful with it is immaterial.

David J.

Re: jsonb_array_elements_recursive()

From
"Joel Jacobson"
Date:
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

Re: jsonb_array_elements_recursive()

From
"Joel Jacobson"
Date:
On Sun, Feb 7, 2021, at 18:42, Joel Jacobson wrote:
>    SELECT pit.jsonb_array_elements_recursive(value);

Sorry, that line should have been:

    SELECT unnest(value);



Re: jsonb_array_elements_recursive()

From
Pavel Stehule
Date:


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

Re: jsonb_array_elements_recursive()

From
Zhihong Yu
Date:
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@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

Re: jsonb_array_elements_recursive()

From
Pavel Stehule
Date:


ne 7. 2. 2021 v 19:18 odesílatel Zhihong Yu <zyu@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 <joel@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

Re: jsonb_array_elements_recursive()

From
"David G. Johnston"
Date:
On Sun, Feb 7, 2021 at 11:39 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:


ne 7. 2. 2021 v 19:18 odesílatel Zhihong Yu <zyu@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.


More generally, a sequence of characters has no meaning to the system unless and until an externally supplied type is given to it allowing it to interpret the sequence of characters in some concrete way.  The system will never assign a concrete type to some random sequence of characters based upon what those characters are.  Forgive the idiom, but to do otherwise would be putting the cart before the horse.  It would also be quite expensive and prone to, as above, different types deciding on the same textual representation being valid input to each.

David J.