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'