Unlock the Power of JSON in SQL Server

Spread the love

JSON (JavaScript Object Notation) has become the most useful feature of modern applications. Using JSON, we can easily exchange data between systems. SQL Server provides native support for JSON. You can efficiently store, query, and manipulate JSON data in SQL Server. In this blog, we will explore how JSON works in SQL Server, provide examples of JSON, and discuss practical use cases of JSON.

Why JSON in SQL Server?

JSON’s lightweight format is ideal for data interchange, especially in web and mobile applications. SQL Server provides robust JSON functions to:

Store JSON data directly in tables.
Query specific parts of JSON documents.
Modify and validate JSON data.
Convert relational data to JSON and vice versa.

Storing JSON Data in SQL Server

You can store JSON data in a standard NVARCHAR column. Here’s how:

Example: Creating a Table with JSON Data

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    ProductDetails NVARCHAR(MAX) -- JSON data stored here
);

INSERT INTO Products (ProductID, ProductName, ProductDetails)
VALUES
(1, 'Mobile', '{"Brand": "Apple", "Model": "iPhone 16", "Price": 1000}'),
(2, 'TV', '{"Brand": "Samsung", "Model": "HD Smart LED TV", "Price": 500}');

Key Points:

JSON data is stored as text in NVARCHAR columns.
SQL Server does not have a native JSON datatype but provides functions to handle JSON data effectively.

JSON Formatter & Validator is an online tool to check if JSON is valid or not and also format the JSON. In this tool, you can paste the JSON and check it.

Querying JSON Data with SQL Server

Example 1: ISJSON

The ISJSON function checks if a given string is a valid JSON format.

SELECT 
    ProductName,
    CASE 
        WHEN ISJSON(ProductDetails) = 1 THEN 'Valid JSON'
        ELSE 'Invalid JSON'
    END AS ValidJson
FROM Products;

Output:

ProductName	ValidJson
Mobile	Valid JSON
TV	Valid JSON

Example 2: JSON_VALUE

The JSON_VALUE function retrieves a scalar value from a JSON document.

SELECT 
    ProductName,
    JSON_VALUE(ProductDetails, '$.Price') AS Price
FROM Products;

Output:

ProductName	Price
Mobile	1000
TV	500

Example 3: JSON_QUERY

Use JSON_QUERY to retrieve JSON objects or arrays.

SELECT 
    ProductName,
    JSON_QUERY(ProductDetails, '$') AS FullDetails
FROM Products;

Output:

ProductName	FullDetails
Mobile	{“Brand”: “Apple”, “Model”: “iPhone 16”, “Price”: 1000}
TV	{“Brand”: “Samsung”, “Model”: “HD Smart LED TV”, “Price”: 500}

Example 4: JSON_MODIFY

The JSON_MODIFY function allows you to update JSON data.

UPDATE Products
SET ProductDetails = JSON_MODIFY(ProductDetails, '$.Price', 1200)
WHERE ProductID = 1;

SELECT ProductName, ProductDetails FROM Products WHERE ProductID = 1;

Output:

ProductName	ProductDetails
Mobile	{“Brand”: “Apple”, “Model”: “iPhone 16”, “Price”: 1200}

Example 5: FOR JSON

Use FOR JSON to convert relational data into JSON format.

SELECT 
    ProductID,
    ProductName,
    ProductDetails
FROM Products
FOR JSON AUTO;

Output:

[
   {
      "ProductID":1,
      "ProductName":"Mobile",
      "ProductDetails":"{\"Brand\": \"Apple\", \"Model\": \"iPhone 16\", \"Price\": 1200}"
   },
   {
      "ProductID":2,
      "ProductName":"TV",
      "ProductDetails":"{\"Brand\": \"Samsung\", \"Model\": \"HD Smart LED TV\", \"Price\": 500}"
   }
]

Example 6: OPENJSON

OPENJSON transforms JSON data into a tabular format.

DECLARE @Json NVARCHAR(MAX) = '[
    {"EmployeeID": 1, "EmpName": "John Doe"},
    {"EmployeeID": 2, "EmpName": "Jane Smith"}
]';

SELECT *
FROM OPENJSON(@Json)
WITH (
    EmployeeID INT '$.EmployeeID',
    EmpName NVARCHAR(100) '$.EmpName'
);

Output:

EmployeeID	EmpName
1	John Doe
2	Jane Smith

Conclusion

Integrating JSON with SQL Server unlocks endless possibilities for modern applications. Whether you’re building APIs or managing e-commerce data, SQL Server’s JSON functions provide a powerful, flexible way to handle semi-structured data. Start practising with the examples provided and take your database skills to the next level!

Also Read,

Spread the love