Converting JSON data into a SQL Server table

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 six 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) Converting JSON data into a SQL Server table (this blog) 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 six 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)
Converting JSON data into a SQL Server table (this blog)
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

The basic FOR JSON command
Using FOR JSON PATH to generate subsections

For this part of the blog we're going to briefly abandon our Star Wars characters. Instead, consider this query:

-- list prolific directors in name order

SELECT

d.DirectorID,

d.FirstName,

d.FamilyName,

d.FullName,

d.DateOfBirth,

d.DateOfDeath,

d.Gender

FROM

Director as d

WHERE (

SELECT COUNT(*)

FROM Film as f

WHERE f.DirectorID = d.DirectorID

) > 20

ORDER BY

d.FamilyName

When you run this it lists out the most prolific directors from the Wise Owl movies database, which as it happens give these results:

Sadly, if unsurprisingly, all of the most prolific directors are male.

The basic FOR JSON command

To turn this into JSON you just need to give your columns suitable names and add FOR JSON AUTO at the bottom:

-- list prolific directors in name order

SELECT

d.DirectorID,

d.FirstName as 'name.first',

d.FamilyName as 'name.last',

d.FullName as 'name.full',

d.DateOfBirth AS 'date.birth',

d.DateOfDeath 'date.death',

d.Gender

FROM

Director as d

WHERE (

SELECT COUNT(*)

FROM Film as f

WHERE f.DirectorID = d.DirectorID

) > 20

ORDER BY

d.FamilyName

FOR JSON AUTO

By giving the columns names SQL should automatically assign the columns into JSON objects as shown:

Object	Fields
Root	DirectorId, Gender
name	first, last, full
date	birth, death

Here's what this gives:

The JSON isn't easy to read in this format!

And here's the JSON copied into a separate file and indented:

Sadly, the JSON groups I mentioned above haven't been created.

Using FOR JSON PATH to generate subsections

To get the properly constructed JSON data, in your query change the last line to this:

ORDER BY

d.FamilyName

-- generate JSON section

FOR JSON PATH

This will then use the dots in your column aliases to generate sections:

Much better!

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) Converting JSON data into a SQL Server table (this blog) 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)
Converting JSON data into a SQL Server table (this blog)
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

The basic FOR JSON command

Using FOR JSON PATH to generate subsections

Head office

London

Manchester