Query support for nested collections of primitive types #30713
Description
#29427 allows mapping arbitrary collections of primitive types to JSON text columns, and #30426 allows querying them with arbitrary LINQ operators. This tracks mapping nested collections as well (e.g. int[][]).
Some ideas for query translations over nested collections:
-- Index to get a nested array, returns 3, 4
SELECT o.value FROM OPENJSON((
SELECT i.value FROM OPENJSON('[[1, 2], [3, 4]]') AS i
ORDER BY i.[key]
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY)) AS o;
-- Index to get a nested element, returns 4
SELECT o.value FROM OPENJSON((
SELECT i.value FROM OPENJSON('[[1, 2], [3, 4]]') AS i
ORDER BY i.[key]
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY)) AS o
ORDER BY o.[key]
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;
-- c.Nested.Where(n => n[0] == 1)
SELECT o.value FROM OPENJSON('[[1, 2], [3, 4]]') AS o
WHERE (
SELECT i.value FROM OPENJSON(o.value) AS i
ORDER BY i.[key]
OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY) = 1;
-- Flatten (SelectMany), returns 1, 2, 3, 4
SELECT i.value FROM OPENJSON('[[1, 2], [3, 4]]') AS o
CROSS APPLY OPENJSON(o.value) AS i;Note that IIRC PG doesn't support nested arrays (but does support multidimensional ones).