JSON Sibling Arrays – Without Ordinal Association

Oracle’s JSON_TABLE provides a facility to un-nest arrays using the nested path clause, but if you try to use that with sibling arrays you can end up with unexpected results. Sibling arrays here mean separate arrays, but at the same level under the same parent object.

There are two types of sibling array scenarios:
1. Without Ordinal Association – Array items have no connection through ordinal position, they are separate aspects under the parent object.
2. With Ordinal Association – Array items are linked across the sibling arrays by ordinal position. So the n^th array object in one array is linked in some way to the n^th array object in the sibling array. This is covered in my post JSON Sibling Arrays – With Ordinal Association.

In this post, I’ll cover the first situation, without ordinal association. Let’s create an example to explain what I mean, in this case data for Tennis Doubles’ teams.

create table t_teams(js) as
  select to_clob('
  {
    "Teams" : [
      {
        "Name" : "The Superstars",
        "Manager" : "Joe Bloggs",
        "Players" : [
          {
            "Name" : "John Smith"
          },
          {
            "Name" : "Mark Jerome"
          }
        ],
        "Trophies" : [
          {
            "Name" : "League 2 Champion",
            "Date" : "2022-06-24"
          },
          {
            "Name" : "Challenge Cup",
            "Date" : "2021-05-04"
          }
        ]
      },
      {
        "Name" : "The Golden Oldies",
        "Manager" : "Steve Hart",
        "Players" : [
          {
            "Name" : "Trevor Munn"
          },
          {
            "Name" : "Jerry Hand"
          }
        ],
        "Trophies" : [
          {
            "Name" : "Taylor Cup",
            "Date" : "2020-05-13"
          }
        ]
      },
      {
        "Name" : "Double Trouble",
        "Manager" : "Mary Gantt",
        "Players" : [
          {
            "Name" : "Peter Nolan"
          },
          {
            "Name" : "Will Deacon"
          }
        ],
        "Trophies" : []
      }
    ]
  }')
  from dual
/

Table created.

Let’s write a query to flatten the pertinent data, so that there’s a row for each team’s player and trophy information.

select te.*
from t_teams tt
join json_table (
       js
     , '$.Teams[*]'
       columns (
         TeamName varchar2(100) path '$.Name'
       , Manager  varchar2(100) path '$.Manager'
       , nested                 path '$.Players[*]'
         columns (
           PlayerNum  for ordinality
         , PlayerName varchar2(100) path '$.Name'
         )
       , nested                 path '$.Trophies[*]'
         columns (
           TrophyName varchar2(100) path '$.Name'
         , TrophyDate date          path '$.Date'
         )
       )
     ) te on 1 = 1
/

TEAMNAME         |MANAGER   | PLAYERNUM|PLAYERNAME |TROPHYNAME       |TROPHYDATE           
-----------------|----------|----------|-----------|-----------------|---------------------
The Superstars   |Joe Bloggs|         1|John Smith |                 |
The Superstars   |Joe Bloggs|         2|Mark Jerome|                 |
The Superstars   |Joe Bloggs|          |           |League 2 Champion|24/06/2022
The Superstars   |Joe Bloggs|          |           |Challenge Cup    |04/05/2021
The Golden Oldies|Steve Hart|         1|Trevor Munn|                 |
The Golden Oldies|Steve Hart|         2|Jerry Hand |                 |
The Golden Oldies|Steve Hart|          |           |Taylor Cup       |13/05/2020
Double Trouble   |Mary Gantt|         1|Peter Nolan|                 |
Double Trouble   |Mary Gantt|         2|Will Deacon|                 |

9 rows selected.

The query isn’t giving the results we expect, as the Players and Trophies sibling arrays that have been un-nested, are on separate lines. Why is that? Well, sibling arrays in JSON generally represent different things, so Oracle return values for sibling arrays in different rows. The total number of returned rows is the sum of the items in the sibling array treated as a UNION and not the cartesian product.

So, how would we correct this? We have to chain one of the two sibling arrays to another JSON_TABLE clause instead of using nested path clause.

select /*+ no_merge(te) */ te.Name, te.Manager, te.PlayerNum, te.PlayerName, tr.*
from t_teams tt
join json_table (
       tt.js
     , '$.Teams[*]'
       columns (
         TeamName varchar2(100) path '$.Name'
       , Manager  varchar2(100) path '$.Manager'
       , Trophies varchar2(500) format json path '$.Trophies'
       , nested path '$.Players[*]'
         columns (
           PlayerNum  for ordinality
         , PlayerName varchar2(100) path '$.Name'
         )
       )
     ) te on 1 = 1
left join json_table (
            te.Trophies
          , '$[*]'
            columns (
              TrophyName varchar2(100) path '$.Name'
            , TrophyDate date          path '$.Date'
            )
          ) tr on 1 = 1
/

TEAMNAME         |MANAGER   | PLAYERNUM|PLAYERNAME |TROPHYNAME       |TROPHYDATE           
-----------------|----------|----------|-----------|-----------------|---------------------
The Superstars   |Joe Bloggs|         1|John Smith |League 2 Champion|24/06/2022
The Superstars   |Joe Bloggs|         1|John Smith |Challenge Cup    |04/05/2021
The Superstars   |Joe Bloggs|         2|Mark Jerome|League 2 Champion|24/06/2022
The Superstars   |Joe Bloggs|         2|Mark Jerome|Challenge Cup    |04/05/2021
The Golden Oldies|Steve Hart|         1|Trevor Munn|Taylor Cup       |13/05/2020
The Golden Oldies|Steve Hart|         2|Jerry Hand |Taylor Cup       |13/05/2020
Double Trouble   |Mary Gantt|         1|Peter Nolan|                 |
Double Trouble   |Mary Gantt|         2|Will Deacon|                 |

8 rows selected.

The NO_MERGE hint is required to make the first JSON_TABLE (aliased as “te”) a non-mergeable view. Without this, you would get an ORA-40556: unsupported chaining of JSON_TABLE exception.