SQL Server 2016 select where in json array

Question

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

JsonData like:

{
   "Names": ["John", "Joe", "Sam"]
}

How can I inner join this table like:

SELECT* FROM [TestTable] AS T
INNER JOIN [JsonTable] AS J ON T.[Name] IN JSON_QUERY(J.[JsonData], '$.Names')

wchatx · Accepted Answer · 2019-05-25 21:04:57Z

7

You need to use OPENJSON function for reading Names array. You can use this query.

SELECT * FROM [TestTable] T
INNER JOIN [JsonTable] AS J ON T.[Name] IN (SELECT value FROM OPENJSON(J.[JsonData],'$.Names'))

wchatx

1882 silver badges8 bronze badges

answered Oct 18, 2017 at 10:23

Serkan Arslan

13.4k4 gold badges33 silver badges46 bronze badges

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

Comments

Lydia · Accepted Answer · 2017-10-18 15:15:39Z

7

Another way is to use the cross apply operator like this:

SELECT *
FROM [JsonTable] AS J
CROSS APPLY OPENJSON(J.[JsonData], '$.Names') jsonValue
INNER JOIN [TestTable] T ON T.[Name] = jsonvalue.value

answered Oct 18, 2017 at 15:15

Lydia

1174 bronze badges

monikapatelIT · Accepted Answer · 2018-03-23 01:01:49Z

0

You can use JSON_VALUE to get particular field value,

SELECT * 
FROM [TestTable] AS T 
INNER JOIN [JsonTable] AS J 
ON T.[Name] IN JSON_VALUE(J.[JsonData], '$.Names');

for the nested value have look this link : JSON_VALUE (Transact-SQL)

answered Mar 23, 2018 at 1:01

monikapatelIT

1,00716 silver badges27 bronze badges

Incorrect syntax near 'JSON_VALUE'. Expecting '('.