Support LINQ querying of non-primitive collections within JSON

[roji]

We don't currently allow composing LINQ operators over collections within JSON documents, e.g.:

ctx.Entity.Where(e => e.JsonThing.Track.Segments.Any(s => s.HR > 130)

While the JSONPATH language was created for this purpose, support for it is very limited across databases, with the exception of PostgreSQL (see docs).

However, databases do provide ways of converting JSON arrays to a standard relational rowset, which can then be queried via usual SQL operators. We've already implemented support for these mechanisms (OPENJSON on SQL Server, json_each on SQLite) as part of primitive collections (#30426); we could extend that support to also work for non-primitive collections within JSON.

Originally suggested in #4021 (comment)

[SarcoZ]

JsonPath query API need.I expect it

[marinasundstrom]

I watched a video where they talked about not being able to nest conditions on collections in queries like this one.

So I guess this would resolve it. 🙂

[roji]

@marinasundstrom that's right - this issue tracks expressing complex queries inside JSON documents.