JSON Data Types in SQL Server

This document provides a comprehensive overview of how to work with JSON data within SQL Server.

Introduction to JSON Support

SQL Server 2016 and later versions offer robust support for JSON data. You can store JSON text in VARCHAR, NVARCHAR, or VARCHAR(MAX), NVARCHAR(MAX) columns. The primary advantage of using JSON in SQL Server is the ability to store and retrieve semi-structured data efficiently and to query it using standard T-SQL syntax.

Key Functions for JSON

SQL Server provides a set of built-in functions to parse, manipulate, and query JSON data.

FOR JSON Clause

The FOR JSON clause transforms your query results into a JSON format. It has two modes:

• FOR JSON AUTO: Automatically formats the results as JSON based on the table structure.
• FOR JSON PATH: Provides more control over the JSON output structure.

Example: FOR JSON AUTO

SELECT
    CustomerID,
    FirstName,
    LastName,
    (SELECT OrderID, OrderDate FROM Orders WHERE CustomerID = Customers.CustomerID FOR JSON AUTO) AS Orders
FROM
    Customers
WHERE
    CustomerID = 1
FOR JSON AUTO;

Example: FOR JSON PATH

SELECT
    FirstName AS [Name.First],
    LastName AS [Name.Last],
    Email
FROM
    Customers
WHERE
    CustomerID = 1
FOR JSON PATH;

OPENJSON Function

OPENJSON is a table-valued function that parses JSON text and returns objects and properties from the JSON input as rows and columns.

Syntax

OPENJSON ( jsonExpression [ , path ] )
WITH ( column_list )

Example

DECLARE @json NVARCHAR(MAX) = N'[
    {"name":"Alice", "age":30, "city":"New York"},
    {"name":"Bob", "age":25, "city":"Los Angeles"}
]';

SELECT *
FROM OPENJSON(@json)
WITH (
    Name NVARCHAR(100) '$.name',
    Age INT '$.age',
    City NVARCHAR(100) '$.city'
);

JSON_VALUE Function

JSON_VALUE extracts a scalar value (string, number, boolean) from a JSON string based on a specified path.

Syntax

JSON_VALUE ( jsonExpression, path )

Example

DECLARE @json NVARCHAR(MAX) = N'{
    "user": {
        "name": "Charlie",
        "contact": {
            "email": "charlie@example.com"
        }
    }
}';

SELECT JSON_VALUE(@json, '$.user.name') AS UserName,
       JSON_VALUE(@json, '$.user.contact.email') AS UserEmail;

JSON_QUERY Function

JSON_QUERY extracts an object or an array from a JSON string based on a specified path.

Syntax

JSON_QUERY ( jsonExpression, path )

Example

DECLARE @json NVARCHAR(MAX) = N'{
    "user": {
        "name": "David",
        "addresses": [
            {"street": "123 Main St", "city": "Anytown"},
            {"street": "456 Oak Ave", "city": "Otherville"}
        ]
    }
}';

SELECT JSON_QUERY(@json, '$.user.addresses') AS UserAddresses;

ISJSON Function

ISJSON checks whether a string contains valid JSON.

Syntax

ISJSON ( expression )

Example

SELECT ISJSON('{"name":"Eve"}'); -- Returns 1 (true)
SELECT ISJSON('This is not JSON'); -- Returns 0 (false)

Performance Considerations

When working with large JSON datasets, consider the following:

• Indexing: While you cannot directly index JSON content, you can create computed columns based on extracted JSON values and then index those computed columns.
• Data Storage: Store JSON in NVARCHAR(MAX) for Unicode compatibility.
• Parsing Efficiency: Prefer OPENJSON with a defined schema (WITH clause) over using JSON_VALUE or JSON_QUERY repeatedly for better performance.

Use Cases

• Storing semi-structured data alongside relational data.
• Integrating with APIs that return or accept JSON.
• Enabling flexible data schemas that can evolve without constant table alterations.