Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Recursive descent of multi level JSON array.

thinkaboutitMay 5 2026

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;
This post has been answered by Paulzip on May 7 2026
Jump to Answer
Comments
Post Details
Added on May 5 2026
6 comments
147 views