Working with JSON

Introduction

JavaScript Object Notation (JSON) is a lightweight data-interchange format that is easy for humans to read and write. It is also widely used in web applications for transmitting structured data. Starting with SQL Server 2016, MS SQL introduced native support for handling JSON data, allowing you to store, parse, query, and manipulate JSON documents directly within SQL queries.

This Section covers how to work with JSON data in MS SQL, including parsing JSON data, storing it in tables, querying it, and converting relational data to JSON format.

Storing JSON Data

While MS SQL does not have a specific JSON data type, you can store JSON data in NVARCHAR columns.

Example: Create a table to store customer details in JSON format.

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    customer_name NVARCHAR(100),
    CustomerDetails NVARCHAR(MAX)
);

Here, CustomerDetails will store JSON data like the customer’s address, preferences, or contact information.

Inserting JSON Data into a Table

You can insert JSON data directly into the NVARCHAR column.

Example: Insert a customer with JSON-formatted details.

INSERT INTO Customers (customer_id, customer_name, CustomerDetails)
VALUES (1, 'John Doe', '{ "Address": "123 Main St", "Phone": "123-456-7890" }');

The CustomerDetails column stores the JSON object as a string.

Querying JSON Data Using JSON Functions

MS SQL provides built-in functions to query and manipulate JSON data.

1. JSON_VALUE()

This function extracts a scalar value from a JSON string.

Example: Retrieve a customer’s phone number from the JSON data.

SELECT customer_name, JSON_VALUE(CustomerDetails, '$.Phone') AS PhoneNumber
FROM Customers;

The $.Phone path points to the Phone key in the JSON object.

2. JSON_QUERY()

Use JSON_QUERY() to extract a JSON object or array from a JSON string.

Example: Retrieve the entire JSON object for a customer’s details.

SELECT customer_name, JSON_QUERY(CustomerDetails) AS CustomerDetailsJSON
FROM Customers;

This will return the full JSON object from the CustomerDetails column.

3. JSON_MODIFY()

• This function allows you to modify values in an existing JSON document.

Example: Update a customer’s phone number in the JSON field.

UPDATE Customers
SET CustomerDetails = JSON_MODIFY(CustomerDetails, '$.Phone', '987-654-3210')
WHERE customer_id = 1;

Here, the phone number in the JSON document is updated.

Converting Relational Data to JSON

MS SQL allows you to convert result sets from relational queries into JSON format using the FOR JSON clause.

Example: Convert customer records to JSON format.

SELECT customer_id, customer_name 
FROM Customers
FOR JSON AUTO;

The FOR JSON AUTO clause generates a JSON object with automatic structure based on the table and column names.

You can also use the FOR JSON PATH clause to customize the structure of the output.

Example: Customize the JSON output to include nested objects.

SELECT customer_id AS 'Customer.ID', 
       customer_name AS 'Customer.Name'
FROM Customers
FOR JSON PATH;

The output JSON will have a nested structure, with Customer as the parent object.

Parsing and Shredding JSON Data

The OPENJSON() function allows you to parse JSON data and transform it into tabular format. This is useful when you want to query or join JSON data with other tables.

Example: Parse JSON data stored in the CustomerDetails column and display it as separate columns.

SELECT customer_id, customer_name, 
       JSON_VALUE(CustomerDetails, '$.Address') AS Address, 
       JSON_VALUE(CustomerDetails, '$.Phone') AS Phone
FROM Customers;

You can also use OPENJSON() to shred more complex JSON objects.

Example: Parse an array inside a JSON object.

DECLARE @json NVARCHAR(MAX) = N'[
    { "ProductID": 1, "ProductName": "Product A" },
    { "ProductID": 2, "ProductName": "Product B" }
]';

SELECT *
FROM OPENJSON(@json)
WITH (
    ProductID INT '$.ProductID',
    ProductName NVARCHAR(100) '$.ProductName'
);

This example parses a JSON array and returns its contents as rows.

Handling JSON in API Integration

In modern web and mobile applications, JSON is often the format of choice for API communication. MS SQL’s JSON functions allow you to store and work with API responses or requests directly within your database.

Example: Store API response data for customer orders in JSON format.

INSERT INTO CustomerOrders (customer_id, OrderData)
VALUES (1, '{ "OrderID": 123, "Items": [ { "Product": "Laptop", "Price": 1200 }, { "Product": "Mouse", "Price": 20 } ] }');

You can query this data and extract relevant information using the same JSON functions.

Best Practices

• Indexing: Since MS SQL does not support indexing inside JSON fields, always index other fields (e.g., customer_id) for optimal performance.
• Use JSON for Semi-Structured Data: JSON is great for cases where data can vary widely between records. However, if all records share the same structure, relational design may be more efficient.
• Avoid Overuse: JSON fields are flexible but come with a performance cost compared to normal relational columns. Use them for cases where flexibility is critical.

Conclusion

MS SQL’s JSON support offers a powerful way to handle semi-structured data. By using functions like JSON_VALUE(), JSON_QUERY(), OPENJSON(), and FOR JSON, you can store, query, and manipulate JSON data directly in the database. This is especially useful in scenarios like customer management where you might need to store dynamic information such as preferences, orders, and interactions, without strict table structures.

Asked in Interview

• How do you store JSON data in SQL?
• How do you retrieve specific data from a JSON field?
• How do you combine data from JSON fields with data from relational columns in a single query?