JSON, SQL Server and the example for this series of blogs

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 one 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 (this blog) Loading JSON data from our Star Wars example API Storing JSON in a table using the new SQL Server 2025 json data type 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 one 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 (this blog)
Loading JSON data from our Star Wars example API
Storing JSON in a table using the new SQL Server 2025 json data type
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

JSON (as you surely know if you're reading this blog) is a file format for holding structured data. Here's the start of a typical JSON file, holding Star Wars characters (more on these later):

The results section of this JSON file contains a dictionary of characters in the Star Wars filims.

This blog will show you how to store data in the json field type:

The FilmJson column holds JSON text listing all of the Star Wars films.

Creating fields like this:

The FilmJson field has data type json.

This blog will also show you how to extract information from this into SQL Server format:

This is the same data in a more familiar format.

We'll also look at writing SQL like this to link T-SQL and JSON data together:

SELECT

f.FilmId,

j.FilmTitle

FROM

tblFilm AS f

CROSS APPLY OPENJSON(f.FilmJson, '$.results')

WITH (

FilmTitle nvarchar(200) '$.title'

) AS j;

Let's get started with something which isn't strictly JSON at all: how to load our sample Star Wars data into a SQL variable.

Parts of this blog
JSON, SQL Server and the example for this series of blogs (this blog) Loading JSON data from our Star Wars example API Storing JSON in a table using the new SQL Server 2025 json data type 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