How to Filter Json Object in Json Array?

Question

I have some data stored in a sql column that looks like

{
  "items": [
    { "ids": [4], "fromCompanyId": 4 },
    { "ids": [6, 1], "fromCompanyId": 1 }
  ]
}

now I want to make a where clause that would find all "items" that have a "fromCompanyId" of "4".

All I found so far is

WHERE JSON_VALUE(jsonInfo,'$.info.address[0].state') LIKE 'US%'

but they are hardcoding the array index. I need to find all matches.

I also am trying openJson but still not working

where 4 in (select fromCompanyId  from openjson(Details, '$.items.fromCompanyId') WITH( [fromCompanyId] int '$.fromCompanyId')))

Answer 1

You need to openjson on several levels. Something like this.

declare @json nvarchar(max)=N'{
  "items": [
    { "ids": [4], "fromCompanyId": 4 },
    { "ids": [6, 1], "fromCompanyId": 1 }
  ]
}'

select id,fromCompanyId
from openjson(@json,'$.items') j --path to the main array
cross apply openjson(value,'$.ids') -- path inside element of main array
with(id int '$')
cross apply openjson(value)
with (
fromCompanyId int '$.fromCompanyId'
)
where fromCompanyId=4

Similar with a table field.

declare @tbl table (id int, detail nvarchar(max))
insert @tbl (id,detail) values
(1,N'{
  "items": [
    { "ids": [4], "fromCompanyId": 4 },
    { "ids": [6, 1], "fromCompanyId": 1 }
  ]
}'),
(2,N'{
  "items": [
    { "ids": [5], "fromCompanyId": 4 },
    { "ids": [7,9], "fromCompanyId": 4 },
    { "ids": [6, 1], "fromCompanyId": 1 }
  ]
}')

select id,jid,fromCompanyId
from @tbl
cross apply openjson(detail,'$.items') -- path to the main array
cross apply openjson(value,'$.ids') -- path inside array element
with(jid int '$')
cross apply openjson(value)
with (
fromCompanyId int '$.fromCompanyId'
)
where fromCompanyId=4