Storing JSON in a table using the new SQL Server 2025 json data type

This page has 0 threads | Add post

Blogs

Many people around the world enjoy and benefit from our blogs. If you're one of them - and can afford to - please make a small donation to help keep this and future blogs, exercises, skills assessment tests, tips, tutorials and videos freely available to everyone!

Some other pages relevant to these blogs include:

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

Using JSON in SQL Server, including the new SQL 2025 functions Part three of an eight-part series of blogs
SQL Server 2025 introduces a new JSON data type and some new JSON functions - we thought we'd use this as an excuse to create this masterclass on working with JSON in SQL Server JSON, SQL Server and the example for this series of blogs Loading JSON data from our Star Wars example API Storing JSON in a table using the new SQL Server 2025 json data type (this blog) Extracting data from JSON - part 1 (using JSON_VALUE and JSON_QUERY) Extracting data from JSON - part 2 (using OPENJSON) Converting JSON data into a SQL Server table Joining T-SQL and JSON data Using the new preview modify keyword to change JSON

Using JSON in SQL Server, including the new SQL 2025 functions

Part three of an eight-part series of blogs

SQL Server 2025 introduces a new JSON data type and some new JSON functions - we thought we'd use this as an excuse to create this masterclass on working with JSON in SQL Server

JSON, SQL Server and the example for this series of blogs
Loading JSON data from our Star Wars example API
Storing JSON in a table using the new SQL Server 2025 json data type (this blog)
Extracting data from JSON - part 1 (using JSON_VALUE and JSON_QUERY)
Extracting data from JSON - part 2 (using OPENJSON)
Converting JSON data into a SQL Server table
Joining T-SQL and JSON data
Using the new preview modify keyword to change JSON

Posted by Andy Brown on 27 November 2025

The json data type makes it easy to store the contents of your JSON files in SQL Server:

The new JSON field in table data and table design view.

Here's some SQL (building on our previous example) which would create, populate and show this table:

-- create a variable to hold the JSON text

DECLARE @json_text NVARCHAR(MAX)

-- set this variable to be the contents of the file

SELECT @json_text = j.BulkColumn

FROM OPENROWSET(

BULK 'C:\wiseowl\person.json',

SINGLE_CLOB

) as j;

-- drop the table if it exists

DROP TABLE IF EXISTS tblPerson

-- store this as a table

CREATE TABLE tblPerson(

PersonId int primary key identity,

PersonJson JSON

);

-- add our JSON as the first row

INSERT INTO tblPerson(PersonJson)

VALUES (@json_text)

-- show this has worked

SELECT * FROM tblPerson

The key point of this SQL is that it designates the PersonJson column as having json data type:

This new data type was introduced in SQL Server 2025.

The JSON data type holds a string of text in binary format, making accessing and indexing the data much quicker and more efficient.

Parts of this blog
JSON, SQL Server and the example for this series of blogs Loading JSON data from our Star Wars example API Storing JSON in a table using the new SQL Server 2025 json data type (this blog) Extracting data from JSON - part 1 (using JSON_VALUE and JSON_QUERY) Extracting data from JSON - part 2 (using OPENJSON) Converting JSON data into a SQL Server table Joining T-SQL and JSON data Using the new preview modify keyword to change JSON

Parts of this blog

JSON, SQL Server and the example for this series of blogs
Loading JSON data from our Star Wars example API
Storing JSON in a table using the new SQL Server 2025 json data type (this blog)
Extracting data from JSON - part 1 (using JSON_VALUE and JSON_QUERY)
Extracting data from JSON - part 2 (using OPENJSON)
Converting JSON data into a SQL Server table
Joining T-SQL and JSON data
Using the new preview modify keyword to change JSON

Some other pages relevant to these blogs include:

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

This blog has 0 threads

Add a new post

ALL BLOGS

SQL BLOGS

SQL BLOGS

Head office

London

Manchester