JSON Data Handling in SQL Server

In a real-time project, it is not easy to manage 100 columns in the table; here, in those situations, we need to add JSON to sql table columns, and then we need to manage it.

Example

Suppose we have some Application settings that need to be set up from the database, and it has very complex settings that keep changing so instead of creating multiple columns for those settings, we can club all settings or configurations into one JSON format and save them to a single Sql table column.

SQL Server provides support for handling JSON data, allowing you to store JSON in columns and perform operations on it. This can be particularly useful when dealing with semi-structured or unstructured data. Here are some examples to illustrate how to read JSON from SQL Server columns.

To handle JSON data in SQL Server

1. Store JSON in NVARCHAR(MAX) or VARCHAR(MAX) columns.
2. Insert JSON using regular INSERT statements.
3. Read and Parse JSON using JSON_VALUE, JSON_QUERY, and OPENJSON:
   • JSON_VALUE: Extracts a scalar value from JSON.
   • JSON_QUERY: Extracts an object or array.
   • OPENJSON: Parses JSON and returns a table of key-value pairs.
4. Modify JSON using JSON_MODIFY to update or add properties.
5. Aggregate Data into JSON using the FOR JSON clause.

Storing JSON Data

JSON data is typically stored in columns of type NVARCHAR(MAX) or VARCHAR(MAX).

Create table

CREATE TABLE ProductsData (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(50),
    ProductDetails NVARCHAR(MAX) -- JSON data stored in this column
);

Insert JSON on ProductDetails column

INSERT INTO ProductsData (ProductID, ProductName, ProductDetails)
VALUES
    (1, 'Laptop', 
        '{
            "Brand": "Dell", 
            "Specifications": {
                "Processor": "Intel i7", 
                "RAM": "16GB", 
                "Storage": "512GB SSD"
            }
        }'),
    (2, 'Smartphone', 
        '{
            "Brand": "Apple", 
            "Specifications": {
                "Model": "iPhone 12", 
                "Storage": "128GB"
            }
        }');

Simple Select

SELECT 
    ProductID, 
    ProductName, 
    ProductDetails
FROM 
    ProductsData;

Reading JSON Data

You can read JSON data from the table and parse it using built-in functions such as JSON_VALUE, JSON_QUERY, and OPENJSON.

Read SQL JSON Column

SELECT  
    ProductID,  
    ProductName,  
    JSON_VALUE(ProductDetails, '$.Brand') AS Brand,  
    JSON_VALUE(ProductDetails, '$.Specifications.Processor') AS Processor  
FROM  
    ProductsData;

Result

Extract Nested Objects or Arrays with JSON_QUERY

SELECT 
    ProductID, 
    ProductName, 
    JSON_QUERY(ProductDetails, '$.Specifications') AS Specifications
FROM 
    Products;

Expand JSON Arrays with OPENJSON

SQL

-- Insert data with JSON array
INSERT INTO ProductsData (ProductID, ProductName, ProductDetails)
VALUES
(3, 'Tablet', 
'{
    "Brand": "Samsung", 
    "Models": [
        {"Model": "Galaxy Tab S7", "Storage": "256GB"}, 
        {"Model": "Galaxy Tab S6", "Storage": "128GB"}
    ]
}');

-- Select data and expand JSON array
SELECT
    ProductID,
    ProductName,
    Models.Model,
    Models.Storage
FROM
    ProductsData
CROSS APPLY OPENJSON(ProductDetails, '$.Models')
WITH (
    Model NVARCHAR(50) '$.Model',
    Storage NVARCHAR(50) '$.Storage'
) AS Models;

Aggregating Data into JSON Format

SQL Server allows you to convert query results into JSON format using the FOR JSON clause.

SQL

SELECT 
    ProductID, 
    ProductName, 
    ProductDetails 
FROM 
    ProductsData 
FOR JSON PATH;

Conclusion

We have learned ways to read JSON from SQL Server columns.

Thanks!