How to search SQL column containing JSON array
Solution 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'
Solution 2:
It's very simple , can be easily done using JSON_CONTAINS() function.
SELECT * FROM table
where JSON_CONTAINS(column, 'joe','$.name');