Need help with your JSON?

Try our JSON Formatter tool to automatically identify and fix syntax errors in your JSON. JSON Formatter tool

SQL Server JSON Functions and Formatting Options

Introduction: Why JSON in SQL Server?

Modern applications frequently exchange data using the JSON format. SQL Server, starting with SQL Server 2016, provides native functions to handle JSON data directly within the database engine. This allows you to:

Format query results as JSON.
Parse JSON strings stored in database columns.
Query values and objects within JSON text.
Modify values in JSON strings.

Leveraging these functions can simplify your application code, move data transformation logic closer to the data source, and improve performance in certain scenarios.

Generating JSON from SQL Queries (FOR JSON)

The FOR JSON clause is appended to aSELECT statement to format the results as JSON text. It comes with several modes: AUTO,PATH, and RAW.

FOR JSON AUTO

AUTO mode automatically structures the JSON output based on the tables and columns in the SELECT statement. It's the simplest to use but offers less control over the structure compared to PATH.

SELECT
    CustomerID,
    Name,
    Email
FROM
    Customers
FOR JSON AUTO;

Example Output (might vary slightly based on data):

[
  {
    "CustomerID": 1,
    "Name": "Alice",
    "Email": "alice@example.com"
  },
  {
    "CustomerID": 2,
    "Name": "Bob",
    "Email": "bob@example.com"
  }
]

With Joins, AUTO mode will nest based on table joins:

SELECT
    c.CustomerID,
    c.Name,
    o.OrderID,
    o.OrderDate
FROM
    Customers c
JOIN
    Orders o ON c.CustomerID = o.CustomerID
ORDER BY
    c.CustomerID
FOR JSON AUTO;

Example Output:

[
  {
    "CustomerID": 1,
    "Name": "Alice",
    "Orders": [
      {
        "OrderID": 101,
        "OrderDate": "2023-01-15T00:00:00"
      },
      {
        "OrderID": 105,
        "OrderDate": "2023-02-20T00:00:00"
      }
    ]
  },
  {
    "CustomerID": 2,
    "Name": "Bob",
    "Orders": [
      {
        "OrderID": 102,
        "OrderDate": "2023-01-16T00:00:00"
      }
    ]
  }
]

FOR JSON PATH

PATH mode gives you full control over the JSON structure using column aliases formatted as paths (e.g., '$.path.to.property'). This is more flexible for complex or specific JSON outputs.

SELECT
    CustomerID AS "$.id",
    Name AS "$.details.fullName",
    Email AS "$.contact.email"
FROM
    Customers
FOR JSON PATH;

Example Output:

[
  {
    "id": 1,
    "details": {
      "fullName": "Alice"
    },
    "contact": {
      "email": "alice@example.com"
    }
  },
  {
    "id": 2,
    "details": {
      "fullName": "Bob"
    },
    "contact": {
      "email": "bob@example.com"
    }
  }
]

Nesting with PATH mode using subqueries or joins:

SELECT
    c.CustomerID AS "id",
    c.Name AS "name",
    (
        SELECT
            o.OrderID AS "$.orderId",
            o.OrderDate AS "$.orderDate"
        FROM
            Orders o
        WHERE
            o.CustomerID = c.CustomerID
        FOR JSON PATH
    ) AS "orders" -- Alias names the JSON array property
FROM
    Customers c
FOR JSON PATH;

Example Output:

[
  {
    "id": 1,
    "name": "Alice",
    "orders": [
      {
        "orderId": 101,
        "orderDate": "2023-01-15T00:00:00"
      },
      {
        "orderId": 105,
        "orderDate": "2023-02-20T00:00:00"
      }
    ]
  },
  {
    "id": 2,
    "name": "Bob",
    "orders": [
      {
        "orderId": 102,
        "orderDate": "2023-01-16T00:00:00"
      }
    ]
  }
]

FOR JSON RAW

RAW mode generates a JSON array where each element is the value of a single column. It's typically used when you only select one column and want a simple array of values.

SELECT
    Name
FROM
    Customers
FOR JSON RAW;

Example Output:

[
  "Alice",
  "Bob"
]

FOR JSON Formatting Options

Various options can be combined with FOR JSON to control the output format:

