JSON Data Type Explained for SQL Server Developers

Problem

Is there a native way to handle JSON in SQL Server?

Solution

In this article, we will discuss ways to handle JSON data in SQL Server 2025 or later. We will talk about the advantages of these new features, how to create indexes, and some related JSON functions.

Requirements

You will need to have SSMS and SQL Server 2025 installed:

Getting started

We will show how to create a table that can store JSON data in SQL Server.

In versions prior to SQL Server 2025, we used the nvarchar(max) or the varchar(max) to store JSON data like this:

CREATE TABLE ProductsJSONVarchar
(
Id INT IDENTITY(1,1) PRIMARY KEY,
JsonData NVARCHAR(MAX)
);

To insert JSON data, we inserted rows like this:

INSERT INTO ProductsJSONVarchar (JsonData)
VALUES
(N'{
    "product": "Laptop",
    "brand": "Dell",
    "price": 1000,
    "stock": 20,
    "features": {
        "ram": "32GB",
        "cpu": "i7",
        "ssd": "512GB"
    }
}');

Now, in SQL Server 2025 and later we can use the JSON data type like this:

CREATE TABLE ProductsJSON
(
Id INT IDENTITY(1,1) PRIMARY KEY,
JsonData JSON
);

The insertion of data is the same.

Advantages of JSON data type

With this new data type, the JSON is stored as native JSON, the data was stored as plain text in the past, and it was necessary to validate the data with the ISJSON function.

When using VARCHAR or NVARCHAR you could insert invalid JSON. In this example, we will insert an invalid JSON text using the nvarchar(x) data type:

INSERT INTO ProductsJSONVarchar (JsonData)
VALUES
(N'{
    product": "Laptop",
    "brand": "Dell",
    "price": 1000,
    "stock": 20,
    "features": {
        "ram": "32GB",
        "cpu": "i7",
        "ssd": "512GB"
    }
}');

The JSON is missing the double quotes at the beginning of the word product (should be “product”, not product”). However, the data type is nvarchar(max), which is text, so it does not validate the JSON data, and invalid JSON text is inserted.

On the other hand, if we try to insert into the table with the JSON data type, the JSON format is validated:

INSERT INTO ProductsJSON (JsonData)
VALUES
(N'{
    product": "Laptop",
    "brand": "Dell",
    "price": 1000,
    "stock": 20,
    "features": {
        "ram": "32GB",
        "cpu": "i7",
        "ssd": "512GB"
    }
}');

Error message:

Msg 13609, Level 16, State 9, Line 28
JSON text is not properly formatted. Unexpected character 'p' is found at position 7.

As you can see, the JSON data is natively validated before insertion.

In versions prior to SQL Server 2025, you could use the ISJSON function to validate the data:

SELECT ISJSON
(N'{
    product": "Laptop",
    "brand": "Dell",
    "price": 1000,
    "stock": 20,
    "features": {
        "ram": "32GB",
        "cpu": "i7",
        "ssd": "512GB"
    }
}') validatejson

The ISJSON function returns a 1 or 0. However, it does not tell you where the error is.

1 if the JSON text is valid
0 if it is invalid

The new JSON data type tells you where the error is.

Better performance with the JSON data type

Now that it uses the native JSON Binary data instead of plain text, queries are faster. We will add some data to test.

First, we will insert some data into both tables. This query will insert 1000000 rows.

;WITH N AS
(
    SELECT TOP (100000)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM sys.all_objects a
    CROSS JOIN sys.all_objects b
)
INSERT INTO dbo.ProductsJSON (JsonData)
SELECT JSON_OBJECT(
           'productId': n,
           'name': CONCAT('Product ', n),
           'category': CASE n % 5
                         WHEN 0 THEN 'Laptop'
                         WHEN 1 THEN 'Phone'
                         WHEN 2 THEN 'Tablet'
                         WHEN 3 THEN 'Monitor'
                         ELSE 'Printer'
                       END,
           'price': CAST((n % 2000) + 100 AS DECIMAL(10,2)),
           'stock': n % 250,
           'brand': CASE n % 4
                      WHEN 0 THEN 'Contoso'
                      WHEN 1 THEN 'Fabrikam'
                      WHEN 2 THEN 'Northwind'
                      ELSE 'Adventure'
                    END,
           'active': CASE WHEN n % 2 = 0 THEN 1 ELSE 0 END
       )
