在SQL Server中查询根为对象数组的Json

比如:

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)

它可能与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