WITHOUT_ARRAY_WRAPPER: Removes the default outer JSON array brackets []. Useful when you expect a single JSON object.
INCLUDE_NULL_VALUES: Includes properties with NULL values. By default, they are omitted.
ROOT('ElementName'): Adds a single root key (or element name) to the JSON output, wrapping the result.
AS JSON: Casts the output to a JSON type, useful for chaining functions or storing JSON.

SELECT
    CustomerID,
    Name,
    -- Assuming some customers might have NULL email
    Email
FROM
    Customers
WHERE CustomerID = 1 -- Select a single customer
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER, INCLUDE_NULL_VALUES, ROOT('CustomerData');

Example Output (if Email is NULL for CustomerID 1):

{
  "CustomerData": {
    "CustomerID": 1,
    "Name": "Alice",
    "Email": null
  }
}

Parsing and Querying JSON (OPENJSON, JSON_VALUE, JSON_QUERY)

SQL Server provides functions to read and extract data from JSON strings.

OPENJSON

OPENJSON is a table-valued function that parses JSON text and returns data in rows and columns. It's particularly useful for shredding JSON arrays or objects into a relational format.

Basic OPENJSON usage (default schema):

Without a WITH clause, it returns key, value, and type for each element at the first level of the JSON structure.

DECLARE @jsonInfo NVARCHAR(MAX);
SET @jsonInfo = N'{
  "name": "Product A",
  "price": 19.99,
  "tags": ["electronics", "gadget"],
  "details": {
    "weight": "1kg",
    "color": "black"
  }
}';

SELECT *
FROM OPENJSON(@jsonInfo);

Example Output:

key       value                           type
--------- ------------------------------- ------
name      Product A                       1
price     19.99                           2
tags      ["electronics","gadget"]        4  -- Type 4 is array
details   {"weight":"1kg","color":"black"} 5  -- Type 5 is object

(Types: 0=null, 1=string, 2=number, 3=boolean, 4=array, 5=object)

OPENJSON with explicit schema (WITH clause):

This is more common, defining the expected columns, their SQL data types, and the JSON path to the corresponding value.

DECLARE @jsonInfo NVARCHAR(MAX);
SET @jsonInfo = N'{
  "name": "Product A",
  "price": 19.99,
  "inStock": true,
  "releaseDate": "2023-01-01T00:00:00Z",
  "details": { "weight": "1kg" }
}';

SELECT name, price, inStock, releaseDate, weight
FROM OPENJSON(@jsonInfo)
WITH (
    name VARCHAR(100) '$.name',
    price DECIMAL(10, 2) '$.price',
    inStock BIT '$.inStock',
    releaseDate DATETIME2 '$.releaseDate',
    -- Extract from nested object
    weight VARCHAR(50) '$.details.weight'
);

Example Output:

name        price   inStock releaseDate               weight
----------- ------- ------- ------------------------- ------
Product A   19.99   1       2023-01-01 00:00:00.0000000 1kg

Extracting arrays with OPENJSON:

You can use OPENJSON with CROSS APPLY to shred an array property within a JSON column.

-- Assume a table 'Products' with a NVARCHAR(MAX) column 'TagsJson'
-- Example data: INSERT INTO Products (TagsJson) VALUES ('["electronics", "gadget", "popular"]');

-- Select Product ID and individual tags
SELECT
    ProductID,
    TagValue
FROM
    Products
CROSS APPLY OPENJSON(TagsJson) WITH (TagValue VARCHAR(50) '$'); -- '$' means extract each element

Example Output:

ProductID   TagValue
----------- ---------
1           electronics
1           gadget
1           popular

JSON_VALUE vs JSON_QUERY

These functions extract values from a JSON string without necessarily shredding the whole document into rows.

JSON_VALUE(jsonString, path): Extracts a scalar value (string, number, boolean) from the JSON string at the specified path. Returns NULL if the path doesn't exist or points to an object/array.
JSON_QUERY(jsonString, path): Extracts a JSON object or array from the JSON string at the specified path. Returns NULL if the path doesn't exist or points to a scalar value.

DECLARE @jsonDoc NVARCHAR(MAX);
SET @jsonDoc = N'{
  "id": 123,
  "customer": {
    "name": "Alice",
    "city": "London"
  },
  "items": [
    {"itemId": 1, "qty": 2},
    {"itemId": 2, "qty": 1}
  ]
}';

