Parsing JSON in SQL Server

JSON has become a popular data transfer format, especially in client-server architecture applications. Since SQL Server 2016, JSON support has been introduced, allowing you to modify, read (to some extent), and validate JSON data. In this article, we will explore how to read JSON values in SQL Server.

Getting a Scalar Value from a JSON Property

Suppose we have the following JSON:

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

To parse a value, we can use the JSON_VALUE function with the following syntax:

JSON_VALUE(<JSON string>, <path to the specified property>)

For example:

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

This will produce the following result:

Caveat: JSON_VALUE can only retrieve scalar values. It cannot parse arrays or objects.

Getting a JSON Object

To retrieve an object value from JSON, we use the JSON_QUERY function. Its syntax is similar to JSON_VALUE:

JSON_QUERY(<JSON string>, <path to the specified property>)

We can revise the previous query as follows:

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

This will produce the following result:

Parsing an Array of Objects into a Rowset

What if we need to extract the "hobbies" array as a rowset? In this case, we use the OPENJSON function. Its syntax is similar to JSON_VALUE:

SELECT * 
FROM OPENJSON(@employeeJSON, '$.hobbies') AS EmployeeHobbies;

This will produce the following result:

Now, suppose we have another JSON array of objects:

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

If we need a rowset with specific columns, such as id and name, we can use the following query:

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

This will produce the following result:

Wrap-Up

Here’s a summary of the methods to parse JSON in SQL Server 2016 and later:

• Use JSON_VALUE to retrieve scalar values.
• Use JSON_QUERY to retrieve non-scalar values (e.g., arrays or objects).
• Use OPENJSON to convert arrays into rowsets, allowing you to treat them like tables.