How to create a PL/SQL function to iterate JSON Keys for SQL

Oracle supports a wide variety of JSON parsing, querying, and construction functionality; but, does not provide a means in SQL of listing the keys of JSON object (at least not as of 23ai). While this is not a common need, it is a feature I have wanted a few times. Fortunately, it is an easy limitation to circumvent with a little PL/SQL.
The JSON_OBJECT_T type includes a get_keys method which returns a VARRAY.

Unfortunately, that type isn’t usable directly in SQL.

SQL> select json_object_t('{"a":"test 1", "b": "test 2", "c" : "test 3"}') from dual;

Error starting at line : 1 in command -
select json_object_t('{"a":"test 1", "b": "test 2", "c" : "test 3"}') from dual
Error report -
ORA-40573: invalid use of PL/SQL JSON object type

But we can create a simple function wrapping the object and method call returning the collection type.

CREATE OR REPLACE FUNCTION json_keys(p_json IN JSON)
    RETURN json_key_list
AS
BEGIN
    RETURN json_object_t(p_json).get_keys;
END;

And then this function can be used to query a json object. Note the function, as written, only returns the top-level keys. It does not recurse into any sub-objects. For example, the “f” and “g” keys within the “e” object are not listed in the second query.

SQL> select * from json_keys(json('{"a":"test 1", "b": "test 2", "c" : "test 3"}'));

   COLUMN_VALUE
_______________
a
b
c

SQL> SELECT *
  2    FROM json_keys(
  3             json(
  4                 '{"a":1,"b": "hello", "c":true, "d":null,
  5                    "e": {"f":"subvalue","g":2}
  6                  }'
  7                 )
  8         );

   COLUMN_VALUE
_______________
a
b
c
d
e

In one of my previous articles I present a framework to recursively search through sub-keys within objects and arrays. That article doesn’t include a function to return just the keys, but following the same design presented there you could return the keys and sub-keys in some kind of a collection.

Another option instead of a json_key_list is to return a nested table collection. For example…

CREATE OR REPLACE FUNCTION json_keys(p_json IN json)
    RETURN vctab
AS
    v_keys      vctab := vctab();
    v_keylist   json_key_list;
BEGIN
    v_keylist := json_object_t(p_json).get_keys;

    v_keys.EXTEND(v_keylist.COUNT);

    FOR i IN 1 .. v_keylist.COUNT
    LOOP
        v_keys(i) := v_keylist(i);
    END LOOP;

    RETURN v_keys;
END;


select * from json_keys(json('{"a":1, "b": "hello", "c":true, "d":null, "e": {"f":"subvalue","g":2}}'));

With vctab being declared as follows…

CREATE OR REPLACE TYPE vctab AS TABLE OF VARCHAR2(4000);

As you can see, internally to the function, the entire json_key_list varray is still generated. The function in this form essentially just doubles the memory requirements and adds extra work, so I don’t recommend this.

However, if you have many keys, you may prefer to use a pipelined function to return the keys as the client needs them instead of all at once. Again, you still have to generate the varray on the server. So, you’re not saving server-side memory over the original function; but you don’t need to construct the full table collection, so this is still an improvement over the previous non-pipelined version. Furthermore, if the client doesn’t read all of the returned keys, you might be able to reduce total network traffic and client memory consumption. The function returning the collection in pipelined form might look like this:

CREATE OR REPLACE FUNCTION json_keys(p_json IN JSON)
    RETURN vctab
    PIPELINED
AS
    v_keylist   json_key_list;
BEGIN
    v_keylist := json_object_t(p_json).get_keys;

    FOR i IN 1 .. v_keylist.COUNT
    LOOP
        PIPE ROW (v_keylist(i));
    END LOOP;

    RETURN;
END;

Ideally, a key iterator will be made available as a standard function with SQL syntax, until then I hope you find these functions useful. Thank you for reading. Questions and comments, as always, are welcome.