Parsing JSON in SQL Server

As I am writing this article in 2022, JSON is a popular data transfer format that mostly used in client-server architecture applications. Since its 2016 version, SQL Server supports JSON format that includes modify, read (in some ways) and validate a JSON. Now, we will learn how to read JSON values in SQL Server.

Getting a scalar value of a JSON's property

suppose we have this JSON

  DECLARE @employeeJSON NVARCHAR(MAX) = '
      {
        "id": 0,
        "name": "Salinas Holden",
        "age": 29,
        "hobbies": ["reading", "traveling", "coding"]
      }
  ';

then, to parse value we could use JSON_VALUE with following syntax

  JSON_VALUE(<the JSON in varchar>, <the path to spesified property>)

  SELECT 
    JSON_VALUE(@employeeJSON, '$.age') AS EmployeeAge,
    JSON_VALUE(@employeeJSON, '$.name') AS EmployeeName,
    JSON_VALUE(@employeeJSON, '$.hobbies') AS EmployeeHobbies

the result:

EmployeeAge EmployeeName EmployeeHobbies
29 Salinas Holden NULL

Caveat: We cannot parse the array, use JSON_VALUE to get scalar value only

Getting a JSON object

In order to get an object value of a JSON, we should use JSON_QUERY. The syntax is similar to JSON_VALUE

  JSON_QUERY(<the JSON in varchar>, <the path to spesified property>)

then we can revise the query above as

  SELECT 
    JSON_VALUE(@employeeJSON, '$.age') AS EmployeeAge,
    JSON_VALUE(@employeeJSON, '$.name') AS EmployeeName,
    JSON_QUERY(@employeeJSON, '$.hobbies') AS EmployeeHobbies

which gives a result:

EmployeeAge EmployeeName EmployeeHobbies
29 Salinas Holden ["reading", "traveling", "coding"]

Parsing an array of object to be a rowset

Now what if we need to get the "hobbies" object as a rowset? In this case we should use OPENJSON instead with the same syntax as JSON_VALUE

  SELECT * 
  FROM OPENJSON(@employeeJSON, '$.hobbies') AS EmployeeHobbies
key value type
0 reading 1
1 traveling 1
2 coding 1

suppose we have another array of object as

  DECLARE @users VARCHAR(MAX) = '[
      {
        "id": 0,
        "name": "Ellen Sutton"
      },
      {
        "id": 1,
        "name": "Gibson Bush"
      },
      {
        "id": 2,
        "name": "Pitts Brock"
      }
  ]';

If we need a rowset with specified column that is id and name, then we could use:

  SELECT * FROM OPENJSON(@users) WITH (
    id BIGINT '$.id',
    name VARCHAR(30) '$.name'
  );

which gives us result:

id name
0 Ellen Sutton
1 Gibson Bush
2 Pitts Brock

Wrap up

That's all some ways to parse JSON in SQL Server 2016 and above. In short

  • Use JSON_VALUE if we need the scalar value
  • Use JSON_QUERY if we the value we need to get is non-scalar value
  • Use OPENJSON if we need convert an array to be rowsets so we could treat it just like a table