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.

[Jejuni]

JSON support is great, but JSONPATH will definitely be required as querying will be very limited otherwise.

[roji]

Following @maumar's investigation, I took a cross-database look at JSONPATH support, and I can confirm it's very limited. Except for PostgreSQL, other databases have a very restricted subset of JSONPATH, basically allowing traversal of properties and arrays, and nothing more (close to what we already support).

MySQL does support asterisk in an array, providing a nice translation to LINQ SelectMany (see below), but this isn't supported in either SQLite or SQL Server (and the MySQL support still isn't anywhere close to the PG support).

MySQL array asterisk

SELECT json_extract('
{
  "track": {
    "segments": [
      {
        "location":   [ 47.763, 13.4034 ],
        "start time": "2018-10-14 10:05:14",
        "HR": 73
      },
      {
        "location":   [ 47.706, 13.2635 ],
        "start time": "2018-10-14 10:39:21",
        "HR": 135
      }
    ]
  }
}',
    '$.track.segments[*].location');

Results: [[47.763, 13.4034], [47.706, 13.2635]]

[roji]

However, it should be possible to translate complex query operations inside JSON via OPENJSON. For example, the query above can be translated as follows:

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

SELECT DISTINCT b.*
FROM [Blogs] AS b
CROSS APPLY OPENJSON(b.Json, '$.track.segments') AS [Segments]
CROSS APPLY OPENJSON([Segments].value) WITH (HR INT)
WHERE HR > 130;

In a nutshell, OPENJSON can be used with CROSS/OUTER APPLY to extract/expand out part of the JSON document (including arrays, as above), and join against it. Once that's done, regular SQL can be used to filter the rows.

This would be a completely SQL Server-specific translation scheme.

[roji]

In SQL Server, we can use OPENJSON.

In PostgreSQL: jsonb_to_recordset

SELECT *
FROM ROWS FROM (jsonb_to_recordset('[{"a":40,"b":"foo"},{"a":"100","b":"bar"}]') AS (a INTEGER, b TEXT)) WITH ORDINALITY AS x (p, q)
ORDER BY ordinality;

In SQLite, json_each:

SELECT value->'a' AS a, value->'b' AS b FROM json_each('[{"a":1,"b":"foo"}, {"a":2,"b":"bar"}]');