SQL/JSON function json_value selects a scalar value from JSON data and returns it as a SQL value.

You can also use json_value to create function-based B-tree indexes for use with JSON data — see Indexes for JSON Data.

Function json_value has two required arguments and accepts optional returning and error clauses.

The first argument to json_value is a SQL expression that returns an instance of a scalar SQL data type (that is, not an object or collection data type). It can be of data type VARCHAR2, BLOB, or CLOB. It can be a table or view column value, a PL/SQL variable, or a bind variable with proper casting. The result of evaluating the SQL expression is used as the context item for evaluating the path expression.

The second argument to json_value is a SQL/JSON path expression followed by optional clauses RETURNING, ON ERROR, and ON EMPTY. The path expression must target a single scalar value, or else an error occurs.

The default error-handling behavior is NULL ON ERROR, which means that no value is returned if an error occurs — an error is not raised. In particular, if the path expression targets a non-scalar value, such as an array, no error is raised, by default. To ensure that an error is raised, use ERROR ON ERROR.


Each field name in a given JSON object is not necessarily unique; the same field name may be repeated. The streaming evaluation that Oracle Database employs always uses only one of the object members that have a given field name; any other members with the same field name are ignored. It is unspecified which of multiple such members is used.

See also Unique Versus Duplicate Fields in JSON Objects.

See Also:

Oracle Database SQL Language Reference for information about json_value


15.1 Using SQL/JSON Function JSON_VALUE With a Boolean JSON Value

JSON has the Boolean values true and false. Oracle SQL has no Boolean data type. When SQL/JSON function json_value evaluates a SQL/JSON path expression and the result is true or false, there are two ways to handle the result in SQL: as a string or as a number.

By default, the returned data type is a SQL string (VARCHAR2), meaning that the result is the string 'true' or 'false'. You can alternatively return the result as a SQL number, in which case the JSON value true is returned as the number 1, and false is returned as 0.

Example 15-1 illustrates this. The first query returns the string 'true'; the second query returns the number 1.

You can also use json_value in PL/SQL code. In that case, BOOLEAN is a valid PL/SQL return type for built-in PL/SQL function json_value. Example 15-2 illustrates this.

Example 15-1 JSON_VALUE: Two Ways to Return a JSON Boolean Value in SQL

SELECT json_value(po_document, '$.AllowPartialShipment')
 FROM j_purchaseorder;

SELECT json_value(po_document, '$.AllowPartialShipment' RETURNING NUMBER)
  FROM j_purchaseorder;

Example 15-2 Returning a BOOLEAN PL/SQL Value From JSON_VALUE

Unlike Oracle SQL, PL/SQL has a BOOLEAN data type, which you can use for the return value of PL/SQL built-in function json_value.

PL/SQL also has exception handling. This example uses clause ERROR ON ERROR, to raise an error (which can be handled by user code) in case of error.

  jsonData CLOB;
  SELECT po_document INTO jsonData FROM j_purchaseorder WHERE rownum = 1;
     b := json_value(jsonData, '$.AllowPartialShipment'
                    RETURNING BOOLEAN
                    ERROR ON ERROR);

15.2 SQL/JSON Function JSON_VALUE Applied to a null JSON Value

SQL/JSON function json_value applied to JSON value null returns SQL NULL, not the SQL string 'null'. This means, in particular, that you cannot use json_value to distinguish the JSON value null from the absence of a value; SQL NULL indicates both cases.


SQL/JSON function json_value can be viewed as a special case of function json_table.

Example 15-3 illustrates the equivalence: the two SELECT statements have the same effect.

In addition to perhaps helping you understand json_value better, this equivalence is important practically, because it means that you can use either function to get the same effect.

In particular, if you use json_value more than once, or you use it in combination with json_exists or json_query (which can also be expressed using json_table), to access the same data, then a single invocation of json_table presents the advantage that the data is parsed only once.

Because of this, the optimizer often automatically rewrites multiple invocations of json_exists, json_value and json_query (any combination) to fewer invocations of json_table.

Example 15-3 JSON_VALUE Expressed Using JSON_TABLE

SELECT json_value(column, json_path RETURNING data_type error_hander ON ERROR)
  FROM table;

SELECT jt.column_alias
  FROM table,
       json_table(column, '$' error_handler ON ERROR
         COLUMNS ("COLUMN_ALIAS" data_type PATH json_path)) AS "JT";