json_table.sql

SELECT
    P.CLOUD_IDENTITY,
    JT.*
FROM
    V$PDBS P,
    JSON_TABLE ( P.CLOUD_IDENTITY, '$'
            COLUMNS (
                DATABASE_NAME VARCHAR2 ( 30 ) PATH '$.DATABASE_NAME',
                REGION VARCHAR2 ( 300 ) PATH '$.REGION',
                TENANT_OCID VARCHAR2 ( 300 ) PATH '$.TENANT_OCID',
                DATABASE_OCID VARCHAR2 ( 300 ) PATH '$.DATABASE_OCID',
                COMPARTMENT_OCID VARCHAR2 ( 300 ) PATH '$.COMPARTMENT_OCID',
                OUTBOUND_IP_ADDRESS VARCHAR2(50 CHAR) FORMAT JSON WITH WRAPPER PATH '$.OUTBOUND_IP_ADDRESS[*]',
                PUBLIC_DOMAIN_NAME VARCHAR2 ( 300 ) PATH '$.PUBLIC_DOMAIN_NAME',
                TENANT_ACCOUNT_NAME VARCHAR2 ( 300 ) PATH '$.TENANT_ACCOUNT_NAME',
                INFRASTRUCTURE VARCHAR2 ( 300 ) PATH '$.INFRASTRUCTURE',
                SERVICE VARCHAR2 ( 300 ) PATH '$.SERVICE',
                APPLICATIONS VARCHAR2(50 CHAR) FORMAT JSON WITH WRAPPER PATH '$.APPLICATIONS[*]',
                AVAILABILITY_DOMAIN VARCHAR2 ( 300 ) PATH '$.AVAILABILITY_DOMAIN'
            )
        )
    JT