SQL find elements is in JSON array column

Question

Hello i have a column table who contains json array with tags in it, I try to find a way to select result who contain some of values in it :

ID	tags
1	["test01"]
2	["test02","test03"]

I try to used JSON_QUERY AND JSON_VALUE():

SELECT *
FROM table_tags  
WHERE JSON_QUERY(tags,'$') IN ('test01', 'test02')

return nothing

but with json_value on the first array element [0]

SELECT *
FROM table_tags  
WHERE JSON_VALUE(tags,'$[0]') IN ('test01', 'test02')

it return the first one with test01

ID	tags
1	["test01"]

i need to find a way to iterate through json_value tags to find all tags in ('test01', 'test02')

the database is micorosoft SQL server 2017, i just added the tag — morpheus0010
– morpheus0010, Commented Jun 17, 2021 at 12:37

Zhorov · Accepted Answer · 2021-06-17 12:54:24Z

4

You need an OPENJSON() call to parse the stored JSON array. The result is a table with columns key, value and type and in the value column is each element from the parsed JSON array. The column data type is nvarchar(max) with the same collation as tags column collation.

SELECT *
FROM (VALUES
   (1, '["test01"]'),
   (2, '["test02","test03"]')
) table_tags (id, tags)
WHERE EXISTS (
   SELECT 1 FROM OPENJSON(tags) WHERE [value] IN ('test01', 'test02')
)

edited Jun 17, 2021 at 12:54

answered Jun 17, 2021 at 12:48

Zhorov

30.3k6 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL find elements is in JSON array column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related