How to search SQL column containing JSON array

Question

I have a SQL column that has a single JSON array:

{"names":["Joe","Fred","Sue"]} 

Given a search string, how can I use SQL to search for a match in the names array? I am using SQL 2016 and have looked at JSON_QUERY, but don't know how to search for a match on a JSON array. Something like below would be nice.

SELECT * FROM table WHERE JSON_QUERY(column, '$.names') = 'Joe' 

Answer 1

For doing a search in a JSON array, one needs to use OPENJSON

DECLARE @table TABLE (Col NVARCHAR(MAX)) INSERT INTO @table VALUES ('{"names":["Joe","Fred","Sue"]}')  SELECT * FROM @table  WHERE 'Joe' IN ( SELECT value FROM OPENJSON(Col,'$.names'))   

or as an alternative, one can use it with CROSS APPLY.

SELECT * FROM      @table      CROSS APPLY OPENJSON(Col,'$.names') WHERE value ='Joe' 

Answer 2

It's very simple , can be easily done using JSON_CONTAINS() function.

SELECT * FROM  table where  JSON_CONTAINS(column, 'joe','$.name');