How to search SQL column containing JSON array

sql arrays tsql sql-server-2016 json-query
paultechguy picture paultechguy · Nov 11, 2017 · Viewed 15.9k times · Source

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'

Answer

Serkan Arslan picture Serkan Arslan · Nov 11, 2017

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'

Related questions

Check if value exists in Postgres array

Using Postgres 9.0, I need a way to test if a value exists in a given array. So far I came up with something like this: select '{1,2,3}'::int[] @> (ARRAY[]::int[] || value_variable::int) But I keep thinking there …

Read More
Store select query's output in one array in postgres

My code is: SELECT column_name FROM information.SCHEMA.columns WHERE table_name = 'aean' It returns column names of table aean. Now I have declared an array: DECLARE colnames text[] How can I store select's output in colnames array. Is …

Read More
How to use php array with sql IN operator?

I have and array with two values and I want to use it with sql IN operator in select query. Here is the structure of my table id comp_id 1 2 2 3 3 1 I have an array $arr which have two values Array ( [0] =&…

Read More