Microsoft SQL Server

In Microsoft SQL Server, you can work with JSON data directly using built-in functions to parse, query, and extract data from JSON objects stored in database columns. This feature allows you to handle semi-structured data in a flexible way, making it easier to integrate with modern applications that use JSON. You can use SQL Server’s JSON functions to convert relational data into JSON format or to query JSON data within columns. For beginners, learning how to use JSON in SQL Server enables you to work with a common data format without needing a separate NoSQL database.

Below is a detailed explanation of how to use JSON in SELECT queries, with examples and best practices.

1. Basic Syntax for Selecting JSON Data

You can use the JSON_VALUE() function to extract a scalar value from a JSON object stored in a column.

SELECT JSON_VALUE(column_name, '$.key')
FROM table_name;

Example:

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

This query extracts the ProductName field from the ProductDetails JSON object stored in the Products table.

2. Querying Nested JSON Data

You can query nested JSON data by specifying the path to the nested element in the JSON_VALUE() function.

SELECT JSON_VALUE(ProductDetails, '$.Specifications.Color') AS ProductColor
FROM Products;

This query extracts the Color field from a nested Specifications object in the ProductDetails JSON column.

3. Returning Data as JSON

SQL Server also allows you to return query results as JSON using the FOR JSON clause. You can convert a standard SQL result set into JSON format.

SELECT ProductName, Price
FROM Products
FOR JSON AUTO;

This query returns the result set in JSON format, automatically converting the columns to key-value pairs.

4. Modifying JSON Data

You can update specific fields within a JSON object using the JSON_MODIFY() function. This allows you to change or insert values into a JSON object stored in a table column.

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

This query updates the Price field in the ProductDetails JSON column for a specific product.

5. Aggregating JSON Data

You can aggregate relational data into JSON format using the FOR JSON clause with grouping or joining tables.

SELECT Category, 
       (SELECT ProductName, Price 
        FROM Products 
        WHERE Products.CategoryID = Categories.CategoryID 
        FOR JSON PATH) AS Products
FROM Categories;

This query retrieves categories and includes a nested JSON array of products for each category.

6. Best Practices for Working with JSON in SQL Server

1. Use JSON_VALUE() for Extracting Scalar Values – Use JSON_VALUE() to extract specific scalar values from JSON columns. It's a quick and efficient way to retrieve data from JSON without parsing the entire object.

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

2. Use JSON_QUERY() for Extracting JSON Fragments – Use JSON_QUERY() when you want to extract a JSON object or array from a JSON column rather than a scalar value.

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

3. Validate JSON Data with ISJSON() – Before querying or updating JSON data, use the ISJSON() function to ensure the data is valid JSON. This can prevent errors when working with malformed JSON.

   SELECT ProductName
   FROM Products
   WHERE ISJSON(ProductDetails) = 1;

4. Use FOR JSON PATH for Flexible JSON Output – When converting data to JSON, use FOR JSON PATH for more control over the structure of the output. It allows you to create complex nested JSON objects.

   SELECT ProductName, Price 
   FROM Products
   FOR JSON PATH;

5. Optimize JSON Storage – If your table contains a large amount of JSON data, store it in NVARCHAR(MAX) columns and index any scalar values you frequently query using JSON_VALUE() to improve performance.

By learning to work with JSON in SQL Server, you can easily handle semi-structured data and integrate with applications that use JSON, all while leveraging the power of SQL for querying and managing your data.

To gain complete access, login with gmail or outlook, no need of signup, click here

SELECT client_id, first_name, last_name,
complete_address,
JSON_VALUE(complete_address, '$.address1') AS address1,
JSON_VALUE(complete_address, '$.County') AS county,
JSON_VALUE(complete_address, '$."PO BOX"') AS po_box
FROM org_client
WHERE complete_address IS NOT NULL;

SELECT client_id, first_name, last_name,
JSON_VALUE(complete_address, '$.address1') AS address1,
JSON_VALUE(complete_address, '$.County') AS county
FROM org_client
WHERE JSON_VALUE(complete_address, '$."PO BOX"') = '432';

SELECT client_id, first_name, last_name,
complete_address,
JSON_VALUE(complete_address, '$.address1') AS address1,
JSON_VALUE(complete_address, '$.County') AS county
FROM org_client
WHERE JSON_VALUE(complete_address, '$."PO BOX"') = '432';

SELECT client_id,
first_name AS "info.name",
last_name AS "info.surname",
birth_year
FROM org_client
WHERE client_id = 1
FOR JSON PATH;

SELECT JSON DATA

The following SQL queries demonstrate how to extract key-value pairs from a specified JSON column. Additionally, these queries illustrate the utilization of JSON key-value pairs within the WHERE clause.

RAW DATA WITH JSON DATA TYPE

MySQL QUERY

SELECT client_id, first_name, last_name,complete_address ,complete_address -> '$.address1' ,complete_address ->> '$.County' ,JSON_UNQUOTE(complete_address -> '$."PO BOX"') FROM org_client where complete_address IS NOT NULL;

POSTGRESQL QUERY

SELECT client_id, first_name, last_name,complete_address -> 'address1' AS address1 ,complete_address ->> 'County' AS county FROM org_client where complete_address ->> 'PO BOX' = '432';

MS SQL SERVER AND ORACLE QUERY

SELECT client_id, first_name, last_name, complete_address ,JSON_VALUE(complete_address, '$.address1') AS address1 ,JSON_VALUE(complete_address, '$.County') AS county FROM org_client where JSON_VALUE(complete_address, '$."PO BOX"') = '432';

QUERY DATA MAPPING

QUERY OUT PUT