Parsing JSON in Oracle 11g

Question

I need to parse a Json string including structs and arrays of structs. I understand that the question was raised, but two hours of googling did not give a satisfactory result due to the existing restrictions:

• We cannot install large frameworks (except for compiling ONE separate package). This deprives us of APEX.
• We cannot use temporary tables.

But there are the usual plsql features:

• Any collections
• Data types declared at the package level.
• Actually, within one package, any code that does not contradict the restrictions.

So. As an example of json:

vSrc :=
'{
  "NAME": " Arnold  Schwarzenegger",
  "AGE": "35",
  "PARTNER":{
    "NAME":"Sandra Bullock",
    "AGE":"33"
  }
  "CHILDREN": [
    {
      "NAME": "Jhon",
      "AGE": "4"
    },
    {
      "NAME": "Kate",
      "AGE": "8"
    }
  ]
}'

We want to be able to receive either a value or an array of json strings by address. For example:

v_result := jsonAPI.getJson(source => vSrc, address => 'PARTNER.NAME');

Returns the value 'Sandra Bullock', and the call

v_result := jsonAPI.getJson(source => vSrc, address => 'CHILDREN');

Returns a collection, displaying it with the following code:

dbms_output.put_line('First Kid:');
dbms_output.put_line(v_result(1));
dbms_output.put_line('Second Kid:');
dbms_output.put_line(v_result(2));

We'll see

First Kid:
{"NAME":"Jhon","AGE":"4"}
Second Kid:
{"NAME":"Kate","AGE":"8"}

Thus, we can use the elements of this collection as a parameter to call the same api. Is there a way to parse JSON like this in pure PLSQL? Some well-known open source libraries, for example, or something like that?

question from:https://stackoverflow.com/questions/65923780/parsing-json-in-oracle-11g

Answer 1

Waitting for answers