Extracting data from JSON - part 1 (using JSON_VALUE and JSON

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 four 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) (this blog) 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 four 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) (this blog)
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

In this blog

Getting single objects using JSON_VALUE
Getting at arrays using JSON_QUERY
Using array subscripts

This page shows various ways in which you can get at data in this JSON file:

As always when working with JSON (or any other) data you need to be able to understand the structure of the data in order to make any sense of it in your SQL.

Recall that this JSON is stored in the PersonJson column in our tblPerson table:

We'll get the JSON from the PersonJson column in this table.

Getting single objects using JSON_VALUE

You can use the JSON_VALUE function to extract information from different nodes of a JSON file. For example:

-- show a couple of JSON columns

SELECT

p.PersonId,

JSON_VALUE(p.PersonJson, '$.count') AS NumberCharacters,

JSON_VALUE(p.PersonJson, '$.results') AS Results

FROM

tblPerson AS p

Here's what this would return:

The count object is correct, but the results one shows as null.

The reason the results are null is that the results object has nothing stored at its level, although it contains an array of characters at the next level down:

The results object is null, even though it contains an array of characters at a lower level of hierarchy.

Getting at arrays using JSON_QUERY

As of SQL Server 2025 you can now get the array stored in a node using JSON_QUERY ... WITH ARRAY WRAPPER:

SELECT

p.PersonId,

JSON_VALUE(p.PersonJson, '$.count') AS NumberCharacters,

-- show the array stored in the "results" object

JSON_QUERY(p.PersonJson, '$.results' WITH ARRAY WRAPPER) AS Results

FROM

tblPerson AS p

Here's what this would show:

We now have the list of characters.

Using array subscripts

You can use the * key to get all of the items in an array, as this example shows:

-- 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;

SELECT

-- show the array stored in the "results" object

JSON_QUERY(@json_text, '$.results[*].name') AS Results

This would give the names of all of the characters returned from the Star Wars API:

The list of all of the people returned.

If you only want certain people, here are some ideas for what you could put instead of the * in the SQL above:

What to put (path)	What it would return
'$.results[0].name'	The first character's name
'$.results[1 to 3].name'	The second, third and fourth characters' names

However, the most useful way to get at data in JSON is probably still using the OPENJSON function, the subject of the next part of this blog.

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

Getting single objects using JSON_VALUE

Getting at arrays using JSON_QUERY

Using array subscripts

Head office

London

Manchester