arrayspostgresqlt-sqlsql-server-2016json-query

How to search SQL column containing JSON array

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

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