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:

  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:

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

--going deeper to get only the employee name
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');
database icon
Finally, a unified workspace for your SQL development
Get more done, together, with PopSQL and SQL Server