SQL Reference - JSON

JSON_VALUE

JSON_VALUE is an SQL function that extracts and returns a scalar value from a JSON string. The scalar returned can be of types: int, float, bit, NVARCHAR(n), and NVARCHAR(max). The function can handle complex nested JSON paths as long as the final value is a scalar. This function returns NULL if the JSON path address leads to multiple elements or fails to match elements in the provided JSON.

JSON_VALUE( expression , path )

expression: This parameter refers to the JSON text, which is an expression. It can also be a column that has JSON text.
path: This parameter signifies the path to the value in the JSON text. The path string, which uses JavaScript-like syntax, identifies the value that needs to be extracted.

Example

DECLARE @json NVARCHAR(4000) = N'{
    "info": {
        "type": "Fruit",
        "name": "Apple",
        "color": "Green"
    }
}';

SELECT JSON_VALUE(@json, '$.info.color') as fruit_color;

Output

fruit_color
Green

Explanation

In the provided SQL query, the JSON_VALUE function is used to extract a value from a JSON string. The value extracted is specified by the JSON path $.info.color. This path navigates to the “info” object inside the JSON string, then retrieves the value of “color”, which is “Green”. The result is returned in the column named “fruit_color”.

JSON_VALUE(json_document, json_path [RETURNING data_type [FORMAT JSON]])

json_document: This parameter refers to the JSON document which we wish to extract data from. The document must exist valid JSON format. This is an input parameter.
json_path: This is the path within the JSON document where the target data is stored. Asher its name suggests, it follows the path to reach the desired data value. The path always starts from the root of the JSON document.
returning data_type: This optional parameter is used to specify the expected datatype of the value to be returned by the `JSON_VALUE` function. If not provided, the default data type, VARCHAR2(4000), is used.
format json: This optional clause specifies that the result is to be returned as a JSON formatted string. This clause is relevant only when the data_type parameter is also provided.

Example

SELECT JSON_VALUE('{"team": "Warriors"}', '$.team')
FROM dual;

Output

'Warriors'

Explanation

The JSON_VALUE function in Oracle extracts a scalar value from a JSON document. In this example, it extracts the value of team from the JSON document {"team": "Warriors"}. The result is 'Warriors'.