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'))

Answer 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'))