Oracle - Parsing Multiple JSON Values in a single SQL query

Question

I have data in PersonalDetails table in JSON format, I am trying to parse the data in a single query, currently I am using JSON_TABLE functions, in my query I am only able to parse only one column, if I try to use JSON_TABLE functions twice it throwing me an error, is their a way to parse all the columns in a single query? or is their any other function apart from JSON_TABLE?

Sample Data

{
      "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
}  

My Query

SELECT 
FirstName,
LastName,
Job,
Street,
City,
Country,
Postcode,
ContactDetails,
DateOfBirth,
Active
FROM
JSON_TABLE(tab.Address, '$' COLUMNS
    ( Address VARCHAR(255) PATH '$.Street',
        City VARCHAR(255) PATH '$.City',
        Country VARCHAR(255) PATH '$.Country',
        Postcode VARCHAR(255) PATH '$.Postcode',
    )) JT,
    PersonalDetails tab;

Answer 1

You could use the NESTED clause:

with PersonalDetails (data) as (
select
'{
      "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
}' 
from dual
)
SELECT jt.*
FROM PersonalDetails,
 JSON_TABLE(data, '$'
   COLUMNS (FirstName VARCHAR2(50), LastName VARCHAR2(50), Job, Active,
   NESTED ContactDetails COLUMNS (Email VARCHAR2(100), Phone)
   )
) jt;

Output:

FIRSTNAME  LASTNAME JOB   ACTIVE EMAIL                PHONE
---------- -------- ----- ------ -------------------- ---------------
John       Doe      Clerk true   john.doe@example.com 44 123 123456

Answer 2

with PersonalDetails (jsn) as (
select '{
      "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
}' from dual
)
select jt.* from PersonalDetails, json_table (
  PersonalDetails.jsn, '$' 
  COLUMNS
  Firstname VARCHAR2(30) PATH '$.FirstName',
  Address  VARCHAR2(255) PATH '$.Address.Street',
  City     VARCHAR2(255) PATH '$.Address.City',
  Country  VARCHAR2(255) PATH '$.Address.Country',
  Postcode VARCHAR2(255) PATH '$.Address.Postcode',
  dob      VARCHAR2(11) PATH '$.DateOfBirth',
  email    VARCHAR2(50) PATH '$.ContactDetails.Email'
) jt;