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 20-1 illustrates this — the query returns 'true'.

With a RETURNING clause you can specify the return data type. Example 20-2 illustrates the use of RETURNING BOOLEAN to return a BOOLEAN value (true or false) in SQL — the query returns true. Example 20-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 20-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.

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

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

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;
/ 

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