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

JSON has Boolean values true and false. When SQL/JSON function json_value evaluates a path expression to JSON true or false, it can return a BOOLEAN or a VARCHAR2 value ('true' or 'false'), or a NUMBER value (1 for true, 0 for false).

By default, json_value returns a VARCHAR2 (string) value. If the targeted data is a JSON Boolean value then by default the returned value is the string 'true' or 'false'. Example 22-1 illustrates this — the query returns 'true'.

With a RETURNING clause you can specify the return data type. Example 22-2 illustrates the use of RETURNING BOOLEAN to return a BOOLEAN value (true or false) in SQL — the query returns true. Example 22-3 illustrates the same thing in PL/SQL, and it shows the use of clause ERROR ON ERROR.

By default, RETURNING NUMBER raises an error when the targeted data is a JSON Boolean value. However, if you include the clause ALLOW BOOLEAN TO NUMBER CONVERSION then no error is raised; in that case, 1 is returned for a true JSON value, and 0 is returned for a false value. Example 22-4 illustrates this — the query returns 1.

SQL/JSON function json_table generalizes other SQL/JSON query functions, including json_value. When you use it to project a JSON Boolean value, json_value is used implicitly, and the resulting SQL value is returned as a VARCHAR2 value, by default. By default, the data type of the projection column is therefore VARCHAR2.

But just as for json_value, you can project a JSON Boolean value as a BOOLEAN value. And you can project it as a NUMBER value, by specifying NUMBER data type for the column and including the clause ALLOW BOOLEAN TO NUMBER CONVERSION.

Example 22-1 JSON_VALUE: Returning a JSON Boolean Value as VARCHAR2

Returning a VARCHAR2 is the default behavior for function json_value.

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

Example 22-2 JSON_VALUE: Returning a JSON Boolean Value to SQL as BOOLEAN

This example returns a SQL BOOLEAN value for Boolean JSON data. (BOOLEAN data type is available in Oracle SQL starting with Release 23ai.)

SELECT json_value(data, '$.AllowPartialShipment'
                  RETURNING BOOLEAN)
  FROM j_purchaseorder;

Example 22-3 JSON_VALUE: Returning a JSON Boolean Value to PL/SQL as BOOLEAN

This example uses clause ERROR ON ERROR, to raise an error in case of error. (User exception-handling code can then handle the error.)

DECLARE
  b     BOOLEAN;
  jdata CLOB;
BEGIN
  SELECT data INTO jdata FROM j_purchaseorder
    WHERE rownum = 1;
  b := json_value(jdata, '$.AllowPartialShipment'
                  RETURNING BOOLEAN
                  ERROR ON ERROR);
END;
/ 

Example 22-4 JSON_VALUE: Returning a JSON Boolean Value to SQL as NUMBER

This examples uses clause ALLOW BOOLEAN TO NUMBER CONVERSION to return the SQL NUMBER value 1, meaning true. Without that clause, RETURNING NUMBER raises an error for Boolean JSON data.

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