Разбор JSON в ORACLE DB

 
 
CREATE TABLE json_documents (
  id    RAW(#cc66cc;">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.FirstName,
       a.data.LastName,
       a.data.Address.Postcode #b1b100;">AS Postcode,
       a.data.ContactDetails.Email #b1b100;">AS Email
FROM   json_documents a;