Using JSON_VALUE To Instantiate a User-Defined Object-Type or Collection-Type Instance

20.2 Using JSON_VALUE To Instantiate a User-Defined Object-Type or Collection-Type Instance

You can use SQL/JSON function json_value to instantiate an instance of a user-defined SQL object type or collection type. You do this by targeting a JSON object or array in the path expression and specifying the object or collection type, respectively, in the RETURNING clause.

The elements of a targeted JSON array provide the elements of a returned collection-type instance. The JSON array elements must correspond, one-to-one, with the collection-type elements. If they do not then a mismatch error occurs. A JSON array element that is an object (see next) or an array is handled recursively.

The fields of a targeted JSON object provide the attribute values of a returned object-type instance. The JSON fields must correspond, one-to-one, with the object-type attributes. If they do not then a mismatch error occurs.

The field names of the targeted JSON object are compared with the SQL names of the object attributes. A field value that is an array or an object is handled recursively, so that 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 (case insensitively, by default), then a mismatch error occurs.

If all names match then the corresponding data types are checked for compatibility. If there is any type incompatibility then a mismatch error occurs. Table 18-2 specifies the compatible scalar data types — any other type combinations are incompatible, which entails a mismatch error.

A mismatch error occurs at query compile time if any of the following are true. By default, mismatch errors are ignored, but you can change this error handling by including one or more ON MISMATCH clauses in your invocation of json_value.

The fields of a targeted JSON object, or the elements of a targeted JSON array, do not correspond in number and kind to the attributes of the specified object-type instance, or to the elements of the specified collection-type instance, respectively.
The fields of a targeted JSON object do not have the same names as the attributes of a specified object-type instance. By default this matching is case-insensitive.
The JSON and Oracle SQL scalar data types of a JSON value and its corresponding object attribute value or collection element value are not compatible, according to Table 18-2.

You can use json_value to return an object-type or collection-type instance in PL/SQL, as well as SQL. However, the behavior of clauses NULL ON MISMATCH and NULL ON EMPTY is slightly different when returning a record-type or an index table-type instance, because values of these types cannot be atomically NULL. See their documentation for details.

Example 20-5 Instantiate a User-Defined Object Instance From JSON Data with JSON_VALUE

This example defines SQL object types shipping_t and addr_t. Object type shipping_t has attributes name and address, which have types VARCHAR2(30) and addr_t, respectively.

Object type addr_t has attributes street and city.

The example uses json_value to select the JSON object that is the value of field ShippingInstructions and return an instance of SQL object type shipping_t. Names of the object-type attributes are matched against JSON object field names case-insensitively, so that, for example, attribute address (which is the same as ADDRESS) of SQL object-type shipping_t matches JSON field address.

(The query output is shown pretty-printed here, for clarity.)

CREATE TYPE addr_t AS OBJECT
  (street VARCHAR2(100),
   city   VARCHAR2(30));

-- Create after type addr_t, because that's referred to here.
--
CREATE TYPE shipping_t AS OBJECT
  (name    VARCHAR2(30),
   address addr_t); 

-- Query data to return shipping_t instances:
SELECT json_value(po_document, '$.ShippingInstructions'
                  RETURNING shipping_t)
  FROM j_purchaseorder;

JSON_VALUE(PO_DOCUMENT,'$.SHIPPINGINSTRUCTIONS'RETURNING
--------------------------------------------------------
SHIPPING_T('Alexis Bull',
           ADDR_T('200 Sporting Green',
                  'South San Francisco'))
SHIPPING_T('Sarah Bell',
           ADDR_T('200 Sporting Green',
                  'South San Francisco'))

Example 20-6 Instantiate a Collection Type Instance From JSON Data with JSON_VALUE

This example defines SQL collection type items_t and SQL object types part_t and item_t. An instance of collection type items_t is a varray of item_t instances. Attribute part of object-type item_t is itself of SQL object-type part_t.

It then uses json_value to select the JSON

(The query output is shown pretty-printed here, for clarity.)

CREATE TYPE part_t AS OBJECT
  (description VARCHAR2(30),
   unitprice   NUMBER);
 
CREATE TYPE item_t AS OBJECT
  (itemnumber NUMBER,
   part       part_t);
  
CREATE TYPE items_t AS VARRAY(10) OF item_t;

-- Query data to return items_t collections of item_t objects
SELECT json_value(po_document, '$.LineItems' RETURNING items_t)
  FROM j_purchaseorder;

JSON_VALUE(PO_DOCUMENT,'$.LINEITEMS'RETURNINGITEMS_TUSIN
--------------------------------------------------------
ITEMS_T(ITEM_T(1, PART_T('One Magic Christmas', 19.95)),
        ITEM_T(2, PART_T('Lethal Weapon', 19.95)))
ITEMS_T(ITEM_T(1, PART_T('Making the Grade', 20)),
        ITEM_T(2, PART_T('Nixon', 19.95)),
        ITEM_T(3, PART_T(NULL, 19.95)))