JSON_VALUE Function

The JSON_VALUE SQL/JSON function selects a JSON scalar value and returns a SQL scalar value.

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. The data targeted by the SQL expression can be of any of these data types: CLOB, JSON, or VARCHAR2.

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

    The type of the returned value can be specified through the optional RETURNING clause, which can be any of these data types: CLOB, DATE, NUMBER, TIMESTAMP or VARCHAR2.

    The default error-handling behavior is NULL ON ERROR. In particular, if the path expression targets a nonscalar value (such as a JSON array), then no error is raised by default. To ensure an error is raised, use ERROR ON ERROR.

JSON_VALUE returns a SQL NULL for JSON null values. To distinguish a JSON null value from the absence of value (JSON_VALUE returns SQL NULL for both under the default error-handling behavior), use ERROR ON ERROR.

See also:

JSON_VALUE in Oracle TimesTen In-Memory Database SQL Reference

Topics:

Using JSON_VALUE with a Boolean JSON Value

JSON has Boolean values (true and false). When JSON_VALUE evaluates a path expression to a JSON Boolean, it can return a VARCHAR2 value or a NUMBER value. See Table 3-5. By default, JSON_VALUE returns a VARCHAR2 value. If the targeted data is a JSON Boolean value, then by default the function returns a 'true' or 'false' string. See Example 3-12.

Table 3-5 JSON_VALUE Mapping for JSON Boolean Values

JSON Boolean Value VARCHAR2 Value NUMBER(1) value

true

'true'

1

false

'false'

0

You specify the return data type with the RETURNING clause. Example 3-13 illustrates the use of RETURNING BOOLEAN to return a SQL BOOLEAN value (TRUE or FALSE).

By default, RETURNING NUMBER returns an error when the targeted data is a JSON Boolean value. However, if you include the ALLOW BOOLEAN TO NUMBER CONVERSION clause, then JSON_VALUE returns no error. See Table 3-5 for the SQL NUMBER values JSON_VALUE returns in this case. Example 3-14 illustrate the use of both clauses.

Example 3-12 JSON_VALUE Returning a JSON Boolean Value as VARCHAR2

The query in this example returns VARCHAR2 values. This the default behavior. 

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

The query returns this output, given the JSON data in inserted into the j_purchaseorder table in Example 2-2. 

< <NULL> >
< true >
2 rows found.

Example 3-13 JSON_VALUE Returning a JSON Boolean Value as SQL BOOLEAN

The query in this example returns SQL BOOLEAN values. 

SELECT JSON_VALUE(po_document, '$.AllowPartialShipment'
                  RETURNING BOOLEAN)
  FROM j_purchaseorder;

The query returns this output, given the JSON data in inserted into the j_purchaseorder table in Example 2-2. 

< <NULL> >
< TRUE >
2 rows found.

Example 3-14 JSON_VALUE Returning a JSON Boolean Value as SQL NUMBER

The query in this example returns SQL NUMBER values. 

SELECT JSON_VALUE(po_document, '$.AllowPartialShipment'
                  RETURNING NUMBER
                  ALLOW BOOLEAN TO NUMBER CONVERSION)
  FROM j_purchaseorder;

The query returns this output, given the JSON data in inserted into the j_purchaseorder table in Example 2-2. 

< <NULL> >
< 1 >
2 rows found.

JSON_VALUE as JSON_TABLE

The JSON_VALUE function can be viewed as a special case of the JSON_TABLE function. In particular, if you use JSON_VALUE more than once—or use it in a combination with JSON_EXISTS or JSON_QUERY (which you can express using JSON_TABLE)—to access the same data, then a single use of JSON_TABLE has the advantage of parsing the data only once.

Example 3-15 JSON_EXISTS Expressed Using JSON_TABLE

The queries in this example are equivalent. Both SELECT statements have the same effect. 

SELECT JSON_VALUE(po_document, '$.PONumber'
                  RETURNING NUMBER(4)
                  ERROR ON ERROR)
  FROM j_purchaseorder;

SELECT jt.po_number FROM j_purchaseorder,
  JSON_TABLE(po_document, '$' ERROR ON ERROR
    COLUMNS("po_number" NUMBER(4) PATH '$.PONumber')) AS "JT";
Both queries return this output, given the JSON data in inserted into the j_purchaseorder table in Example 2-2.
< 1599 >
< 1600 >
2 rows found.