SQL Server JSON_VALUE() Function

Summary: in this tutorial, you will learn how to use the SQL Server JSON_VALUE() function to extract scalar value from a JSON string.

Introduction to SQL Server JSON_VALUE() function #

The JSON_VALUE() function extracts a scalar value from a JSON string.

Here’s the syntax of the JSON_VALUE() function:

JSON_VALUE ( expression , path )

In this syntax:

expression: This can be the name of a variable or a column that contains JSON data.
path: This is a JSON path that specifies the element in the expression to extract.

The JSON_VALUE() function returns a single text value with the type NVARCHAR(4000).

If the return value has more than 4000 characters, JSON_VALUE will return null in lax mode or error in strict mode.

To deal with the extracted value whose length is more than 4000 characters, you can use the OPENJSON() function instead.

SQL Server JSON_VALUE() function examples #

Let’s take some examples of using the JSON_VALUE() function.

1) Basic SQL Server JSON_VALUE() function examples #

The following example uses the JSON_VALUE() function to extract the name property from a JSON string:

SELECT JSON_VALUE('{"name": "John", "age": 25}', '$.name') Name;

Output:

Name
-------
John

In this example, we use the JSON path ‘$.name’ to reference the name property of the JSON object.

If the JSON_VALUE cannot find the element that matches a JSON path, it returns NULL.

For example, the following uses the JSON_VALUE to extract email from a JSON object:

SELECT JSON_VALUE('{"name": "john", "age": 25}', '$.email') Email;

Output:

Email
-------
NULL

It returns NULL.

2) Using the JSON_VALUE() function with JSON array #

The following example uses the JSON_VALUE() function to retrieve the first item from an array of numbers:

SELECT JSON_VALUE('[1,2,3]', '$[0]') FirstItem;

Output:

FirstItem
---------
1

3) Using the JSON_VALUE() function variables #

The following example illustrates how to use the JSON_VALUE() function with a variable:

DECLARE @info NVARCHAR(MAX);
SET @info = '{"name": "John", "skills": ["SQL Server", "C#", "ASP.NET"]}';
SELECT 
   JSON_VALUE(@info, '$.name') Name,
   JSON_VALUE(@info, '$.skills[0]') Skill;

Output:

Name   Skill
-----  ----------
John   SQL Server

4) Using the JSON_VALUE() function with table data #

First, create a new table called employees:

CREATE TABLE employees(
   id INT IDENTITY PRIMARY KEY,
   info NVARCHAR(MAX) NOT NULL
);

The info column of the employees table will store JSON data.

Second, insert rows into the employees table:

INSERT INTO employees (info)
VALUES 
    ('{"first_name": "John", "last_name": "Doe", "email": "[email protected]", "skills": ["SQL", "Python", "JavaScript"]}'),
    ('{"first_name": "Alice", "last_name": "Smith", "email": "[email protected]", "skills": ["Java", "C#", "HTML/CSS"]}'),
    ('{"first_name": "Bob", "last_name": "Johnson", "email": "[email protected]", "skills": ["PHP", "Ruby", "Node.js"]}');

Third, extract the data from JSON in the info columns using the JSON_VALUE() and OPENJSON() functions:

SELECT 
    JSON_VALUE(info, '$.first_name') AS first_name,
    JSON_VALUE(info, '$.last_name') AS last_name,
    JSON_VALUE(info, '$.email') AS email,
    skill.value AS skill
FROM employees
CROSS APPLY OPENJSON(info, '$.skills') AS skill;

Output:

first_name | last_name | email             | skill
-------------------------------------------------
John       | Doe       | [email protected]  | SQL
John       | Doe       | [email protected]  | Python
John       | Doe       | [email protected]  | JavaScript
Alice      | Smith     | [email protected] | Java
Alice      | Smith     | [email protected] | C#
Alice      | Smith     | [email protected] | HTML/CSS
Bob        | Johnson   | [email protected]   | PHP
Bob        | Johnson   | [email protected]   | Ruby
Bob        | Johnson   | [email protected]   | Node.js

Summary #

Use the SQL Server JSON_VALUE() function to extract scalar value from a JSON string.

Was this tutorial helpful?