SQL - JSON_VALUE() Function

The SQL JSON_VALUE() function accepts a JSON string and returns a scalar value. It decodes a JSON string and extracts scalar values along a predetermined path.The path is provided in a certain format, which is shown below.

  • '$' − It refers the entire JSON object.

  • '$.property1' − It refers to the property1 in JSON object.

  • '$[2]' − It refers the second elemen in JSON object.

  • '$.property1.property2[4].property3' − It refers the nested property in JSON object.

Syntax

Following is the syntax of the SQL JSON_VALUE() function −

JSON_VALUE ( expression , path )

Parameters

  • expression − It is typically the name of tzhe column or a variable that contains JSON text.

  • path − The JSON path that specifies the property to extract. If the format of the path is not valid, JSON_VALUE returns an error.

Example

Let's retrive the key value from the JSON string by using the following query −

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

Output

When we execute the above query, the output is obtained as follows −

+------------------------------------+
|                              Result|
+------------------------------------+
|                    TUzTORIALSPOINT  |
+------------------------------------+

Example

In the following example we are going to specify lax explicitly to the path that does not contain argument that match JSON string and checking the result by using the following query −

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

Output

When we execute the above query, the output is obtained as follows −

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

Example

Let's consider the following example, where we are going to retrive the second value of the JSON array from JSON string by using the following query −

DECLARE @iphone VARCHAR(MAX)
   = '{
   "Item":"Iphone13",
   "Color":["Green", "Pink", "White"]
   }'
SELECT JSON_VALUE(@iphone,'$.Color[1]') AS JSON_Color

Output

When we execute the above query, the output is obtained as follows −

+------------------------------------+
|                         JSON_Color |
+------------------------------------+
|                              Pink  |
+------------------------------------+

Example

Let's consider another scenario where we are going to retrive the first record from the JSON array on mentioning the index position by using the following query−

DECLARE @work VARCHAR(4000);
SET @work = N'{
   "Customers": [
      {
         "Name": "XYZ",
         "Age": "23",
         "Address": "101, Sector 5, Gurugram, India",
         "Salary": 105000
      },
      {
         "Name": "ABC",
         "Age": "34",
         "Address": "17, Park Avenue, Mumbai, India",
         "Salary": 780000
      }
   ]
}';
SELECT JSON_VALUE(@work, '$.Customers[0].Name') AS 'Name', 
   JSON_VALUE(@work, '$.Customers[0].Age') AS 'Age', 
   JSON_VALUE(@work, '$.Customers[0].Address') AS 'Address', 
   JSON_VALUE(@work, '$.Customers[0].Salary') AS 'Salary';

Output

On executing the above query, the output is displayed as follows −

+-------+-----+-------------------------------+-----------+      
| Name  |Age  |             Address           |  Salary   |
+-------+-----+-------------------------------+-----------+
| XYZ   |23   |101, Sector 5, Gurugram, India | 105000    |
+-------+-----+-------------------------------+-----------+

Example

Let's consider another example, where we are going to retrive the value from the nested JSON by using the following query−

DECLARE @work VARCHAR(4000);
SET @work = N'{
   "Customers": [
   
      {
         "Name": "Revi",
         "Age": "25",
         "Department": {"course":"HTML", "Field":"IT" 
         "Salary": 100000
      },
      
   ]
}';
SELECT JSON_VALUE(@work, '$.Customers[0].Name') AS 'Name', 
   JSON_VALUE(@work, '$.Customers[0].Department.Field') AS 'Field';

Output

On executing the above query, the output is displayed as follows −

+-------+-----+       
| Name  |Field|             
+-------+-----+   
| Revi  |IT   |  
+-------+-----+

Example

let's look into the following example, where we are going to use the use the strict path and trying to reteive the output by using the following query −

SELECT JSON_VALUE('{"car": "RX100"}', 'strict$.car') AS 'Output';

Output

On executing the above query, the output is displayed as follows −

+----------------------------------------------+
|                                       Result |
+----------------------------------------------+
|                                        RX100 |
+----------------------------------------------+
sql-json-functions.htm
Advertisements