T-SQL Syntax for JSON Nodes

Last updated: October 26, 2023

This document details the Transact-SQL (T-SQL) syntax used to manipulate and query JSON data within SQL Server. Understanding these constructs is crucial for working with semi-structured data effectively.

JSON Functions and Syntax

SQL Server provides a rich set of functions and syntax extensions to handle JSON data. These include creating JSON text from relational data, parsing JSON text into relational format, and validating JSON data.

1. Creating JSON Text with FOR JSON

The FOR JSON clause transforms relational data from a SELECT statement into a JSON string. You can specify the output format (AUTO, PATH, ROOT).

Syntax:

SELECT column1, column2, ...
FROM your_table
WHERE ...
FOR JSON PATH | AUTO | ROOT('root_name') [, WITHOUT_ARRAY_WRAPPER]

Example (FOR JSON PATH):

-- Select data and format as JSON with PATH
SELECT
    ProductID AS id,
    Name AS productName,
    ListPrice AS price
FROM Production.Product
WHERE ProductID BETWEEN 1 AND 3
FOR JSON PATH;

This would generate output like:

[
  {"id":1,"productName":"Adjustable Race","price":0.00},
  {"id":2,"productName":"Bearing Ball","price":0.00},
  {"id":3,"productName":"BB Ball Bearing","price":0.00}
]

2. Parsing JSON Text with OPENJSON

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 ( schema_definition ) ]

The schema_definition specifies the columns, their data types, and the JSON path to the values for each column.

Example:

-- Parse a JSON string into a table
DECLARE @json NVARCHAR(MAX) = N'[
  {"ProductID": 1, "Name": "Adjustable Race", "Price": 0.00},
  {"ProductID": 2, "Name": "Bearing Ball", "Price": 0.00}
]';

SELECT *
FROM OPENJSON(@json)
WITH (
    ProductID INT '$.ProductID',
    ProductName NVARCHAR(100) '$.Name',
    Price DECIMAL(10,2) '$.Price'
);

This query would return:

ProductID	ProductName	Price
1	Adjustable Race	0.00
2	Bearing Ball	0.00

3. JSON Path Expressions

JSON Path expressions are used within functions like JSON_VALUE, JSON_QUERY, and OPENJSON to navigate and select specific elements within a JSON document.

Common Path Elements:

• $: Represents the root of the JSON document.
• .propertyName: Selects the value of a property within an object.
• [index]: Selects an element from an array by its zero-based index.
• .*: Selects all properties of an object.
• [*]: Selects all elements of an array.

Example:

-- Extracting a specific value from JSON
DECLARE @json NVARCHAR(MAX) = N'{
    "customer": {
        "name": "Alice",
        "orders": [
            {"orderId": "A101", "total": 50.75},
            {"orderId": "B202", "total": 120.00}
        ]
    }
}';

SELECT
    JSON_VALUE(@json, '$.customer.name') AS CustomerName,
    JSON_VALUE(@json, '$.customer.orders[0].orderId') AS FirstOrderId,
    JSON_QUERY(@json, '$.customer.orders') AS AllOrders;

4. JSON Querying Functions

SQL Server offers functions to extract scalar values and JSON fragments from JSON text.

• JSON_VALUE(expression, path): Extracts a scalar value (string, number, boolean, null) from a JSON string.
• JSON_QUERY(expression, [path]): Extracts an object or an array from a JSON string.
• ISJSON(expression): Checks if a string contains valid JSON.

Example:

-- Using JSON functions to query data
DECLARE @productInfo NVARCHAR(MAX) = N'{
    "product": {
        "name": "Laptop",
        "specifications": {
            "cpu": "Intel i7",
            "ram_gb": 16,
            "storage_options": ["512GB SSD", "1TB SSD"]
        },
        "available": true
    }
}';

SELECT
    JSON_VALUE(@productInfo, '$.product.name') AS ProductName,
    JSON_VALUE(@productInfo, '$.product.specifications.ram_gb') AS RAM,
    JSON_QUERY(@productInfo, '$.product.storage_options') AS StorageOptions,
    ISJSON(@productInfo) AS IsValidJson;

5. Modifying JSON Data

While SQL Server excels at querying and parsing JSON, direct in-place modification of JSON strings within columns is typically done by reading the JSON, modifying it in T-SQL variables, and then writing it back. However, functions like JSON_MODIFY (available in newer versions) can assist with targeted updates.

JSON_MODIFY (SQL Server 2017+):

JSON_MODIFY can be used to insert, update, or delete a value within a JSON string.

JSON_MODIFY ( expression, path, newValue )

Best Practices

• Use ISJSON to validate input before processing.
• Choose the appropriate FOR JSON mode (PATH, AUTO, ROOT) based on your output requirements.
• Specify explicit schemas in OPENJSON WITH for better performance and data type control.
• Understand JSON Path syntax thoroughly for precise data extraction.
• Consider the performance implications of querying large JSON documents.