ORACLE json_object_t – error when call get_string() method on object got by chain of methods .get_array() .get()

I am trying to get a value from an array contained in JSON-object by chain of methods of JSON_OBJECT_T type – .get_array(…).get(0).get_string(…)

But getting an exception component 'GET_STRING' must be declared

Here is my code, could someone explain why the chain does not work?

declare
    l_data clob := '{
        "data": {
            "foo": "bar",
            "persons": [{"code":100, "name":"Elon"}, {"code":200, "name":"Musk"}]
        }
    }';
    --
    function bool_to_char(val in boolean) return varchar2 is
    begin
        return case val when true then 'TRUE' else 'FALSE' end;
    end;
begin
    dbms_output.put_line(
        bool_to_char(
            json_object_t
                .parse(l_data)
                .get_object('data')
                .get_array('persons')
                .get(0)
                .is_object
        )
    );  -- TRUE - got object

    -- this code works - got "Elon"
    dbms_output.put_line(
        json_object_t
            .parse(
                json_object_t
                    .parse(l_data)
                    .get_object('data')
                    .get_array('persons')
                    .get(0)
                    .stringify
            )
            .get_string('name')
    );

    -- this code does not work - got exception "PLS-00302: component 'GET_STRING' must be declared"
    /*
    dbms_output.put_line(
        json_object_t
            .parse(l_data)
            .get_object('data')
            .get_array('persons')
            .get(0)
            .get_string('name')
    );
    */
end;

Answer

The problem is that calling .get(0) returns the type JSON_ELEMENT_T, not a JSON_OBJECT_T type. JSON_ELEMENT_T does not have GET_STRING method which is why you are getting the error.

After using get(0), you can TREAT the returned value as a JSON_OBJECT_T so that you can call the GET_STRING method.

See code below

DECLARE
    l_data   CLOB := '{
        "data": {
            "foo": "bar",
            "persons": [{"code":100, "name":"Elon"}, {"code":200, "name":"Musk"}]
        }
    }';

    --
    FUNCTION bool_to_char (val IN BOOLEAN)
        RETURN VARCHAR2
    IS
    BEGIN
        RETURN CASE val WHEN TRUE THEN 'TRUE' ELSE 'FALSE' END;
    END;
BEGIN
    DBMS_OUTPUT.put_line (
        bool_to_char (
            json_object_t.parse (l_data).get_object ('data').get_array ('persons').get (0).is_object)); -- TRUE - got object

    -- this code works - got "Elon"
    DBMS_OUTPUT.put_line (
        json_object_t.parse (
            json_object_t.parse (l_data).get_object ('data').get_array ('persons').get (0).stringify).get_string (
            'name'));

    -- this code does not work - got exception "PLS-00302: component 'GET_STRING' must be declared"
    --Fixed using TREAT(... as JSON_OBJECT_T)
    DBMS_OUTPUT.put_line (
        TREAT (
            json_object_t.parse (l_data).get_object ('data').get_array ('persons').get (0)
                AS json_object_t).get_string ('name'));
END;