Handling JSON Data type in SQL Server

     In today's world, JSON (JavaScript Object Notation) has become the standard for data interchange across various applications. SQL Server, being one of the most popular relational database management systems, provides support for JSON data types and related functions. In this blog, we will explore how to handle JSON data in SQL Server using a real-world use case.

Use case scenario

Suppose we have a database of customers and their orders. Each customer has a unique identifier, name, address, and a list of orders they have made. Each order has an order identifier, date, product name, and quantity. We have received a JSON file containing the customer data and their orders, and we need to import it into our SQL Server database.

Importing JSON data

We can import JSON data into SQL Server using the OPENROWSET function, which allows us to read data from a file into a table. The following code shows how to import the JSON data into a table:

SELECT *

FROM OPENROWSET(BULK 'C:\jsondata.json', SINGLE_CLOB) AS jdata

CROSS APPLY OPENJSON(jdata, '$.customers')

WITH (

    CustomerId int '$.id',

    Name nvarchar(100) '$.name',

    Address nvarchar(200) '$.address',

    Orders nvarchar(MAX) '$.orders' AS JSON

) AS customers;

In this code, we are using the OPENROWSET function to read the JSON file into a single CLOB (character large object) value. Then, we use the OPENJSON function to parse the JSON data and create a table with columns for the customer's ID, name, address, and orders. The Orders column is defined as a JSON data type.

Querying JSON data

Once we have imported the JSON data into the SQL Server database, we can use various functions to query and manipulate it. Here are a few examples:

-- Retrieve all customers with their orders

SELECT *

FROM Customers;

-- Retrieve the names of all customers

SELECT Name

FROM Customers;

-- Retrieve the total number of orders for each customer

SELECT Name, JSON_VALUE(Orders, '$.length') AS OrderCount

FROM Customers;

-- Retrieve the details of all orders made by a specific customer

SELECT *

FROM Customers

WHERE CustomerId = 123

CROSS APPLY OPENJSON(Orders)

WITH (

    OrderId int '$.id',

    Date date '$.date',

    ProductName nvarchar(100) '$.product_name',

    Quantity int '$.quantity'

) AS orders;

In these examples, we are using various functions such as OPENJSON, JSON_VALUE, and CROSS APPLY to extract data from the JSON column and perform various operations on it. We can also use functions such as JSON_QUERY and JSON_MODIFY to modify the JSON data and update the table.

Conclusion

In this blog, we have seen how to handle JSON data in SQL Server using a real-world use case. By using the built-in functions and data types provided by SQL Server, we can easily import, query, and manipulate JSON data in our database. Whether we are working with data from web applications, mobile apps, or other systems that use JSON data, SQL Server provides a powerful tool for managing and processing JSON data.