How to search SQL column containing JSON array

Question

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'

Tripletail

Answer 1

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'

Steffi

SQL Server Reference: learn.microsoft.com/en-us/sql/t-sql/functions/… – Harslet

Answer 2

Postgres syntax

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%';

Krucik

Answer 3

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

Picturesque

Answer 4

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.

Bryna

Answer 5

It's very simple , can be easily done using JSON_CONTAINS() function.

SELECT * FROM  table
where  JSON_CONTAINS(column, 'joe','$.name');

Quickwitted

MySQL Reference: dev.mysql.com/doc/refman/5.7/en/json-search-functions.html – Harslet

This only works for MySQL, the question is about SQL 2016. – Forepaw

Finally, something that is simple and working! – Telekinesis