SQL Server

How to Query a JSON Object in SQL Server

SQL Server has supported querying JSON columns since the version launched in 2016. This gives the advantage of storing and querying unstructured data. Here's how you can query a JSON column in SQL Server.

Get only home_address.state from the employees table:

select  
  json_value(home_address,'$.state') as state
from employees

Get only employees from a specific state:

select  *
from employees
where  json_value(home_address,'$.state')='AR'

declare @json nvarchar(max) = N'{"employee":[{"id":1,"name":{"first":"Jane","middle":"Laura","last":"Jones"},"address":{"home":"11th Ave W","work":"10th Ave W","city":"Tampa","zipcode":"33601","state":"Florida"}}]}';

Get first key and its value of a JSON array:

select 
  top 1 [key]
from openjson(@json);

--going deeper to get only the employee name
select 
  [key], 
  value
from openjson(@json, '$.employee[0].name');

Get JSON keys as columns populated with key values:

select *
from openjson(@json, '$.employee')
with(first_name nvarchar(25) '$.name.first',
     middle_name nvarchar(25) '$.name.middle', 
     last_name nvarchar(25) '$.name.last');