sql-server - 查询其根是SQL Server中对象数组的Json

Question

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

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

JSON_VALUE 或 JSON_QUERY 的 MSDN 示例需要在根目录中有一个 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)