Query against JSON array elements?

Question

Is it possible to run queries on array elements? Seems like it works ok on the top level items... Lets say I have this json:

{
  "_id": "5769cfbf7e45b52e388bbc78",
  "address": {
    "street": "2 Avenue",
    "zipcode": "10075",
    "building": "1480",
    "coord": [
      73.9557413,
      40.7720266
    ]
  },
  "borough": "Manhattan",
  "cuisine": "Italian",
  "grades": [
    {
      "date": "2014-10-01T00:00:00.000Z",
      "grade": "A",
      "score": 11
    },
    {
      "date": "2014-01-06T00:00:00.000Z",
      "grade": "B",
      "score": 17
    }
  ],
  "name": "Vella",
  "restaurant_id": "41704620"
}

Is it possible to use JSON_QUERY / JSON_VALUE to do a where clause against grades[xxx].score? I.e. I want to return all documents where ANY of the grades.score is >= 17.

I think you want to use OPENJSON. I don't have SQL Server 2016 available, so I can't really help any further than that. — Blorgbeard
– Blorgbeard, Commented Jun 24, 2016 at 2:26

d.popov · Accepted Answer · 2016-08-23 11:52:34Z

2

SQL Azure and SQL Server 2016 supports JSON natively now. You can use the following query in 'EXISTS' clause, or whatever is needed (assuming @json contains the given JSON data):

SELECT g.score as score
FROM OPENJSON(@json) WITH (Id varchar(max)) j
    CROSS APPLY OPENJSON(JSON_QUERY(@json, '$.grades')) WITH (score int) AS g
WHERE g.score = 17

edited Aug 23, 2016 at 11:52

answered Aug 23, 2016 at 11:25

d.popov

4,2951 gold badge40 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

qxg Over a year ago

SQL Azure and SQL Server 2016 supports JSON natively.

Collectives™ on Stack Overflow

Query against JSON array elements?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related