SQL Server 2016 select where in json object array

Question

I have a table [JsonTable], and the column [JsonData] save the json string,

JsonData like:

{
   "Content": [
      {
         "ContentId": "123",
         "Type": 1
      },
      {
         "ContentId": "456",
         "Type": 2
      }
   ]
}

or

[
     {
        "ContentId": "123",
        "Type": 1
     },
     {
        "ContentId": "456",
        "Type": 2
     }
]

How can I inner join like

SELECT* FROM [Content] AS C
INNER JOIN [JsonTable] AS J ON C.[Id] IN (SELECT value FROM OPENJSON(J.[JsonData],'$.Content.ContentId'))

Serkan Arslan · Accepted Answer · 2017-10-31 19:37:59Z

1

You can use this query.

SELECT * FROM [Content] AS C
INNER JOIN [JsonTable] AS J ON C.[Id] IN (SELECT JSON_VALUE(value,'$.ContentId') 
                                          FROM OPENJSON(J.[JsonData],'$.Content'))

answered Oct 31, 2017 at 19:37

Serkan Arslan

13.4k4 gold badges33 silver badges46 bronze badges

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

Collectives™ on Stack Overflow

SQL Server 2016 select where in json object array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related