FROM N;
GO
 
INSERT INTO dbo.ProductsJSONVarchar (JsonData)
SELECT CAST(JsonData AS NVARCHAR(MAX))
FROM dbo.ProductsJSON;
GO

Let’s compare the queries with the JSON data type.

First, run this code to turn on set statistics.

SET STATISTICS TIME ON;
SET STATISTICS IO ON;
GO

Now, we will run a query in the table with the JSON data type:

SELECT COUNT(*) AS TotalLaptopJSON
FROM dbo.ProductsJSON
WHERE JSON_VALUE(JsonData, '$.category') = 'Laptop';
GO

The results are the following:

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 5 ms.
(1 row affected)
Table 'ProductsJSON'. Scan count 1, logical reads 3229, physical reads 0, page server reads 0, read-ahead reads 0, 
page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, 
lob page server read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 266 ms,  elapsed time = 323 ms.

Now, we will run the same query using the nvarchar(max) table:

SELECT COUNT(*) AS TotalLaptopVarchar
FROM dbo.ProductsJSONVarchar
WHERE JSON_VALUE(JsonData, '$.category') = 'Laptop';
GO

The results are the following:

SQL Server parse and compile time: 
   CPU time = 391 ms, elapsed time = 470 ms.
(1 row affected)
Table 'ProductsJSONVarchar'. Scan count 1, logical reads 3407, physical reads 0, page server reads 0, read-ahead reads 0, 
page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, 
lob page server read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 328 ms,  elapsed time = 344 ms.

As you can see, the CPU time is better when you use the JSON data type. The parse and compile CPU and elapsed time are better, and the CPU execution and elapsed time are better as well. So, performance is better with the JSON data type.

JSON indexes

It is also important to apply JSON indexes. This will improve the performance even more.

Let’s add an index to the table with JSON data type:

CREATE JSON INDEX IX_ProductsJSON_JsonData
ON dbo.ProductsJSON (JsonData)
FOR ('$.category', '$.price', '$.brand');
GO

Now, we will run the query again and check the results.

SELECT COUNT(*) AS TotalLaptopJSON
FROM dbo.ProductsJSON
WHERE JSON_VALUE(JsonData, '$.category') = 'Laptop';
GO

If we check the results, we will see a better CPU time, and elapsed time for execution.

SQL Server parse and compile time: 
   CPU time = 425 ms, elapsed time = 425 ms.
(1 row affected)
Table 'ProductsJSON'. Scan count 1, logical reads 3229, physical reads 1, page server reads 0, read-ahead reads 1411, 
page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, 
lob page server read-ahead reads 0.
SQL Server Execution Times:
   CPU time = 219 ms,  elapsed time = 231 ms.

JSON Functions JSON_ARRAYAGG, JSON_ARRAY, JSON_CONTAINS

The JSON_ARRAYAGG functions builds a JSON array from an aggregation of data columns in SQL Server. The following example shows how to create a JSON array using the function.

SELECT JSON_ARRAYAGG(c1) myarray
FROM (VALUES ('Laptop'), ('Tablet'), ('Cellphone')) AS t(c1);

The result is as follows:

["Laptop","Tablet","Cellphone"]

Also, we have the JSON_ARRAY. This function builds a simple JSON array.

SELECT JSON_ARRAY('Laptop', 1, 'Tablet', 2,'Cellphone', 3) myarray;

The result is as follows:

["Laptop",1,"Tablet",2,"Cellphone",3]

Finally, we have JSON_CONTAINS that verifies if a value exists in a JSON document. The following example verifies if the values exist in the JSON document.

DECLARE @j AS JSON = '{"Laptop": 1, "Tablet": 2, "Cellphone": 3}';
 
SELECT JSON_CONTAINS(@j, 2, '$.Tablet') AS is_value_found;

Conclusion

The SQL Server JSON data type is a nice new feature that simplifies the use of JSON in SQL Server. Now, the JSON text is validated automatically and performance is better.

Next Steps

To learn more about SQL Server 2025, check our links related:

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs.

MSSQLTips Awards: Author of the Year Contender – 2015-2018, 2022, 2023 | Champion (100+ tips) – 2018

JSON Data Type and Indexes in SQL Server 2025