• If json_value targets a single scalar JSON value then it returns a corresponding scalar SQL value. You can specify the SQL data type for the returned scalar value. By default it is VARCHAR2(4000).

  If json_value targets the JSON scalar value null it returns SQL NULL of whatever the SQL return type is. (For example, for the default return type VARCHAR2(4000) it does not return 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.

• If json_value targets a JSON array, and you specify a SQL collection type (varray or nested table) as the return type, then json_value returns an instance of that collection type.

  The elements of a targeted JSON array provide the elements of the returned collection-type instance. A scalar JSON array element produces a scalar SQL value in the returned collection instance (see previous). A JSON array element that is an object (see next) or an array is handled recursively.

• If json_value targets a JSON object, and you specify a user-defined SQL object type as the return type, then json_value returns an instance of that object type.

  The field values of a targeted JSON object provide the attribute values of the returned object-type instance. The field names of the targeted JSON object are compared with the SQL names of the SQL object attributes. A scalar field value produces a scalar SQL value in the returned object-type instance (see above). A field value that is an array (see previous) or an object is handled recursively,

  Ultimately it is the names of JSON fields with scalar values that are compared with the names of scalar SQL object attributes. If the names do not match exactly, case-sensitively, then a mismatch error is handled at query compile time.

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 it accepts some optional 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). A scalar value returned from json_value can be of any of these data types: BINARY_DOUBLE, BINARY_FLOAT, BOOLEAN, CHAR, CLOB, DATE, INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH, NCHAR, NCLOB, NVARCHAR2, NUMBER, RAW^Foot 1, SDO_GEOMETRY, TIMESTAMP, TIMESTAMP WITH TIME ZONE, VARCHAR2, and VECTOR.

If the RETURNING type is VECTOR then the behavior depends on the targeted input data as follows:

• If the targeted data is a JSON-scalar vector value then that value is returned as a VECTOR instance.

• If the targeted data is a JSON array with only number elements then the array is converted to a VECTOR instance, which is returned.

• If the targeted data is any other JSON value (that is, a non-vector scalar, an array with any non-number elements, or an object) then an error is raised.

The first argument 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, PASSING, ON ERROR, ON EMPTY, and ON MISMATCH. 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 nonscalar value, such as an array, no error is raised, by default. To ensure that an error is raised, use ERROR ON ERROR.

In a path-expression array step, if only one position is specified then it is matched against the data. Otherwise, there is no match (by default, NULL is returned).

_________________________________________________________