How to avoid to duplicate columns when getting a table from a json element

Question

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

Kindly format the question properly

Nishant Gupta
– Nishant Gupta

2022-06-03 07:11:00 +00:00
Commented Jun 3, 2022 at 7:11 — Nishant Gupta
– Nishant Gupta, Commented Jun 3, 2022 at 7:11
@Nishant Gupta I think the format is better now.

Pierre-olivier Gendraud
– Pierre-olivier Gendraud

2022-06-03 07:31:04 +00:00
Commented Jun 3, 2022 at 7:31 — Pierre-olivier Gendraud
– Pierre-olivier Gendraud, Commented Jun 3, 2022 at 7:31

Alex Poole · Accepted Answer · 2022-06-04 11:16:35Z

1

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

NAME	_1_start	_1_end	_2_start	_2_end	_3_start	_3_end
A	1	2	15	25	26	33
B	1	2	3	12	null	null
C	null	null	1	2	null	null
D	null	null	null	null	null	2

db<>fiddle

answered Jun 4, 2022 at 11:16

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to avoid to duplicate columns when getting a table from a json element

1 Answer 1

Comments

Your Answer

Hot Network Questions

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

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

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related