I have a json element with this structure

array of letter(A,B,C or D):

• _1,

  -start
  - end    
  

• _2,

    -start
    -end    

• _3,

  -start
  -end

I would like to obtain a table like this

obj_type	1_start	1_end	2_start	2_end	3_start	3_end
A	value	value	value	value	value	value
B	value	value	value	value	value	value
C	value	value	value	value	value	value

value can be null

WITH d (department_data) AS (SELECT (UTL_RAW.cast_to_raw ('{
  "r": [
    {
      "obj_type": "A",
      "_1":  {
        "start": "1",
        "end": "2"
        },
        "_2":  {
        "start": "15",
        "end": "25"
        },
        "_3":  {
        "start": "26",
        "end": "33"
        }
    },
    {
      "obj_type": "B",
      "_1": {
        "start": "1",
        "end": "2"
    },
        "_2":  {
        "start": "3",
        "end": "12"
        }
    },    {
      "obj_type": "C",
      "_2":{
        "start": "1",
        "end": "2"
    }
    },    {
      "obj_type": "D",
      "_3": {
        "start": "",
        "end": "2"
    }
    }
  ]
}')) FROM DUAL)
--select * from d;
SELECT j.*
  FROM d,
       JSON_TABLE (
           d.department_data,
           '$'
           COLUMNS (

               NESTED PATH '$.r[*]'
                    COLUMNS (
                        name PATH '$.obj_type', 
                        NESTED PATH '$._1[*]'
                            columns ("_1_start" PATH '$.start',
                                     "_2_end" PATH '$.end'),
                        NESTED PATH '$."_2"[*]'
                            columns ("UVG_start" PATH '$.start',
                                     "UVG - Zusatz_end" PATH '$.end'),
                        NESTED PATH '$."_3"[*]'
                            columns ("UVG - Ueberschusslohn_start" PATH '$.start',
                                     "UVG - Ueberschusslohn_end" PATH '$.end')
                            ))) j

This query is not returning what I've expected.

The lines are duplicated. start and end from _1 are not the same line of start and end from _2. Furthermore if there is no value they are not printed.

to summarize, something like this

obj_type	1_start	1_end	2_start	2_end	3_start	3_end
A	value	value	nothing	nothing	nothing	nothing
A	nothing	nothing	value	value	nothing	nothing
A	nothing	nothing	nothing	nothing	value	value

same thing with B and C

code

CodePudding user response：

I will write a proper answer when I will have time. But to resolve this problem, you must make that: change the structure of the json string.

array of letter(A,B,C or D):

• array_of number(1,2,3)

    -start
    - end    

select letter, number, start, end from json.

and after that use a the key word pivot to get the tab with 1_start, 2_start ...

CodePudding user response：

With your example data you don't need the repeated nested path elements, you can just do:

SELECT j.*
FROM d
CROSS APPLY JSON_TABLE (
  d.department_data,
  '$'
  COLUMNS (
    NESTED PATH '$.r[*]'
    COLUMNS (
      name PATH '$.obj_type', 
      "_1_start" PATH '$."_1".start',
      "_1_end" PATH '$."_1".end',
      "_2_start" PATH '$."_2".start',
      "_2_end" PATH '$."_2".end',
      "_3_start" PATH '$."_3".start',
      "_3_end" PATH '$."_3".end'
    )
  )
) j

db<>fiddle