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.
Since
Interests
is a nested array, you need to parse your way through the array levels. To do this, you can useCROSS APPLY
withOPENJSON()
. The firstCROSS APPLY
will get you the friend names and the JSON array of interests, and then the secondCROSS APPLY
pulls the interests out of the array and corrolates them with the appropriate friend names. Here's an example query: