How to search SQL column containing JSON array
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”