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