JSON_VALUE() Examples in SQL Server (T-SQL)

Posted on by Ian

When using JSON with SQL Server, you can use the JSON_VALUE() function to return a scalar value from a JSON string.

To use this function, you provide two arguments; the JSON expression, and the property to extract.

Syntax

The syntax goes like this:

JSON_VALUE ( expression , path )

Where expression is the JSON string expression, and path is the property you want to extract from that expression.

The path argument can include an optional path mode component. This optional path mode can be a value of either lax or strict. This value, if any, comes before the dollar sign.

Example 1 – Basic Usage

Here’s an example to demonstrate basic usage of the JSON_VALUE() function.

SELECT JSON_VALUE('{"Name": "Bruce"}', '$.Name') AS 'Result';

Result:

+----------+
| Result   |
|----------|
| Bruce    |
+----------+

In this example:

  • The {"Name": "Bruce"} argument is the JSON expression (a small one, but still a valid JSON expression). JSON expressions consist of a key/value pair. In this case, Name is the key, Bruce is its value.
  • The $.Name argument is the path. This path references the value of the Name key of the JSON expression. So we can extract the value by referencing the name of the pair.

Example 2 – Arrays

To extract a value from an array, reference its index within square brackets, followed by the relevant key. Here’s an example:

/* 
CREATE THE ARRAY (and put into a variable called @data)
*/
DECLARE @data NVARCHAR(4000)
SET @data=N'{
    "Cities": [
        {
            "Name": "Kabul",
            "CountryCode": "AFG",
            "District": "Kabol",
            "Population": 1780000
        },
        {
            "Name": "Qandahar",
            "CountryCode": "AFG",
            "District": "Qandahar",
            "Population": 237500
        }
    ]
}'

/* 
QUERY THE ARRAY
*/
SELECT 
  JSON_VALUE(@data,'$.Cities[0].Name') AS 'Name',
  JSON_VALUE(@data,'$.Cities[0].CountryCode') AS 'Country Code',
  JSON_VALUE(@data,'$.Cities[0].District') AS 'District',
  JSON_VALUE(@data,'$.Cities[0].Population') AS 'Population'

UNION ALL

SELECT 
  JSON_VALUE(@data,'$.Cities[1].Name') AS 'Name',
  JSON_VALUE(@data,'$.Cities[1].CountryCode') AS 'Country Code',
  JSON_VALUE(@data,'$.Cities[1].District') AS 'District',
  JSON_VALUE(@data,'$.Cities[1].Population') AS 'Population';

Result:

+----------+----------------+------------+--------------+
| Name     | Country Code   | District   | Population   |
|----------+----------------+------------+--------------|
| Kabul    | AFG            | Kabol      | 1780000      |
| Qandahar | AFG            | Qandahar   | 237500       |
+----------+----------------+------------+--------------+

So in this example, we create a JSON array and put it into a variable called @data. We then run a query, using @data as the first argument of the JSON_VALUE() function (this is because @data contains the JSON expression).

Arrays use zero-based numbering, so to extract the first item we need to use Cities[0], the second one Cities[1], and so on.

Example 3 – A Database Example

If we were to put the data from the previous example into a database, we could rewrite the query as follows:

SELECT 
  JSON_VALUE(Document,'$.Cities[0].Name') AS 'Name',
  JSON_VALUE(Document,'$.Cities[0].CountryCode') AS 'Country Code',
  JSON_VALUE(Document,'$.Cities[0].District') AS 'District',
  JSON_VALUE(Document,'$.Cities[0].Population') AS 'Population'
FROM Json_Documents

UNION ALL

SELECT 
  JSON_VALUE(Document,'$.Cities[1].Name') AS 'Name',
  JSON_VALUE(Document,'$.Cities[1].CountryCode') AS 'Country Code',
  JSON_VALUE(Document,'$.Cities[1].District') AS 'District',
  JSON_VALUE(Document,'$.Cities[1].Population') AS 'Population'
FROM Json_Documents

Result:

+----------+----------------+------------+--------------+
| Name     | Country Code   | District   | Population   |
|----------+----------------+------------+--------------|
| Kabul    | AFG            | Kabol      | 1780000      |
| Qandahar | AFG            | Qandahar   | 237500       |
+----------+----------------+------------+--------------+

This assumes that the JSON document is stored in a column called Document, which is in a table called Json_Documents.

Example 4 – Path Mode

As mentioned, you also have the option of specifying the path mode. This can be either lax or strict.

The value of the path mode determines what happens when the path expression contains an error.  Specifically:

  • In lax mode, the function returns empty values if the path expression contains an error. For example, if you request the value $.name, and the JSON text doesn’t contain a name key, the function returns null, but does not raise an error.

  • In strict mode, the function raises an error if the path expression contains an error.

The default value is lax.

Here’s an example to demonstrate the difference between these two modes.

Error in lax mode

Here’s what happens when the path expression contains an error while in lax mode.

SELECT JSON_VALUE('{"Name": "Bruce"}', 'lax $.Hobbies') AS 'Result';

Result:

+----------+
| Result   |
|----------|
| NULL     |
+----------+

In this example we’re trying to reference Hobbies, but that key doesn’t exist in the JSON document. In this case we get a null value (because we’re using lax mode).

Error in strict mode

Here’s what happens when we run the same code in strict mode.

SELECT JSON_VALUE('{"Name": "Bruce"}', 'strict $.Hobbies') AS 'Result';

Result:

Msg 13608, Level 16, State 1, Line 1
Property cannot be found on the specified JSON path.

As expected, strict mode results in an error message being displayed.

Example 5 – Returning Objects and Arrays

The JSON_VALUE() function doesn’t return objects and arrays. If you want to return an object or an array, use the JSON_QUERY() function instead. Here’s an example where I use both functions within a query.

DECLARE @data NVARCHAR(4000)
SET @data=N'{  
    "Suspect": {    
       "Name": "Homer Simpson",  
       "Address": {    
         "City": "Mae Sai",  
         "Province": "Chiang Rai",  
         "Country": "Thailand"  
       },  
       "Hobbies": ["Eating", "Sleeping", "Base Jumping"]  
    }
 }'
 SELECT 
   JSON_VALUE(@data,'$.Suspect.Name') AS 'Name',
   JSON_VALUE(@data,'$.Suspect.Address.Country') AS 'Country',
   JSON_QUERY(@data,'$.Suspect.Hobbies') AS 'Hobbies',
   JSON_VALUE(@data,'$.Suspect.Hobbies[2]') AS 'Last Hobby';

Result:

+---------------+-----------+----------------------------------------+--------------+
| Name          | Country   | Hobbies                                | Last Hobby   |
|---------------+-----------+----------------------------------------+--------------|
| Homer Simpson | Thailand  | ["Eating", "Sleeping", "Base Jumping"] | Base Jumping |
+---------------+-----------+----------------------------------------+--------------+

In this case, I use JSON_VALUE() to return various scalar values, and JSON_QUERY() to return an array.

So if you need to return an object or an array (including the whole JSON document), see JSON_QUERY() Examples in SQL Server.