SELECT
    JSON_VALUE(@jsonDoc, '$.id') AS OrderId, -- Scalar
    JSON_VALUE(@jsonDoc, '$.customer.name') AS CustomerName, -- Scalar
    JSON_VALUE(@jsonDoc, '$.customer.city') AS CustomerCity, -- Scalar
    JSON_VALUE(@jsonDoc, '$.items[0].itemId') AS FirstItemId, -- Scalar from array element
    JSON_VALUE(@jsonDoc, '$.items') AS Items_JSON_VALUE_Result, -- Returns NULL (items is an array)
    JSON_QUERY(@jsonDoc, '$.customer') AS CustomerObject, -- Object
    JSON_QUERY(@jsonDoc, '$.items') AS ItemsArray, -- Array
    JSON_QUERY(@jsonDoc, '$.customer.name') AS CustomerName_JSON_QUERY_Result; -- Returns NULL (name is scalar)

Example Output:

OrderId CustomerName CustomerCity FirstItemId Items_JSON_VALUE_Result CustomerObject                      ItemsArray                                   CustomerName_JSON_QUERY_Result
--------- ------------ ------------ ----------- ------------------------- --------------------------------- -------------------------------------------- ------------------------------
123       Alice        London       1           NULL                      {"name":"Alice","city":"London"}  [{"itemId":1,"qty":2},{"itemId":2,"qty":1}] NULL

Use JSON_VALUE for simple properties, JSON_QUERY for objects or arrays.

Path Mode (Lax vs Strict):

JSON paths support lax (default) and strict modes.

lax: Returns NULL if a path step doesn't exist or is invalid.
strict: Raises an error if a path step doesn't exist or is invalid.

DECLARE @jsonDoc NVARCHAR(MAX) = N'{"name": "Test"}';

-- Lax mode (default) - returns NULL for non-existent path
SELECT JSON_VALUE(@jsonDoc, '$.address.city') AS LaxResult;

-- Strict mode - raises an error for non-existent path
-- SELECT JSON_VALUE(@jsonDoc, 'strict $.address.city') AS StrictResult;

Lax result:

LaxResult
---------
NULL

Strict mode is useful when you want to ensure the JSON conforms to an expected structure.

Modifying JSON Data (JSON_MODIFY)

The JSON_MODIFY(jsonString, path, newValue) function updates a value at a specific path within a JSON string and returns the modified JSON string.

DECLARE @jsonDoc NVARCHAR(MAX);
SET @jsonDoc = N'{
  "name": "Product A",
  "price": 19.99,
  "details": { "color": "black" }
}';

