In Oracle PL/SQL, How do I check if a JSON object contains an element of a specific key-CodePudding

I have a procedure as shown below. It accepts a JSON object as an input. One of the elements in this object type is an array with the key samplekey. In some cases, the JSON object p_object does not contain the samplekey array. In those instances, the procedure encounters a runtime exception. What is the best way to handle this scenario in PL/SQL? Should I have to check for the existence of an element with key samplekey in the object p_object first? If yes, how do I do this? Is there a better way to handle such scenarios?

procedure(p_object in json_object_t)
as

lja json_array_t;

begin

lja   := p_object.get_array('samplekey');

end;

CodePudding user response：

You can use the HAS function of JSON_OBJECT_T. This will return TRUE or FALSE if the JSON has the key you search for. Below is an example of how to use it.

DECLARE
    PROCEDURE test_json (p_object IN json_object_t)
    IS
    BEGIN
        IF p_object.has ('samplekey')
        THEN
            DBMS_OUTPUT.put_line ('JSON has the key "samplekey"');
        ELSE
            DBMS_OUTPUT.put_line ('JSON does NOT have the key "samplekey"');
        END IF;
    END;
BEGIN
    test_json (json_object_t ('{}'));
    test_json (json_object_t ('{"samplekey":123}'));
    test_json (json_object_t ('{"otherkey":"test"}'));
END;
/

--Output
JSON does NOT have the key "samplekey"
JSON has the key "samplekey"
JSON does NOT have the key "samplekey"