SQL JSON: Taming the Wild World of JSON Data in SQL Server

SQL JSON: Taming the Wild World of JSON Data in SQL Server


JSON, or JavaScript Object Notation, has become the de facto standard for data interchange. Its flexibility and human-readable format make it ideal for representing complex data structures.

However, integrating JSON data into traditional relational databases can be challenging.

SQL Server, the leading relational database management system (RDBMS), has introduced native support for JSON data, allowing developers to seamlessly store, manipulate, and query JSON data alongside their existing relational data. This integration empowers developers to leverage the strengths of both JSON and SQL for their applications.

Storing JSON Data in SQL Server

SQL Server stores JSON data in specialised NVARCHAR columns, which can hold the entire JSON document. This allows for directly storing complex JSON structures, including nested objects, arrays, and primitive data types.

To create a table with a JSON column, you can use the following syntax:

  ProductName NVARCHAR(255),
  ProductInfo NVARCHAR(MAX)

In this example, the ProductInfo column is declared as a JSON column, allowing you to store the product’s detailed information in JSON format.

Querying JSON Data with SQL Server

SQL Server provides a rich set of built-in functions and operators for working with JSON data. These tools enable you to extract specific values, manipulate JSON structures, and perform joins with other relational data.

Extracting Values from JSON

The JSON_VALUE function extracts specific values from JSON documents based on a specified path. For example, to extract the name property from a ProductInfo JSON column:

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

Manipulating JSON Structures

The JSON_MODIFY function allows you to modify JSON structures by adding, removing, or updating specific elements. For instance, to add a new price property to the ProductInfo JSON column:

UPDATE Products
SET ProductInfo = JSON_MODIFY(ProductInfo, '$.price = 100');

Joining with Relational Data

SQL Server supports joining JSON columns with other relational columns using the WITH JSON clause. This enables you to combine JSON data with traditional relational data for comprehensive queries.

For example, to join the ProductName and price from the Products table with the ProductInfo JSON data:

SELECT p.ProductName, j.price
FROM Products p
CROSS APPLY JSON_QUERY(p.ProductInfo, '$.price') AS j;


SQL Server’s native support for JSON data has revolutionised how developers handle and integrate JSON data into their applications.

By combining the strengths of JSON’s flexibility with SQL’s power, developers can build efficient and scalable applications that seamlessly manage complex data structures.


Hi, my name is Stephen Finchett. I have been a software engineer for over 30 years and worked on complex, business critical, multi-user systems for all of my career. For the last 15 years, I have been concentrating on web based solutions using the Microsoft Stack including ASP.Net, C#, TypeScript, SQL Server and running everything at scale within Kubernetes.