Json_query check which rows have a special value in their json list

Question

I have a table that each row contains a json column. Inside the json column I have an object containing an array of tags. What I want to do is to see which rows in my table have the tag that I am searching for.

Here is an example of my data:

Row 1: 

Id :xxx

Jsom Column:
{
 "tags":[
{"name":"blue dragon", weight:0.80},
{"name":"Game", weight:0.90}
]
}


Row 2:
Id : yyy

Jsom Column:
{
 "tags":[
{"name":"Green dragon", weight:0.70},
{"name":"fantasy", weight:0.80}
]
}

So I want to write a code that if I search for Green, it returns only row 2 and if I search for dragon it returns both row 1 and 2. How can I do that?

I know I can write this to access my array, but more than that I am clueless :\ I am looking for something like this

 Select * from myTable 
 where  JSON_query([JsonColumn], '$.tags[*].name') like '%dragon%'

update

My final query looking like this

select DISTINCT t.id, dv.[key], dv.value
from @t t
cross apply openjson(doc,'$.tags') as d
where json_Value( d.value,'$.name') like '%dragon%'

score 1 · Accepted Answer · answered Jan 26 '19 at 19:32

Something like this:

declare @t table(id int, doc nvarchar(max))

insert into @t(id,doc) values
(1,'
{
 "tags":[
{"name":"blue dragon", "weight":"0.80"},
{"name":"Game", "weight":"0.90"}
]
}'),(2,'
{
 "tags":[
{"name":"Green dragon", "weight":"0.70"},
{"name":"fantasy", "weight":"0.80"}
]
}')

select  t.id, dv.[key], dv.value
from @t t
cross apply openjson(doc,'$.tags') as d
cross apply openjson(d.value) dv
where dv.value like '%dragon%'

Json_query check which rows have a special value in their json list

update

1 Answers1