Querying Json whose root is an array of objects in SQL Server

Question

I have a column in SQL table that has json value like below:

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

MSDN Examples for JSON_VALUE or JSON_QUERY require a json object at root. How can I query above to return rows that have "address" as A9 and "value" as John? I'm using SQL Azure.

Answer 1

Something like this:

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'

outputs

address name
------- -----
A9      John

(1 row affected)