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'
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'
When you know the key that holds the data:
SELECT column_name from table_name where column_name->>'key' LIKE '%QUERYSTRING%';
When you don't know the key that holds the data:
SELECT column_name from table_name where column_name::text LIKE '%QUERYSTRING%';
Just want to add to the existing answers a simple solution how you can check if array inside json contains a value:
DECLARE @Json NVARCHAR(max) = '{"names":["Joe","Fred","Sue"]}'
IF EXISTS (SELECT value FROM OPENJSON(@Json,'$.names') WHERE value = 'Joe')
PRINT 'Array contains "Joe"'
ELSE
PRINT 'Array does not contain "Joe"'
You can search for a match on a JSON array via below query:
SELECT JSON_EXTRACT(COLUMN, "$.names") AS NAME
FROM TABLE JSON_EXTRACT(COLUMN, "$.names") != ""
Replace the TABLE with your table name and COLUMN with the column name in the table. the key I have mentioned name as it was there in your question.
It's very simple , can be easily done using JSON_CONTAINS() function.
SELECT * FROM table
where JSON_CONTAINS(column, 'joe','$.name');
