CODEWITHSUNDEEP
sql-serverazure-sql-database
frosty
frosty

Reputation: 2852

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

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.

Upvotes: 4

Views: 3050

Answers (2)

El Ronnoco
El Ronnoco

Reputation: 11922

It may not be entirely relevant to the OP's post as the usage is different, however it is possible to retrieve arbitrary items from a root-level unnamed JSON array e.g.

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'

Output :

First address.value         Second address.value
A9                          A10

Upvotes: 0

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89141

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)

Upvotes: 4

Related Questions