JSON_VALUE

SQL Server

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”.

Oracle

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'.