Microsoft SQL Server

Chapter 5 - DDL (Data Definition Language)

JSON Data Type

In Microsoft SQL Server, JSON data is stored as text in VARCHAR or NVARCHAR columns. Although SQL Server doesn't have a specific JSON data type, it provides built-in functions to parse and query JSON data. This allows you to integrate and manipulate JSON data effectively within SQL Server tables.

Key Concepts

1. Storing JSON Data

JSON data is stored as text in columns with data types like VARCHAR or NVARCHAR.

2. Inserting JSON Data

You can insert JSON data into these text columns just like any other string data.

3. Querying JSON Data

SQL Server provides functions like JSON_VALUE(), JSON_QUERY(), and OPENJSON() to query and manipulate JSON data.

4. Updating JSON Data

You can update JSON data by replacing or modifying the text stored in JSON columns.

5. Validating JSON Data

Although SQL Server does not validate JSON automatically, you can use built-in functions to check whether JSON is valid.

Code Samples

1. Creating a Table with JSON Column

Define a table with a column to store JSON data.

CREATE TABLE Products(
    ProductID INT PRIMARY KEY IDENTITY(1,1), -- Auto-incremented primary key
    ProductName VARCHAR(100) NOT NULL, -- Product name, cannot be NULL
    ProductDetails NVARCHAR(MAX) -- Column to store JSON data
);

2. Inserting JSON Data

Insert JSON data into the ProductDetails column.

INSERT INTO Products (ProductName, ProductDetails)
VALUES (
    'Sample Product',
    '{"Category":"Electronics","Price":299.99,"InStock":true}'
);

3. Querying JSON Data

Extract a specific value from JSON using JSON_VALUE().

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

Query a JSON array using JSON_QUERY().

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

4. Updating JSON Data

Update the Price value in the ProductDetails JSON column.

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

5. Validating JSON Data

Check if a string is valid JSON using ISJSON().

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

Combined Code Sample

Here is all the code combined for easier reference:

-- Creating a Table with JSON Column

CREATE TABLE Products(
    ProductID INT PRIMARY KEY IDENTITY(1,1),
    ProductName VARCHAR(100) NOT NULL,
    ProductDetails NVARCHAR(MAX)
);

-- Inserting JSON Data

INSERT INTO Products (ProductName, ProductDetails)
VALUES (
    'Sample Product',
    '{"Category":"Electronics","Price":299.99,"InStock":true}'
);

-- Querying JSON Data

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

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

-- Updating JSON Data

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

-- Validating JSON Data

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

These examples should help you get started with using JSON data in SQL Server. Feel free to modify and expand these examples according to your specific needs.

Tansy SQL Course - JSON Data Type - Video Thumbnail

Previous Next

Comments(0 comments)

Comments Not Found

Microsoft SQL Server

JSON Data Type

Key Concepts

1. Storing JSON Data

2. Inserting JSON Data

3. Querying JSON Data

4. Updating JSON Data

5. Validating JSON Data

Code Samples

1. Creating a Table with JSON Column

2. Inserting JSON Data

3. Querying JSON Data

4. Updating JSON Data

5. Validating JSON Data

Combined Code Sample

Most visited