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 )Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Output:

Name
-------
JohnCode language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Output:

Email
-------
NULLCode language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Output:

FirstItem
---------
1Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

Output:

Name   Skill
-----  ----------
John   SQL ServerCode language: SQL (Structured Query Language) (sql)

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
);Code language: SQL (Structured Query Language) (sql)

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"]}');Code language: SQL (Structured Query Language) (sql)

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;Code language: SQL (Structured Query Language) (sql)

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.jsCode language: SQL (Structured Query Language) (sql)

Summary

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