14.2.4.2 Treating an Element More Specifically

Use TREAT to work with a generic JSON element as a more specific PL/SQL JSON type.

The generic type JSON_ELEMENT_T is the "grandparent" in the PL/SQL family of JSON types. A JSON array can hold scalars, objects, or other arrays. In the tree below, if type B is indented inside type A, then you can say B is a more specific kind of A.

JSON_ELEMENT_T
├── JSON_SCALAR_T
│   ├── JSON_NUMBER_T
│   ├── JSON_STRING_T
│   └── JSON_BOOLEAN_T
├── JSON_OBJECT_T
└── JSON_ARRAY_T

Since PL/SQL functions of the same name can't return different types, JSON_ARRAY_T.GET() returns the root JSON_ELEMENT_T type. To explicitly treat the returned array element as one of the more specific types, use:

TREAT(… AS JSON_SPECIFIC_TYPE)

A function like GET_OBJECT below can help save you typing TREAT a lot if you work frequently with arrays of objects.

-- Get object with index p_index in an array of objects
function get_object(
    p_array in json_array_t, 
    p_index in pls_integer)
    return     json_object_t
is
begin
    return treat(p_array.get(p_index) as json_object_t);
end get_object;