查询根目录是 SQL Server 中的对象数组的 Json

Question

我在SQL表中有一列具有如下所示的json值：

[
{"address":{"value":"A9"},
"value":{"type":11,"value":"John"}}, 
{"address":{"value":"A10"},
"value":{"type":11,"value":"Doe"}}]

MSDN JSON_VALUE或JSON_QUERY的示例需要在根目录中使用 json 对象。如何查询上面的内容以返回"地址"为 A9 且"值"为 John 的行？我正在使用SQL Azure。

Answer 1

像这样：

declare @json nvarchar(max) = '[
{"address":{"value":"A9"},
"value":{"type":11,"value":"John"}}, 
{"address":{"value":"A10"},
"value":{"type":11,"value":"Doe"}}]'

select a.*
from openjson(@json) r
cross apply openjson(r.value)
with (   
address  nvarchar(200)   '$.address.value',  
name     nvarchar(200)  '$.value.value'
) a  
where address = N'A9'
and name = N'John'

输出

address name
------- -----
A9      John
(1 row affected)

Answer 2

它可能与OP的帖子不完全相关，因为用法不同，但是可以从根级未命名的JSON数组中检索任意项目，例如

declare @json nvarchar(max) = '[
{"address":
{"value":"A9"},
"value":
{"type":11,"value":"John"}
}, 
{"address":
{"value":"A10"},
"value":
{"type":11,"value":"Doe"}
}
]'
select
JSON_VALUE(
JSON_QUERY(@json, '$[0]'),
'$.address.value') as 'First address.value',
JSON_VALUE(
JSON_QUERY(@json, '$[1]'),
'$.address.value') as 'Second address.value'

输出：

First address.value         Second address.value
A9                          A10