-- Store JSON data in a Oracle database table

CREATE TABLE j_medical_transaction
(trans_id        NUMBER GENERATED BY DEFAULT AS IDENTITY
                   (START WITH 1 INCREMENT BY 1) PRIMARY KEY,
 trans_guid      RAW(16) NOT NULL,
 created_date    TIMESTAMP WITH TIME ZONE,
 med_document    CLOB
                 CONSTRAINT check_json CHECK (med_document IS JSON)
);

-- Insert JSON data into Oracle Table

INSERT INTO j_medical_transaction
 (trans_guid,
  created_date,
  med_document
 )
SELECT
  SYS_GUID(),
  SYSTIMESTAMP,
  '{"HCPCSCode"      : "99001",
    "PatientID"      : "1A2B3C4D5E",
    "LocationCode"   : "NY001",
    "ProviderID"     : "1A1-2B2-3C3",
    "ProviderAddr"   : {"AddrNo":  100, "Type":"Number",
                        "AddrIdx": "A", "Type":"String",
                        "AddrLine1": "1st Avenue", "Type":"String",
                        "AddrLine2": " ", "Type":"String",
                        "City": "New York", "Type":"String",
                        "State": "NY", "Type":"String",
                        "ZipCode": 10001, "Type":"String",
                        "Phone": 2120000000, "Type":"Number"
                       },
    "InsuranceCo"    : "A1B2C3, Insurance Co.",
    "PolicyNo"       : "9O8B7I6J5A4BZ0Z"
   }'
FROM DUAL
;

COMMIT;

-- Querying the table and parse content

SELECT jmt.med_document.PatientID  FROM  j_medical_transaction jmt
;

SELECT 
  jmt.med_document.LocationCode,
  jmt.med_document.ProviderAddr.AddrNo||
  jmt.med_document.ProviderAddr.AddrIdx||' '||
  jmt.med_document.ProviderAddr.AddrLine1||
  DECODE(jmt.med_document.ProviderAddr.AddrLine2,NULL,'',
     ', '||jmt.med_document.ProviderAddr.AddrLine2)||
  jmt.med_document.ProviderAddr.City||', '||
  jmt.med_document.ProviderAddr.State||' '||
  jmt.med_document.ProviderAddr.ZipCode  as Location_address
FROM  j_medical_transaction jmt
;