How to parse JSON in Oracle 11g and 12c

Oracle 11g:

SQL> set serveroutput on;
SQL> DECLARE
  2      s varchar2(32767) := '{ "a": 1, "b": ["hello", "world"]}';
  3  BEGIN
  4      apex_json.parse(s);
  5      sys.dbms_output.put_line('a is '||apex_json.get_varchar2(p_path => 'a'));
  6      sys.dbms_output.put_line('b1 is '||apex_json.get_varchar2(p_path => 'b[1]'));
  7      sys.dbms_output.put_line('b2 is '||apex_json.get_varchar2(p_path => 'b[2]'));
  8  END;
  9  /
a is 1
b1 is hello
b2 is world

PL/SQL procedure successfully completed.

SQL>
SQL> declare
  2      sample_json   varchar2 (32767)
  3          := '{
  4      "glossary": {
  5          "title": "example glossary",
  6          "GlossDiv": {
  7              "title": "S",
  8              "GlossList": {
  9                  "GlossEntry": {
 10                      "ID": "SGML",
 11                      "SortAs": "SGML",
 12                      "GlossTerm": "Standard Generalized Markup Language",
 13                      "Acronym": "SGML",
 14                      "Abbrev": "ISO 8879:1986",
 15                      "GlossDef": {
 16                          "para": "A meta-markup language, used to create markup languages such as DocBook.",
 17                          "GlossSeeAlso": ["GML", "XML"]
 18                      },
 19                      "GlossSee": "markup"
 20                  }
 21              }
 22          }
 23      }
 24  }';
 25  begin
 26      apex_json.parse (sample_json);
 27      dbms_output.put_line (apex_json.get_varchar2 ('glossary.GlossDiv.title'));
 28      dbms_output.put_line (apex_json.get_varchar2 ('glossary.GlossDiv.GlossList.GlossEntry.GlossTerm'));
 29      dbms_output.put_line (apex_json.get_varchar2 ('glossary.GlossDiv.GlossList.GlossEntry.GlossDef.GlossSeeAlso[%d]', 2));
 30  end;
 31  /
S
Standard Generalized Markup Language
XML

PL/SQL procedure successfully completed.

SQL>
SQL> declare
  2      sample_json   varchar2 (32767)
  3          := '{
  4     "parameters":    {
  5        "active_users": "%",
  6        "all_completions": "Y",
  7        "content_object_id": "47630968",
  8        "email": "%",
  9        "excl_status_list": "N,I,BK",
 10        "expired_date_from": "%",
 11        "folder_id": "%",
 12        "get_cert_by_oe_last_update": "N",
 13        "is_completed": "%",
 14        "modified_date_from": "09-DEC-2019",
 15        "modified_date_to": "20-DEC-2019",
 16        "object_id": "%",
 17        "org_id": "%",
 18        "sDateDisplay": "E",
 19        "sTruncateDateOnly": "N"
 20     },
 21     "reportStatus":    {
 22        "runId": 322053,
 23        "status": "Running",
 24        "statusInfo": null,
 25        "startDate": "2019-12-11T00:07:02",
 26        "endDate": null,
 27        "timeTaken": "00:00:01",
 28        "totalResults": 0
 29     }
 30  }';
 31  begin
 32      apex_json.parse (sample_json);
 33      dbms_output.put_line (apex_json.get_varchar2 ('parameters.modified_date_from'));
 34      dbms_output.put_line (apex_json.get_varchar2 ('parameters.modified_date_to'));
 35
 36      dbms_output.put_line (apex_json.get_varchar2 ('reportStatus.runId'));
 37      dbms_output.put_line (apex_json.get_varchar2 ('reportStatus.status'));
 38      dbms_output.put_line (apex_json.get_varchar2 ('reportStatus.statusInfo'));
 39      dbms_output.put_line (apex_json.get_varchar2 ('reportStatus.startDate'));
 40      dbms_output.put_line (apex_json.get_varchar2 ('reportStatus.endDate'));
 41      dbms_output.put_line (apex_json.get_varchar2 ('reportStatus.timeTaken'));
 42      dbms_output.put_line (apex_json.get_varchar2 ('reportStatus.totalResults'));
 43  end;
 44  /
09-DEC-2019
20-DEC-2019
322053
Running
2019-12-11T00:07:02
00:00:01
0

PL/SQL procedure successfully completed.

SQL>


Oracle 12c:

CREATE TABLE json_documents (
  id    RAW(16) NOT NULL,
  data  CLOB,
  CONSTRAINT json_documents_pk PRIMARY KEY (id),
  CONSTRAINT json_documents_json_chk CHECK (data IS JSON)
);

INSERT INTO json_documents (id, data)
VALUES (SYS_GUID(),
        '{
          "FirstName"      : "John",
          "LastName"       : "Doe",
          "Job"            : "Clerk",
          "Address"        : {
                              "Street"   : "99 My Street",
                              "City"     : "My City",
                              "Country"  : "UK",
                              "Postcode" : "A12 34B"
                             },
          "ContactDetails" : {
                              "Email"    : "john.doe@example.com",
                              "Phone"    : "44 123 123456",
                              "Twitter"  : "@johndoe"
                             },
          "DateOfBirth"    : "01-JAN-1980",
          "Active"         : true
         }');


SELECT a.data FROM json_documents a;

json value respponse


SELECT a.data.FirstName,
       a.data.LastName,
       a.data.Address.Postcode AS Postcode,
       a.data.ContactDetails.Email AS Email
FROM   json_documents a;


FIRSTNAME       LASTNAME        POSTCODE   EMAIL
--------------- --------------- ---------- -------------------------
Jayne           Doe             A12 34B    jayne.doe@example.com
John            Doe             A12 34B    john.doe@example.com

2 rows selected.