SQL Reference - JSON

JSON_QUERY

JSON_QUERY is a SQL Server function that extracts an object or an array from a JSON string. It returns a valid JSON formatted string or the error if the JSON string is not appropriately formatted. It is commonly used to extract and represent complex data structures, stored in SQL Server columns, in JSON format.

JSON_QUERY(jsonData, expression)

jsondata: This is the field or expression that returns the JSON text to be parsed. The JSON_QUERY function extracts an object or an array from a JSON string.
expression: This is a JSON path expression that specifies the object or the array to extract from the JSON text. The expression begins with a $. In case the expression returns a scalar value, JSON_QUERY returns null.

Example

DECLARE @json NVARCHAR(4000) = N'
{
      "info": {
           "type": "Fruit",
           "color": "Red",
           "isTrue": true,
           "nothing": null
                }
}';
SELECT JSON_QUERY(@json, '$.info')

Output

{
  "type": "Fruit",
  "color": "Red",
  "isTrue": true,
  "nothing": null
}

Explanation

In the JSON_QUERY example above, SQL query is declaring a JSON object and querying it using the $.info path. This returns the JSON part of the object that is the value of the ‘info’ key.

JSON_QUERY( json_document, json_path RETURNING data_type ERROR ON ERROR )

json_document: This parameter indicates the JSON document that will be queried. It refers to either a JSON or VARCHAR2 value that can be parsed as a JSON object.
json_path: This is the path used to identify the desired data element or elements within the JSON document. It must conform to the SQL/JSON path language specification.
returning data_type: This parameter controls the desired Oracle SQL data type for the return value. It facilitates the conversion of complex JSON types to simpler SQL types.
error on error: This optional clause determines the action to be taken when an error occurs during the evaluation of the JSON path expression. It throws an error if the JSON path isn’t found or if it can’t be evaluated.

Example

SELECT JSON_QUERY('{"employee":{"name":"John","age":30,"city":"New York"}}', '$.employee.name') AS output
FROM dual;

Output

"John"

Explanation

In the above example, JSON_QUERY is used to extract the value of “name” from the “employee” object in the JSON string. The result is the string “John”.