-- Update existing value
SET @jsonDoc = JSON_MODIFY(@jsonDoc, '$.price', 24.99);
-- Add new value (if path doesn't exist)
SET @jsonDoc = JSON_MODIFY(@jsonDoc, '$.inStock', 1);
-- Add value in nested object (creates object if it doesn't exist in lax mode)
SET @jsonDoc = JSON_MODIFY(@jsonDoc, '$.details.weight', '1.2kg');
-- Delete value (set newValue to NULL)
SET @jsonDoc = JSON_MODIFY(@jsonDoc, '$.details.color', NULL);

SELECT @jsonDoc AS ModifiedJson;

Example Output:

{
  "name": "Product A",
  "price": 24.99,
  "details": {
    "weight": "1.2kg"
  },
  "inStock": 1
}

JSON_MODIFY with Arrays:

You can modify array elements or append to arrays using JSON_MODIFY.

DECLARE @jsonDoc NVARCHAR(MAX);
SET @jsonDoc = N'{
  "tags": ["electronics", "gadget"]
}';

-- Append a value to the array
SET @jsonDoc = JSON_MODIFY(@jsonDoc, 'append $.tags', 'popular');
-- Update an element by index (0-based)
SET @jsonDoc = JSON_MODIFY(@jsonDoc, '$.tags[0]', 'hardware');

SELECT @jsonDoc AS ModifiedArrayJson;

Example Output:

{
  "tags": [
    "hardware",
    "gadget",
    "popular"
  ]
}

Note the use of 'append $.tags' to add to the end of the array.

Validating JSON Data (ISJSON)

The ISJSON(jsonString) function checks if a string contains valid JSON. It returns 1 if the string is valid JSON, 0 if it's invalid, and NULL if the input string is NULL.

SELECT
    ISJSON('{"name": "valid"}') AS IsValid1,
    ISJSON('[1, 2, 3]') AS IsValid2,
    ISJSON('invalid json') AS IsValid3,
    ISJSON(NULL) AS IsValidNull;

Example Output:

IsValid1    IsValid2    IsValid3    IsValidNull
----------- ----------- ----------- -----------
1           1           0           NULL

This is useful for validation constraints or before attempting to parse JSON columns.

Performance Considerations

Working with JSON data in SQL Server can sometimes have performance implications, especially with large JSON documents or frequent querying/modification.

**Indexing JSON properties:** You can create indexes on specific properties within JSON columns to speed up queries using JSON_VALUE or JSON_QUERY. This is typically done by creating computed columns based on the JSON properties and then indexing the computed columns.
```
-- Add a computed column for a JSON property
ALTER TABLE Products
ADD ProductName AS JSON_VALUE(ProductDetailsJson, '$.name')

-- Create an index on the computed column
CREATE INDEX IX_Products_ProductName
ON Products(ProductName);
```
**Data Types:** Storing JSON in NVARCHAR(MAX) is standard. Ensure you use appropriate SQL types when extracting data with OPENJSON WITH.
**Avoid Shredding Large JSON Repeatedly:** If you frequently need to access data within a JSON document, consider extracting commonly accessed properties into standard columns and indexing them, rather than repeatedly using OPENJSON or JSON_VALUE on the raw JSON string.
**Hardware:** As with any database operation, sufficient CPU, memory, and fast storage are crucial for performance, especially when processing large amounts of JSON data.

Summary and Best Practices

**Use FOR JSON** to easily format query results for web services or applications expecting JSON. Use AUTO for simplicity, PATH for precise structure control.
**Use OPENJSON** when you need to "shred" JSON data into a relational table format, often for querying, filtering, or joining with other relational data.
**Use JSON_VALUE and JSON_QUERY** for extracting specific scalar values or JSON fragments without converting the whole document to rows. Remember the difference between scalar and object/array extraction.
**Use JSON_MODIFY** for targeted updates within a JSON string without needing to read and rewrite the entire content.
**Use ISJSON** to validate JSON data, especially before attempting parsing or modification.
Consider computed columns and indexing for performance-critical queries on JSON properties.
Store JSON data in NVARCHAR(MAX) columns.

Putting It Together: An Example

Let's imagine a scenario where we have customer data with a JSON column storing preferences, and we want to list customers who prefer email notifications and format the output with their orders included as a nested array.

-- Assume table Customers with columns CustomerID INT, Name NVARCHAR(100), PreferencesJson NVARCHAR(MAX)
-- Assume table Orders with columns OrderID INT, CustomerID INT, OrderDate DATETIME2

-- Filter customers based on a JSON property and format output with nested orders
SELECT
    c.CustomerID AS "id",
    c.Name AS "name",
    JSON_VALUE(c.PreferencesJson, '$.newsletter.email') AS "preferences.emailNewsletter", -- Extract scalar from JSON
    (
        SELECT
            o.OrderID AS "$.orderId",
            o.OrderDate AS "$.orderDate"
        FROM
            Orders o
        WHERE
            o.CustomerID = c.CustomerID
        FOR JSON PATH
    ) AS "orders" -- Nest orders as an array
FROM
    Customers c
WHERE
    ISJSON(c.PreferencesJson) = 1 -- Ensure JSON is valid
    AND JSON_VALUE(c.PreferencesJson, '$.newsletter.email') = 'true' -- Filter based on JSON property
FOR JSON PATH, ROOT('CustomersWithEmailPrefs'); -- Format as root object containing an array

This example combines ISJSON for validation, JSON_VALUE for filtering, and FOR JSON PATH with a subquery for nested output, demonstrating how these functions can work together.

Conclusion

SQL Server's native JSON functions provide powerful capabilities for integrating JSON data into your relational database workflows. Whether you're generating JSON for APIs, parsing data from external sources, or querying/modifying JSON stored within your tables, these functions offer flexible and efficient ways to handle this popular data format directly within SQL Server. Understanding the different functions and formatting options allows developers to choose the most appropriate tools for their specific JSON-related tasks.

Need help with your JSON?

Try our JSON Formatter tool to automatically identify and fix syntax errors in your JSON. JSON Formatter tool