Testing to see if a value exists in a nested JSON array

Question

I have a SQL 2016 table that contains a column holding JSON data. A sample JSON document looks as follows:

{
    "_id": "5a450f0383cac0d725cd6735",
    "firstname": "Nanette",
    "lastname": "Mccormick",
    "registered": "2016-07-10T01:50:10 +04:00",
    "friends": [
      {
        "id": 0,
        "name": "Cote Collins",
        "interests": [
          "Movies",
          "Movies",
          "Cars"
        ]
      },
      {
        "id": 1,
        "name": "Ratliff Ellison",
        "interests": [
          "Birding",
          "Birding",
          "Chess"
        ]
      },
      {
        "id": 2,
        "name": "William Ratliff",
        "interests": [
          "Music",
          "Chess",
          "Software"
        ]
      }
    ],
    "greeting": "Hello, Nanette! You have 4 unread messages.",
    "favoriteFruit": "apple"
  }

I want to pull all documents in which the interests array of each friends object contains a certain value. I attempted this but got no results:

Select *
From <MyTable>
Where 'Chess' IN (Select value From OPENJSON(JsonValue, '$.friends.interests'))

I should have gotten several rows returned. I must not be referencing the interests array correctly or not understanding how SQL Server deals with a JSON array of this type.

Answer 1

Since Interests is a nested array, you need to parse your way through the array levels. To do this, you can use CROSS APPLY with OPENJSON(). The first CROSS APPLY will get you the friend names and the JSON array of interests, and then the second CROSS APPLY pulls the interests out of the array and corrolates them with the appropriate friend names. Here's an example query:

Select [name]
From #MyTable
CROSS APPLY OPENJSON(JsonValue, '$.friends')
WITH ([name] NVARCHAR(100) '$.name',
        interests NVARCHAR(MAX) AS JSON)
CROSS APPLY OPENJSON(interests)
WITH (Interest NVARCHAR(100) '$')
WHERE Interest = 'Chess'