JSON has the Boolean values true and false. When SQL/JSON function json_value evaluates a SQL/JSON path expression and the result is JSON true or false, it can be returned to PL/SQL as a BOOLEAN value, or it can be returned to SQL as the VARCHAR2 value 'true' or 'false'.

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

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

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.

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