Parse JSON in SQL Server

Azure, SQL Database, SQL Server

JSON (JavaScript Object Notation) is a lightweight data-interchange format that has become a standard for data exchange in web and database applications. With the rise of JSON’s popularity, SQL Server introduced built-in support for JSON starting with SQL Server 2016. In this article, we will explore how to parse JSON in SQL Server and extract meaningful data with practical examples.

Why JSON in SQL Server?

JSON allows applications to exchange structured data with simplicity and efficiency. SQL Server’s JSON support makes it easy to:

  1. Store JSON data in table columns.
  2. Query JSON data directly from the database.
  3. Transform JSON data into relational formats.

The key functions provided by SQL Server for JSON manipulation include:

  • OPENJSON(): Parses JSON text and returns objects in tabular format.
  • JSON_VALUE(): Extracts a scalar value from a JSON string.
  • JSON_QUERY(): Retrieves a JSON object or array from a JSON string.

How to Parse JSON in SQL Query

Let us see various examples of parsing JSON strings in SQL Server.

Parsing JSON with OPENJSON()

The OPENJSON() function is the most versatile tool for working with JSON in SQL Server. It can parse JSON text and return a result set that can be queried like a regular table.

Example 1: Basic JSON Parsing

Suppose you have the following JSON stored in a variable:

DECLARE @json NVARCHAR(MAX) =
    N'{
        "employees": [
            {"id": 1, "name": "John Doe", "role": "Manager"},
            {"id": 2, "name": "Jane Smith", "role": "Developer"}
        ]
    }';

To parse this JSON and extract the employee details, you can use OPENJSON() with a specified path:

SELECT id, name, role
FROM OPENJSON(@json, '$.employees')
WITH (
    id INT '$.id',
    name NVARCHAR(50) '$.name',
    role NVARCHAR(50) '$.role'
);

Output:

Explanation:

  • '$' denotes the root of the JSON object.
  • $.employees navigates to the employees array within the JSON.
  • The WITH clause specifies the schema for the JSON fields to be extracted.

Extracting Values with JSON_VALUE()

The function is ideal if you need to extract individual values from a JSON string.

Example 2: Extract a Specific Value

Using the same JSON example above, you can retrieve the name of the first employee:

SELECT JSON_VALUE(@json, '$.employees[0].name') AS FirstEmployeeName;

Output:

Extracting Nested Objects with JSON_QUERY()

The JSON_QUERY() function is used to extract complex objects or arrays from a JSON string.

Example 3: Extract an Array

To extract the entire employees array:

SELECT JSON_QUERY(@json, '$.employees') AS EmployeesArray;

Combining JSON Functions in Queries

You can combine these functions to manipulate and query JSON data effectively.

Example 4: Parsing JSON Stored in a Table

Assume you have a table named EmployeeData with a column EmployeeJSON that stores JSON strings.

CREATE TABLE EmployeeData (
    Id INT IDENTITY PRIMARY KEY,
    EmployeeJSON NVARCHAR(MAX)
);

INSERT INTO EmployeeData (EmployeeJSON)
VALUES
(N'{"id": 1, "name": "John Doe", "role": "Manager"}'),
(N'{"id": 2, "name": "Jane Smith", "role": "Developer"}');

To extract and display employee details:

SELECT
    JSON_VALUE(EmployeeJSON, '$.id') AS EmployeeId,
    JSON_VALUE(EmployeeJSON, '$.name') AS EmployeeName,
    JSON_VALUE(EmployeeJSON, '$.role') AS EmployeeRole
FROM EmployeeData;

Output:

Parsing Complex JSON Objects

SQL Server can handle deeply nested JSON structures. Let’s consider a more complex example:

Example 5: Parsing Nested JSON with Arrays and Objects

Suppose you have a JSON structure with nested objects:

DECLARE @complexJson NVARCHAR(MAX) =
    N'{
        "company": {
            "name": "TechCorp",
            "employees": [
                {
                    "id": 1,
                    "name": "John Doe",
                    "details": {
                        "role": "Manager",
                        "salary": 75000
                    }
                },
                {
                    "id": 2,
                    "name": "Jane Smith",
                    "details": {
                        "role": "Developer",
                        "salary": 60000
                    }
                }
            ]
        }
    }';

To extract employee details including nested properties:

SELECT id, name, role, salary
FROM OPENJSON(@complexJson, '$.company.employees')
WITH (
    id INT '$.id',
    name NVARCHAR(50) '$.name',
    role NVARCHAR(50) '$.details.role',
    salary INT '$.details.salary'
);

Explanation:

  • The path $.company.employees navigates to the employees array inside the company object.
  • Nested properties like role and salary are accessed using the full JSON path ($.details.role and $.details.salary).

Output:

Pro tips:
1. SQL Server’s JSON functions make it incredibly simple to parse and manipulate JSON data without requiring additional tools or complex workarounds. Whether you’re working with JSON stored in variables or tables, these functions provide powerful options for extracting and querying data.
2. Understanding these features simplifies JSON handling and enhances your ability to work effectively with modern data formats. Start leveraging SQL Server’s JSON capabilities in your next project to streamline your data workflows.
3. SQL Server 2025 private preview is announced. Read more about it here.

See more

Download Now
Kunal Rathi

With over 13 years of experience in data engineering and analytics, I've assisted countless clients in gaining valuable insights from their data. As a dedicated supporter of Data, Cloud and DevOps, I'm excited to connect with individuals who share my passion for this field. If my work resonates with you, we can talk and collaborate.

Shopping Cart
Scroll to Top