Managing 100 columns in a table is difficult in real-time projects; in these cases, we must add JSON to the SQL table columns and then manage them.

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

    Store JSON in NVARCHAR(MAX) or VARCHAR(MAX) columns.
    Insert JSON using regular INSERT statements.
    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.
    Modify JSON using JSON_MODIFY to update or add properties.
    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 ColumnSELECT
    ProductID,
    ProductName,
    JSON_VALUE(ProductDetails, '$.Brand') AS Brand,
    JSON_VALUE(ProductDetails, '$.Specifications.Processor') AS Processor
FROM
    ProductsData;

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!

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.