JSON Querying and Extracting Values

Modern applications frequently exchange data in JSON (JavaScript Object Notation) format because it is lightweight, human-readable, and supported across almost every programming language. While SQL Server is a relational database management system, it also provides robust support for working with JSON documents without requiring a separate NoSQL database.

Starting with SQL Server 2016, Microsoft introduced a rich set of JSON functions that allow developers to store, query, validate, and manipulate JSON data directly within SQL queries. This capability is particularly useful when working with APIs, configuration settings, application logs, or semi-structured data.

In this article, we’ll explore how to query JSON data in SQL Server and extract values efficiently using the built-in JSON functions.

Why Store JSON in SQL Server?

Although relational tables remain the preferred way to store structured data, JSON offers flexibility when dealing with dynamic or changing schemas.

Some common scenarios include:

Storing API responses
Saving user preferences
Managing product attributes
Logging application events
Importing third-party data

SQL Server stores JSON as plain NVARCHAR text. Unlike XML, there is no dedicated JSON data type. Instead, SQL Server validates and processes JSON using specialized functions.

Consider the following JSON document:

{
    "EmployeeID": 101,
    "Name": "John Smith",
    "Department": "Sales",
    "Salary": 65000,
    "Address": {
        "City": "New York",
        "State": "NY"
    },
    "Skills": [
        "SQL",
        "Power BI",
        "Azure"
    ]
}

Suppose this JSON is stored in a variable:

DECLARE @Employee NVARCHAR(MAX);

SET @Employee =
'{
    "EmployeeID":101,
    "Name":"John Smith",
    "Department":"Sales",
    "Salary":65000,
    "Address":{
        "City":"New York",
        "State":"NY"
    },
    "Skills":[
        "SQL",
        "Power BI",
        "Azure"
    ]
}';

Now let’s see how SQL Server can query this data.

Extracting Scalar Values with JSON_VALUE()

The JSON_VALUE() function extracts a single scalar value from a JSON document.

Syntax:

JSON_VALUE(expression, path)

Example:

SELECT JSON_VALUE(@Employee, '$.Name') AS EmployeeName;

Result:

John Smith

Retrieve the department:

SELECT JSON_VALUE(@Employee, '$.Department');

Retrieve the salary:

SELECT JSON_VALUE(@Employee, '$.Salary');

Access a nested object:

SELECT JSON_VALUE(@Employee, '$.Address.City') AS City;

Output:

New York

The JSON path uses the $ symbol to represent the root of the JSON document.

Extracting JSON Objects Using JSON_QUERY()

While JSON_VALUE() returns scalar values, JSON_QUERY() returns JSON objects or arrays.

Syntax:

JSON_QUERY(expression, path)

Retrieve the address object:

SELECT JSON_QUERY(@Employee, '$.Address') AS Address;

Output:

{
    "City":"New York",
    "State":"NY"
}

Retrieve the skills array:

SELECT JSON_QUERY(@Employee, '$.Skills') AS Skills;

Output:

[
    "SQL",
    "Power BI",
    "Azure"
]

If you try to use JSON_VALUE() on an array or object, SQL Server returns NULL because it expects a scalar value.

Reading Array Elements

JSON arrays are indexed starting at zero.

Retrieve the first skill:

SELECT JSON_VALUE(@Employee, '$.Skills[0]');

Result:

SQL

Retrieve the second skill:

SELECT JSON_VALUE(@Employee, '$.Skills[1]');

Result:

Power BI

Retrieve the third skill:

SELECT JSON_VALUE(@Employee, '$.Skills[2]');

Result:

Azure

Array indexing makes it easy to retrieve specific elements without parsing the entire document.

Parsing JSON into Rows Using OPENJSON()

One of SQL Server’s most powerful JSON features is the OPENJSON() function.

It converts JSON into relational rows and columns.

Example:

SELECT * FROM OPENJSON(@Employee);

Output:

Key	Value	Type
EmployeeID	101	Number
Name	John Smith	String
Department	Sales	String
Salary	65000	Number
Address	{…}	Object
Skills	[…]	Array

