Joining T-SQL and JSON data

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 seven 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 Joining T-SQL and JSON data (this blog) Using the new preview modify keyword to change JSON

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

Part seven 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

Posted by Andy Brown on 27 November 2025

This is a natural extension of everything that this blog has talked about so far. The fantastic thing about the way that SQL Server deals with JSON is that it doesn't differentiate in queries between whether data comes in table or JSON format, so you can join the two types of data together. Like this, in fact:

-- create a variable to hold the JSON text

DECLARE @json_text NVARCHAR(MAX)

-- set this variable to be the contents of the films table

SELECT @json_text = f.BulkColumn

FROM OPENROWSET(

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

SINGLE_CLOB

) as f;

-- create a CTE to hold the list of films from the JSON file

WITH json_films AS (

SELECT

-- get the title and director from the JSON

f.Title,

f.Director

FROM

-- shred the "results" part of the JSON into rows

OPENJSON(@json_text, '$.results')

WITH(

Title nvarchar(200) '$.title',

Director nvarchar(200) '$.director'

) AS f

)

SELECT

-- get the title and director from the JSON

f.Title,

f.Director,

-- show fields from the Wise Owl movies

-- database table of films

sql_films.OscarWins AS Oscars,

sql_films.RunTimeMinutes as FilmLength,

sql_films.RunTimeMinutes

FROM

-- some of the films in the movies database have extra info

--(eg Star Wars: 'Episode VI - Return of the Jedi')

json_films as f

LEFT JOIN Film as sql_films ON

LOWER(sql_films.Title) LIKE '%' || LOWER(f.Title) || '%'

Here's what this gives for the Wise Owl movies database:

The left columns come from the JSON file, the right from a linked SQL Server table.

So you no longer have to process your JSON and SQL Server data separately - you can join them together and work with them in one place!

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 Joining T-SQL and JSON data (this blog) Using the new preview modify keyword to change JSON

Parts of this blog

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