Обсуждение: jsonb_array_elements_recursive()
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
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?
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--------3214(4 rows)SELECT jsonb_unnest('[[3,2],[1,4]]'::jsonb);jsonb_unnest--------------------3214(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
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
"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
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
Hi,
# SELECT '[[5,2],"a",[8,[3,2],6]]'::jsonb;
jsonb
-------------------------------
[[5, 2], "a", [8, [3, 2], 6]]
(1 row)
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 laneAhh, of course! I totally forgot about function overloading when thinking about this.+1/Joel
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
CheersOn 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 laneAhh, of course! I totally forgot about function overloading when thinking about this.+1/Joel
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.
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
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);
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 ?>>CheersYes, 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 jsonbLANGUAGE plpgsqlAS $$DECLAREvalue jsonb;BEGINFOR value IN SELECT jsonb_array_elements($1) LOOPIF jsonb_typeof(value) <> 'array' THENRETURN NEXT value;ELSERETURN QUERYSELECT pit.jsonb_array_elements_recursive(value);END IF;END LOOP;END$$;SELECT unnest('[[5,2],"a",[8,[3,2],6]]'::jsonb);unnest--------52"a"8326(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
Hi,
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 ?>>CheersYes, 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 jsonbLANGUAGE plpgsqlAS $$DECLAREvalue jsonb;BEGINFOR value IN SELECT jsonb_array_elements($1) LOOPIF jsonb_typeof(value) <> 'array' THENRETURN NEXT value;ELSERETURN QUERYSELECT pit.jsonb_array_elements_recursive(value);END IF;END LOOP;END$$;SELECT unnest('[[5,2],"a",[8,[3,2],6]]'::jsonb);unnest--------52"a"8326(7 rows)Cheers,/Joel
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
CheersOn 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 ?>>CheersYes, 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 jsonbLANGUAGE plpgsqlAS $$DECLAREvalue jsonb;BEGINFOR value IN SELECT jsonb_array_elements($1) LOOPIF jsonb_typeof(value) <> 'array' THENRETURN NEXT value;ELSERETURN QUERYSELECT pit.jsonb_array_elements_recursive(value);END IF;END LOOP;END$$;SELECT unnest('[[5,2],"a",[8,[3,2],6]]'::jsonb);unnest--------52"a"8326(7 rows)Cheers,/Joel
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 typeMinimally 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.