json - 如何过滤Json数组中的Json对象？

Question

我有一些数据存储在一个看起来像的 sql 列中

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

现在我想创建一个 where 子句来查找所有“fromCompanyId”为“4”的“项目”。

目前我只找到了

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

但他们正在对数组索引进行硬编码。我需要找到所有匹配项。

我也在尝试 openJson 但还是不行

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

Answer 1

您需要在多个级别上openjson。像这样。

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

类似于表字段。

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