@NEST and @UNNEST Directives

Directives @nest and @unnest specify nesting and unnesting (flattening) of intermediate objects in both table function as well as in duality-view definition.

Directive @unnest corresponds to SQL keyword UNNEST (there’s no keyword NEST in SQL corresponding to directive @nest). The following are the restrictions when using these two directives:

You cannot unnest a field that has an alias.
When using for duality-views, you cannot nest fields that correspond to identifying columns of the root table (primary-key columns, identity columns, or columns with a unique constraint or unique index).
When using the @nest directive, there are restrictions on what can be specified together with it. In particular, no other directive can be used in conjunction with @nest. Oracle allows the following directives to be used on nested objects in this context: @generated and @hidden.

You can use the following code to unnest the details of the team in the drivers object:

SELECT JSON_SERIALIZE(data PRETTY) AS data FROM GRAPHQL('
    query {
        driver {
            driverId: driver_id
            driverName: name
            driverPoints: points
            team @unnest {
                teamId: team_id
                teamName: name
                teamPoints: points
            }
        }
    }
');

This code produces the following output:

DATA
--------------------------------------------------------------------------------
{
  "driverId" : 101,
  "driverName" : "Lando Norris",
  "driverPoints" : 282,
  "teamId" : 301,
  "teamName" : "McLaren Mercedes",
  "teamPoints" : 666
}

{
  "driverId" : 102,
  "driverName" : "Oscar Piastri",
  "driverPoints" : 384,
  "teamId" : 301,
  "teamName" : "McLaren Mercedes",
  "teamPoints" : 666
}

{
  "driverId" : 103,
  "driverName" : "Charles Leclerc",
  "driverPoints" : 312,
  "teamId" : 302,
  "teamName" : "Ferrari",
  "teamPoints" : 652
}
...................................
...................................
...................................
20 rows selected.

The following code shows the usage of @nest directive where you can nest the points of team in the previous query:

SELECT JSON_SERIALIZE(data PRETTY) AS data FROM GRAPHQL('
    query {
        driver {
            driverId: driver_id
            driverName: name
            driverPoints: points
            team @unnest {
                teamId: team_id
                teamName: name
                teamPoints @nest {
                    points
                }
            }
        }
    }
');

You can see the points field nested in the following output:

DATA
--------------------------------------------------------------------------------
{
  "driverId" : 101,
  "driverName" : "Lando Norris",
  "driverPoints" : 282,
  "teamId" : 301,
  "teamName" : "McLaren Mercedes",
  "teamPoints" :
  {
    "points" : 666
  }
}

{
  "driverId" : 102,
  "driverName" : "Oscar Piastri",
  "driverPoints" : 384,
  "teamId" : 301,
  "teamName" : "McLaren Mercedes",
  "teamPoints" :
  {
    "points" : 666
  }
}
....................................
....................................
....................................
20 rows selected.

Detailed example where @unnest directive is used in creating JSON-Relational Duality View can be found here.