要在JSON数组中进行搜索，需要使用OPENJSON

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'))  

或者作为替代方案，可以将其与CROSS APPLY一起使用.

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

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