Query support for nested collections of primitive types

[roji]

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).

[ajcvickers]

@roji I'm trying to figure out what work this is tracking for 9. I implemented model building and change tracking support for nested collections in #33456. It's possible I should have then assigned this to you to do query stuff. Are we still missing query support for nested collections? If so, everywhere, just in relational, or just in Cosmos? (If it's not working in Cosmos, then that's a regression, and we'll have to decide how to handle it. If it's not working in relational, then we should block nested collections in relational for 9.)

[ajcvickers]

Design meeting decision: leave as-is for Cosmos. Block nested collections on 9. See #34324.