Fear No SQL
In two prior articles I used pl/sql and java to build functions that would return the keys of a JSON document as a collection which could be queried as a table. 23c includes a new MLE (multilingual engine) functionality providing a mechanism to define a stored procedure using JavaScript. This seemed like an ideal solution…
In my previous article I provided a few PL/SQL options for returning a collection of keys from a JSON document. They are simple to use and work well enough for most JSON sources I’ve encountered. SQL> select * from json_keys(json(‘{“a”:”test 1″, “b”: “test 2”, “c” : “test 3”}’)); COLUMN_VALUE ——————————————————————————– a b c Iudith Mentzel…
I found this functional surprise while responding to questions about my previous article. When you invoke the GET_KEYS method of JSON_OBJECT_T, if the collection is empty, 19c will return a NULL value; 21c will return a JSON_KEY_LIST collection of 0 elements. 19c SQL> DECLARE 2 v_version VARCHAR2(20); 3 v_compatible VARCHAR2(20); 4 v_keylist json_key_list; 5 BEGIN…
When presented with a JSON document you may need to process all of its contents. You’ll need to read each value and if there are any objects or arrays within the document you’ll need to read through the contents of each of them as well. Fortunately, the JSON structure is, intentionally, simple in its construction.…
I’ve found myself needing to search LDAP data more often and each time I do it’s been sort of obnoxious to have to rewrite variations of mostly similar DBMS_LDAP routines. Frequently I then need to use that data in a SQL query of some sort to combine it with other data found in one or…