JSON Sibling Arrays – With 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. In my previous post JSON Sibling Arrays – Without Ordinal Association, I covered the situation where sibling arrays have no connection through ordinal position, they are separate aspects under the parent object. In this post I cover the other situation where 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.

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.