This function is extremely useful when importing JSON into relational tables.

Parsing Arrays with OPENJSON()

Suppose you want to retrieve every skill as an individual row.

SELECT value AS Skill
FROM OPENJSON(@Employee, '$.Skills');

Output:

Skill
SQL
Power BI
Azure

This approach is ideal for reporting, filtering, and joining JSON data with relational tables.

Using OPENJSON with a Schema

Instead of returning generic columns (key, value, and type), you can define your own schema.

Example:

SELECT *
FROM OPENJSON(@Employee)
WITH
(
    EmployeeID INT,
    Name NVARCHAR(100),
    Department NVARCHAR(50),
    Salary DECIMAL(10,2)
);

Output:

EmployeeID	Name	Department	Salary
101	John Smith	Sales	65000.00

This method simplifies data extraction and improves readability.

Validating JSON with ISJSON()

Before querying JSON, it’s good practice to verify that the stored text is valid.

Example:

SELECT ISJSON(@Employee);

Result:

A return value of:

1 indicates valid JSON.
0 indicates invalid JSON.

This function helps prevent runtime errors when processing user-supplied or external data.

Updating JSON Values

SQL Server also allows updating values within a JSON document using JSON_MODIFY().

Example:

SET @Employee =
JSON_MODIFY(@Employee, '$.Salary', 70000);

Verify the update:

SELECT JSON_VALUE(@Employee, '$.Salary');

Result:

Update the city:

SET @Employee =
JSON_MODIFY(@Employee, '$.Address.City', 'Chicago');

Retrieve the updated city:

SELECT JSON_VALUE(@Employee, '$.Address.City');

Output:

Chicago

Querying JSON Stored in a Table

Suppose you have a table storing customer information as JSON.

CREATE TABLE Customers
(
    CustomerID INT,
    CustomerInfo NVARCHAR(MAX)
);

Insert sample data:

INSERT INTO Customers
VALUES
(
1,
'{
    "Name":"Alice",
    "Country":"USA",
    "Age":30
}'
);

Retrieve the customer’s name:

SELECT
    CustomerID,
    JSON_VALUE(CustomerInfo, '$.Name') AS CustomerName
FROM Customers;

Filter customers from the USA:

SELECT *
FROM Customers
WHERE JSON_VALUE(CustomerInfo, '$.Country') = 'USA';

This demonstrates that JSON fields can be queried directly within SQL statements, making it possible to filter, sort, and aggregate semi-structured data.

Best Practices

When working with JSON in SQL Server, consider these recommendations:

Validate incoming JSON using ISJSON() before processing it.
Use JSON_VALUE() for extracting single scalar values.
Use JSON_QUERY() for retrieving complete objects or arrays.
Use OPENJSON() when converting JSON into relational rows and columns.
Create computed columns from frequently accessed JSON properties and index them to improve query performance.
Avoid storing highly structured relational data as JSON unless flexibility is required, as traditional table designs generally provide better performance and enforce stronger data integrity.

Conclusion

SQL Server’s JSON capabilities bridge the gap between relational and semi-structured data, allowing developers to integrate modern application data without leaving the SQL environment. Functions such as JSON_VALUE(), JSON_QUERY(), OPENJSON(), JSON_MODIFY(), and ISJSON() make it straightforward to extract values, parse arrays, validate documents, and update JSON content.

Whether you’re consuming REST APIs, storing configuration data, or importing external datasets, these built-in functions help you work with JSON efficiently while continuing to leverage SQL Server’s powerful querying and indexing capabilities. By understanding when to use each function and following best practices, you can build applications that effectively combine the flexibility of JSON with the reliability and performance of a relational database.

Why Store JSON in SQL Server?

Extracting Scalar Values with JSON_VALUE()

Extracting JSON Objects Using JSON_QUERY()

Reading Array Elements

Parsing JSON into Rows Using OPENJSON()

Parsing Arrays with OPENJSON()

Using OPENJSON with a Schema

Validating JSON with ISJSON()

Updating JSON Values

Querying JSON Stored in a Table

Best Practices

Conclusion

SQL Tutorial