Fetching Data from JSON Arrays in SQL Server: A Practical Guide

In today’s modern applications, it's common to encounter JSON data structures, whether you're working with APIs, external services, or storing application configurations. SQL Server, starting from version 2016, introduced built-in support for JSON, allowing us to parse and work with JSON data directly within our database queries.

In this blog, I will guide you through the process of fetching data from a JSON array stored in SQL Server.

---

What is JSON in SQL Server?

JSON (JavaScript Object Notation) is a lightweight data interchange format. SQL Server doesn’t have a dedicated JSON data type like some other databases (e.g., PostgreSQL), but it allows you to store JSON data in NVARCHAR columns. More importantly, SQL Server provides several functions that make it easy to extract information from JSON data.

JSON Functions in SQL Server

SQL Server offers a number of functions to parse JSON data, such as:

1. OPENJSON() – Parses JSON text and returns objects and properties from the JSON as rows and columns.
2. JSON_VALUE() – Extracts a scalar value from a JSON string.
3. JSON_QUERY() – Returns a JSON fragment from a JSON string.

---

Example Scenario

Let’s assume we have a JSON array of orders stored in a column within our SQL Server table. Here's an example of what the JSON array might look like:

json
[
    {
        "orderId": 101,
        "product": "Laptop",
        "quantity": 2,
        "price": 1500
    },
    {
        "orderId": 102,
        "product": "Headphones",
        "quantity": 1,
        "price": 200
    },
    {
        "orderId": 103,
        "product": "Monitor",
        "quantity": 3,
        "price": 300
    }
]

This JSON data might be stored in a column called OrderDetails in a table named CustomerOrders.

---

Step-by-Step: Fetching Data from a JSON Array

Step 1: Storing JSON Data

Let’s first create a simple table to store customer orders, with a column OrderDetails to hold the JSON array:

sql
CREATE TABLE CustomerOrders
(
    Id INT PRIMARY KEY,
    CustomerName NVARCHAR(100),
    OrderDetails NVARCHAR(MAX)
);

We will insert the JSON array into this table:

sql
INSERT INTO CustomerOrders (Id, CustomerName, OrderDetails)
VALUES (1, 'John Doe', '[
    {"orderId": 101, "product": "Laptop", "quantity": 2, "price": 1500},
    {"orderId": 102, "product": "Headphones", "quantity": 1, "price": 200},
    {"orderId": 103, "product": "Monitor", "quantity": 3, "price": 300}
]');

Step 2: Using OPENJSON() to Parse JSON Data

To extract data from the JSON array, we can use the OPENJSON() function. This function parses the JSON text and returns the rows and columns we need.

sql
SELECT *
FROM CustomerOrders
CROSS APPLY OPENJSON(OrderDetails)
WITH
(
    orderId INT '$.orderId',
    product NVARCHAR(100) '$.product',
    quantity INT '$.quantity',
    price DECIMAL(10,2) '$.price'
) AS Orders;

In this query:

• We use CROSS APPLY to apply OPENJSON() to each row in the OrderDetails column.
• The WITH clause specifies the structure of the JSON data, mapping JSON properties to SQL Server columns.
• The $ sign represents the root of the JSON object, and we access specific properties using their paths (e.g., $.orderId).

Step 3: Query Result

Running the above query will produce the following result:

orderId	product	quantity	price
101	Laptop	2	1500
102	Headphones	1	200
103	Monitor	3	300

Now, you have successfully parsed the JSON array and fetched the data in tabular format.

---

Additional Use Cases

1. Filtering Data: You can also filter JSON data based on a condition. For example, to get only orders where the quantity is greater than 2, you can write:

   sql
   SELECT *
   FROM CustomerOrders
   CROSS APPLY OPENJSON(OrderDetails)
   WITH
   (
       orderId INT '$.orderId',
       product NVARCHAR(100) '$.product',
       quantity INT '$.quantity',
       price DECIMAL(10,2) '$.price'
   ) AS Orders
   WHERE Orders.quantity > 2;
   

2. Aggregating JSON Data: You can also aggregate data from a JSON array, such as calculating the total price of all orders:

   sql
   SELECT SUM(quantity * price) AS TotalAmount
   FROM CustomerOrders
   CROSS APPLY OPENJSON(OrderDetails)
   WITH
   (
       orderId INT '$.orderId',
       product NVARCHAR(100) '$.product',
       quantity INT '$.quantity',
       price DECIMAL(10,2) '$.price'
   ) AS Orders;
   

---

Conclusion

Handling JSON data in SQL Server is straightforward and powerful with built-in JSON functions. By using OPENJSON(), JSON_VALUE(), and JSON_QUERY(), you can easily parse and extract the data you need from JSON arrays and objects.

This approach is particularly useful for modern applications where storing and manipulating JSON data is a common requirement. It allows you to retain the flexibility of JSON while also leveraging SQL Server's powerful querying capabilities.

Feel free to apply these techniques in your projects and make the most of JSON data in SQL Server!

---