SQL - JSON_QUERY() Function

The built-in SQL JSON_QUERY() function is used to extract data from a JSON object. The function is used to extract a scalar (single) value or an entire JSON object from a JSON string or column in a table.

JSON_QUERY() returns an error if it finds the JSON that is not valid in expression before it finds the path also it returns the value in different modes if it not an object or an array as −

  • JSON_QUERY returns null, in lax mode.

  • JSON_QUERY returns an error, in strict mode.

Syntax

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

JSON_QUERY ( 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. The JSON path can be lax or strict mode for parsing. if it was not mentioned with parsing mode, lax mode is the default.

Example

Let's retrive the first customer from the JSON variable by using the following query −

DECLARE @work VARCHAR(4000);
SET @work = N'{"customers":
   [
      {"Name": "HELLO", "Age": 23, "Department": "Mechnical"},
      {"Name": "WORLD", "Age": 25, "Department": "BBA"}
   ]
}';
SELECT JSON_QUERY(@work, '$.customers[0]') AS Result;

Output

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

+---------------------------------------------------------+
|                                                Result   |
+---------------------------------------------------------+
| {"Name": "HELLO", "Age": 23, "Department": "Mechnical"} |
+---------------------------------------------------------+

Example

In the following example we are going to specify lax path mode to return scalar value and it return NULL because JSON_QUERY() doesn't do scalar values as it only returns objects and arrays −

SELECT JSON_QUERY('{"car": "RX100"}', 'lax $.car') AS 'Result';

Output

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

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

Example

Let's consider another scenario where we are going to use strict path mode and getting the output by using the following query −

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

Output

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

Object or array cannot be found in the specified JSON path.

Example

Let's look into the following example where we are going to extract the column by using the following query−

DECLARE @work VARCHAR(150)
   = '{"Item":"Iphone 13",
      "Price":70000,
      "Color":["Green", "Pink", "White"]}'
SELECT JSON_QUERY(@work,'$.Color') AS Result

Output

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

+----------------------------------------------+
|                                       Result |
+----------------------------------------------+
|                   ["Green", "Pink", "White"] |
+----------------------------------------------+

Example

Let's consider another scenario, where we are going to retrive the scalar value from JSON string and checking the result by using the following query−

DECLARE @work VARCHAR(150)
   = '{"Item":"Iphone 13",
   "Price":70000,
   "Color":["Green", "Pink", "White"]}'
SELECT JSON_QUERY(@work,'$.Item') AS Result

Output

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

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

Example

let's look into the following example, where we are going to retrive the complete JSON string by using the following query −

DECLARE @work VARCHAR(150)
   = '{"Item":"Iphone 13",
   "Price":70000,
   "Color":["Green", "Pink", "White"]}'
SELECT JSON_QUERY(@work,'$') AS Result

Output

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

+----------------------------------------------------------------------------------------------------------------+
|                                                                                               Result           |
+----------------------------------------------------------------------------------------------------------------+
| {"Item":"Iphone 13",              "Price":70000,             "Color":["Green", "Pink", "White"]}               |
+----------------------------------------------------------------------------------------------------------------+
sql-json-functions.htm
Advertisements