Extracting data from JSON - part 2 (using OPENJSON)

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 five 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 Extracting data from JSON - part 1 (using JSON_VALUE and JSON_QUERY) Extracting data from JSON - part 2 (using OPENJSON) (this blog) 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 five 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
Extracting data from JSON - part 1 (using JSON_VALUE and JSON_QUERY)
Extracting data from JSON - part 2 (using OPENJSON) (this blog)
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

In this blog

Basic use of OPENJSON
Burrowing down into JSON

If you want to extract arrays of data from with JSON it's probably easiest to use OPENJSON to shred data. Here's a quick reminder of what we have so far - a table containing our JSON data of Star Wars characters:

The JSON for the list of characters is in a column called PersonJson.

With this JSON:

The beginning of the JSON listing the characters.

Basic use of OPENJSON

For each row of the tblPerson table (there only is one, as it happens) we want to get a list of all of characters' names and heights from the results section of the JSON. Here's how to do this:

SELECT

f.PersonId,

-- show the list of names and heights for this row

shredded_rows.CharacterName,

shredded_rows.CharacterHeight

FROM

tblPerson AS f

-- for each person, get the JSON containing their results

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

-- and from this extract their name and height

WITH (

CharacterName nvarchar(200) '$.name',

CharacterHeight int '$.height'

) AS shredded_rows;

Here's what this returns for our example:

Had there been a second set of JSON in the table, the characters from this would be listed too.

The way this works is using something very similar to a correlated subquery. For each row in the tblPerson table we open up the JSON stored in the PersonJson column, get the results node, split the contents of the array within this into rows and pick out the name and height properties, giving each an alias. We then show these values in the main query.

Burrowing down into JSON

Each character in the results node has a subnode called films giving the URLs of the films they've appeared in:

The films for Luke Skywalker, for example. Notice that there's no name for each film object - just a value giving the URL.

You could extend our SQL to show the film URLs for each character:

I wouldn't normally include the columns shown in the red boxes above - I've included these just for reference.

Deep breath! Here's the SQL to show these results:

SELECT

f.PersonId,

-- show the list of names and heights for this row

shredded_rows.CharacterName,

shredded_rows.CharacterHeight,

-- show the list of films as JSON for reference

shredded_rows.FilmList,

-- show all of the URL fields (would normally just

-- show FilmUrls.Value)

FilmUrls.*

FROM

tblPerson AS f

-- for each person, get the JSON containing their results

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

-- and from this extract their name, height and ...

WITH (

CharacterName nvarchar(200) '$.name',

CharacterHeight int '$.height',

-- ... the list of films they were in

FilmList nvarchar(max) '$.films' AS JSON

) AS shredded_rows

-- take the list of films and split it into the

-- individual rows

CROSS APPLY OPENJSON(shredded_rows.FilmList) AS FilmUrls

Here's what this does:

For each row in the tblPerson table (there only is one) ...
... for each character in the results section of the JSON stored in the PersonJSON column, pick out the character's name, height and the list of films, then ...
... for each film in this character's films, split it into the individual rows

The trickiest part of this SQL was getting the data types for this line right:

-- ... the list of films they were in

FilmList nvarchar(max) '$.films' AS JSON

You seem to have to get the data as text and then convert it into 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 Extracting data from JSON - part 1 (using JSON_VALUE and JSON_QUERY) Extracting data from JSON - part 2 (using OPENJSON) (this blog) 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
Extracting data from JSON - part 1 (using JSON_VALUE and JSON_QUERY)
Extracting data from JSON - part 2 (using OPENJSON) (this blog)
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

Basic use of OPENJSON

Burrowing down into JSON

Head office

London

Manchester