Im trying to convert JSON to Table using Oracle JSON TBALE. Here is my sample data

 with table1 as (
select '

{
  "id" : "Example",
  "address" : [
      {
        "city" : "NY",
        "state" : "TN"
      },
      {
        "city" : "Jersey city",
        "state" : "NJ"
      }
    ],
                
                "dep" : [
      {
        "id" : "1",
        "name" : "HR"
      },
      {
        "id" : "1",
        "name" : "Sales"
      }
    ],
}' as col1 from dual
) 

Code:

select jt.* from 
    table1 rt,
    JSON_TABLE ( col1 FORMAT JSON, '$[*]'
            COLUMNS
                id VARCHAR2 PATH '$.id',
                NESTED PATH '$.address[*]'
                    COLUMNS (
                        city  PATH '$.city',
                        state1  PATH '$.state'
                      
                    ),
                    
                       NESTED PATH '$.dep[*]'
                    COLUMNS (
                        dep_id  PATH '$.id',
                        dept_name  PATH '$.name'
                      
                    )
                    )jt

Output I'm getting :

Expected output :

How the JSON Table method can be modified to get the desired output ?

CodePudding user response：

You can use the following example. It in fact generates 2 nested tables and joins them. Maybe there's some more expert than me in json_table who can provide a better solution.

with table1 as (
select '

{
  "id" : "Example",
  "address" : [
      {
        "city" : "NY",
        "state" : "TN"
      },
      {
        "city" : "Jersey city",
        "state" : "NJ"
      }
    ],
                
                "dep" : [
      {
        "id" : "1",
        "name" : "HR"
      },
      {
        "id" : "1",
        "name" : "Sales"
      }
    ],
}' as col1 from dual
) 
select jt.id, jt.city, jt.state1, jt1.dep_id, jt1.dept_name from 
    table1 rt,
    JSON_TABLE ( col1 FORMAT JSON, '$[*]'
            COLUMNS
                id VARCHAR2 PATH '$.id',
                NESTED PATH '$.address[*]'
                    COLUMNS (
                        city  PATH '$.city',
                        state1  PATH '$.state'
                      
                    )                    )jt,
            JSON_TABLE ( col1 FORMAT JSON, '$[*]'
            COLUMNS
                id VARCHAR2 PATH '$.id',
                       NESTED PATH '$.dep[*]'
                    COLUMNS (
                        dep_id  PATH '$.id',
                        dept_name  PATH '$.name'
                      
                    )
                    )jt1
where jt.id=jt1.id;

CodePudding user response：

Use two JSON_TABLEs and CROSS JOIN (or CROSS APPLY) them:

select a.*, d.*
from   table1 rt
       CROSS APPLY JSON_TABLE(
         col1 FORMAT JSON,
         '$'
         COLUMNS
           id VARCHAR2 PATH '$.id',
           NESTED PATH '$.address[*]'
           COLUMNS (
             city  PATH '$.city',
             state1  PATH '$.state'
           )
       ) a
       CROSS APPLY JSON_TABLE(
         col1 FORMAT JSON,
         '$.dep[*]'
         COLUMNS (
           dep_id  PATH '$.id',
           dept_name  PATH '$.name'
         )
       ) d

Which, for the sample data, outputs:

ID	CITY	STATE1	DEP_ID	DEPT_NAME
Example	NY	TN	1	HR
Example	NY	TN	1	Sales
Example	Jersey city	NJ	1	HR
Example	Jersey city	NJ	1	Sales

db<>fiddle here