I have JSON document as one of column in a table. Its structured as below where column name are within nested key.

{
  "ACT_CASHFLOW_FE_COLS": [
    {
      "MCR": "MCR_1",
      "COLUMN_VALUE": "UVW"
    },
    {
      "MCR": "MCR_2",
      "COLUMN_VALUE": "XYZ"
    }
  ],
  "ACT_CASHFLOW_INT_RATE": [
    {
      "MCR": "MCR_1",
      "COLUMN_VALUE": "UVW1"
    },
    {
      "MCR": "MCR_2",
      "COLUMN_VALUE": "XYZ1"
    }
  ],
  "ACT_CASHFLOW_INP_COLS": [
    {
      "MCR": "MCR_1",
      "COLUMN_VALUE": "UVW2"
    },
    {
      "MCR": "MCR_2",
      "COLUMN_VALUE": "XYZ2"
    }
  ],
  "ASS_CASHFLOW_FE_COLS": [
    {
      "MCR": "MCR_1",
      "COLUMN_VALUE": "UVW3"
    },
    {
      "MCR": "MCR_2",
      "COLUMN_VALUE": "XYZ3"
    }
  ],
  "ASS_CASHFLOW_INT_RATE": [
    {
      "MCR": "MCR_1",
      "COLUMN_VALUE": "UVW4"
    },
    {
      "MCR": "MCR_2",
      "COLUMN_VALUE": "XYZ4"
    }
  ],
  "ASS_CASHFLOW_INP_COLS": [
    {
      "MCR": "MCR_1",
      "COLUMN_VALUE": "UVW5"
    },
    {
      "MCR": "MCR_2",
      "COLUMN_VALUE": "XYZ5"
    }
  ]
}

I want to have relational DB output as below :

I have tried below query but it does not work

SELECT jt.*
  FROM JSON_TABLE(frd.run_dtls_document,
                  '$' COLUMNS(
                       NESTED PATH '$.ACT_CASHFLOW_FE_COLS[*]'
                        COLUMNS(
                                act_cashflow_fe_cols VARCHAR2(320) PATH '$.COLUMN_VALUE',
                                mcr                  VARCHAR2(320) PATH '$.MCR'
                                ),
                       NESTED PATH '$.ACT_CASHFLOW_INT_RATE[*]'
                        COLUMNS(
                                act_cashflow_int_rate VARCHAR2(320) PATH '$.COLUMN_VALUE',
                                mcr                   VARCHAR2(320) PATH '$.MCR'
                                ),
                       NESTED PATH '$.ACT_CASHFLOW_INP_COLS[*]'
                        COLUMNS(
                                act_cashflow_inp_cols VARCHAR2(320) PATH '$.COLUMN_VALUE',
                                mcr                   VARCHAR2(320) PATH '$.MCR'
                                ),
                       NESTED PATH '$.ASS_CASHFLOW_FE_COLS[*]'
                        COLUMNS(
                                ass_cashflow_fe_cols  VARCHAR2(320) PATH '$.COLUMN_VALUE',
                                mcr                   VARCHAR2(320) PATH '$.MCR'
                                ),
                       NESTED PATH '$.ASS_CASHFLOW_INT_RATE[*]'
                        COLUMNS(
                                ass_cashflow_int_rate VARCHAR2(320) PATH '$.COLUMN_VALUE',
                                mcr                   VARCHAR2(320) PATH '$.MCR'
                                ),
                       NESTED PATH '$.ASS_CASHFLOW_INP_COLS[*]'
                        COLUMNS(
                                ass_cashflow_inp_cols VARCHAR2(320) PATH '$.COLUMN_VALUE',
                                mcr                   VARCHAR2(320) PATH '$.MCR'
                                )
                      )) jt;

Any suggestion will be appreciated.

CodePudding user response：

You can use successive cross joins among individual JSON_TABLE statements, and then filter out by mcr columns which are named differently to get rid of ambiguity such as

WITH j AS
(  
 SELECT *
   FROM frd f,                        --> assuming frd is the table
        JSON_TABLE(run_dtls_document, --> and this is the JSON type column of it
                  '$' COLUMNS (
                         NESTED PATH '$.ACT_CASHFLOW_FE_COLS[*]'
                          COLUMNS(
                                act_cashflow_fe_cols VARCHAR2(320) PATH '$.COLUMN_VALUE',
                                mcr                  VARCHAR2(320) PATH '$.MCR'
                          )
                       )
                   ) j1,
       JSON_TABLE(run_dtls_document, 
                  '$' COLUMNS (
                         NESTED PATH '$.ACT_CASHFLOW_INT_RATE[*]'
                          COLUMNS(
                                act_cashflow_int_rate VARCHAR2(320) PATH '$.COLUMN_VALUE',
                                mcr2                  VARCHAR2(320) PATH '$.MCR'
                          )
                       )
                   ) j2,
       JSON_TABLE(run_dtls_document, 
                  '$' COLUMNS (
                         NESTED PATH '$.ACT_CASHFLOW_INP_COLS[*]'
                          COLUMNS(
                                act_cashflow_inp_cols VARCHAR2(320) PATH '$.COLUMN_VALUE',
                                mcr3                  VARCHAR2(320) PATH '$.MCR'
                          )
                       )
                   ) j3,
       JSON_TABLE(run_dtls_document, 
                  '$' COLUMNS (
                         NESTED PATH '$.ASS_CASHFLOW_FE_COLS[*]'
                          COLUMNS(
                                ass_cashflow_fe_cols VARCHAR2(320) PATH '$.COLUMN_VALUE',
                                mcr4                  VARCHAR2(320) PATH '$.MCR'
                          )
                       )
                   ) j4,
       JSON_TABLE(run_dtls_document, 
                  '$' COLUMNS (
                         NESTED PATH '$.ASS_CASHFLOW_INT_RATE[*]'
                          COLUMNS(
                                ass_cashflow_int_rate VARCHAR2(320) PATH '$.COLUMN_VALUE',
                                mcr5                  VARCHAR2(320) PATH '$.MCR'
                          )
                       )
                   ) j5,
       JSON_TABLE(run_dtls_document, 
                  '$' COLUMNS (
                         NESTED PATH '$.ASS_CASHFLOW_INP_COLS[*]'
                          COLUMNS(
                                ass_cashflow_inp_cols VARCHAR2(320) PATH '$.COLUMN_VALUE',
                                mcr6                  VARCHAR2(320) PATH '$.MCR'
                          )
                       )
                   ) j6
)
SELECT mcr, 
       act_cashflow_fe_cols, act_cashflow_int_rate, act_cashflow_inp_cols,
       ass_cashflow_fe_cols, ass_cashflow_int_rate, ass_cashflow_inp_cols 
  FROM j
 WHERE mcr = mcr2
   AND mcr = mcr3
   AND mcr = mcr4
   AND mcr = mcr5
   AND mcr = mcr6

Demo