Is is possible to do `IN` query over JSON array?

Question

In SQL Server, is there a way to do IN query over json array ?

eg.

There's a column foo which contains a json array

row1 -> {"foo":["a", "b"]}

row2 -> {"foo":["c", "a", "b"]}

I need to query rows which has b in json array

JSON_QUERY can return the array, but there's no way to do

Something like

SELECT *   
FROM table1 
WHERE "b" in JSON_QUERY(foo)

LIKE query will work, but is inefficient

Answer 1

You can combine OPENJSON with JSON_QUERY and use CROSS APPLY to break down the result to the array elements level

declare @tmp table (foo nvarchar(max))

insert into @tmp values
('{"foo":["a", "b"]}')
,('{"foo":["c", "a", "b"]}')
,('{"foo":["c", "a", "y"]}')

SELECT foo
  FROM @tmp AS c
  CROSS APPLY OPENJSON(JSON_QUERY(foo, '$.foo')) AS x
  where x.[value]='b'

Sample input:

Sample output: