Hi all.
I've got some JSON data where the same object (item_id, item_name in the example below) is repeated at multiple levels.
I want to extract the data and convert it into a simple table with all values for the same key appearing in the same column.
The data has multiple varying depths of the object so I can make it work by adding multiple UNION ALLs as shown below but wondered if there's a nicer option that doesn't rely on me having to add queries for each level of objects.
Thanks
PK,ITEM_ID,ITEM_NAME
12345,1,'A'
12345,2,'B'
12345,21,'BA'
12345,22,'BB'
SELECT j.pk, j.item_id, j.item_name
FROM JSON_TABLE(
'{"pk":12345,"items":[{"item_id":1,"item_name":"A"},{"item_id":2,"item_name":"B","items":[{"item_id":21,"item_name":"BA"},{"item_id":22,"item_name":"BB"}]}]}'
,'$'
ERROR ON ERROR
COLUMNS(
pk NUMBER PATH '$.pk'
,NESTED PATH '$.items[*]' COLUMNS(item_id NUMBER PATH '$.item_id', item_name VARCHAR2 PATH '$.item_name'))) j
UNION ALL
SELECT j.pk, j.item_id, j.item_name
FROM JSON_TABLE(
'{"pk":12345,"items":[{"item_id":1,"item_name":"A"},{"item_id":2,"item_name":"B","items":[{"item_id":21,"item_name":"BA"},{"item_id":22,"item_name":"BB"}]}]}'
,'$'
ERROR ON ERROR
COLUMNS(
pk NUMBER PATH '$.pk'
,NESTED PATH '$.items[*].items[*]'
COLUMNS(item_id NUMBER PATH '$.item_id', item_name VARCHAR2 PATH '$.item_name'))) j;