Specifying Nested Objects within a Query

You can retrieve details from multiple tables by specifying it as a nested object in a GraphQL query.

For example, if you would like to get the details of drivers along with the details of their teams, you can nest the team object within a driver object as shown in the GraphQL query within following function:

SELECT JSON_SERIALIZE(data PRETTY) AS data FROM GRAPHQL('
    query {
        driver {
            id: driver_id
            name
            points
            teamDetails: team {
                teamId: team_id
                teamName: name
                teamPoints: points
            }
        }
    }
');

This would produce output containing details of drivers along with their teams:

DATA
--------------------------------------------------------------------------------
{
  "id" : 101,
  "name" : "Lando Norris",
  "points" : 282,
  "teamDetails" :
  {
    "teamId" : 301,
    "teamName" : "McLaren Mercedes",
    "teamPoints" : 666
  }
}

{
  "id" : 102,
  "name" : "Oscar Piastri",
  "points" : 384,
  "teamDetails" :
  {
    "teamId" : 301,
    "teamName" : "McLaren Mercedes",
    "teamPoints" : 666
  }
}

..............
..............
20 rows selected.

Or, you can retrieve the details of all the drivers belonging to a team using the following code:

SELECT JSON_SERIALIZE(data PRETTY) AS data FROM GRAPHQL('
    query {
        team {
            id: team_id
            name
            points
            drivers: driver {
                driverId: driver_id
                driverName: name
                driverPoints: points
            }
        }
    }
');

This query would produce 10 entries, corresponding to the 10 teams and their driver details.

DATA
--------------------------------------------------------------------------------
{
  "id" : 301,
  "name" : "McLaren Mercedes",
  "points" : 666,
  "drivers" :
  [
    {
      "driverId" : 101,
      "driverName" : "Lando Norris",
      "driverPoints" : 282
    },
    {
      "driverId" : 102,
      "driverName" : "Oscar Piastri",
      "driverPoints" : 384
    }
  ]
}

{
  "id" : 302,
  "name" : "Ferrari",
  "points" : 652,
  "drivers" :
  [
    {
      "driverId" : 103,
      "driverName" : "Charles Leclerc",
      "driverPoints" : 312
    },
    {
      "driverId" : 104,
      "driverName" : "Carlos Sainz Jr.",
      "driverPoints" : 340
    }
  ]
}
.................
.................
10 rows selected.

Starting in 23.26.1, you can also return a nested object field as a JSON array of scalar values instead of an array of objects. To do this, enclose the scalar field selection in square brackets ([ ]) under the nested object. This is useful when you only need a single column value from the nested rows (for example, a list of driver names for each team) and you do not need each nested row to be represented as a JSON object.

Example 3-1 Return driver names as an array of strings for each team

SELECT JSON_SERIALIZE(data PRETTY) AS data FROM GRAPHQL('
    query {
        team {
            teamId: team_id
            teamName: name
            driverNames: driver @link(to: team_id) **[
                name
            ]**
        }
    }
');

In this example, the nested driverNames field is returned as an array of strings (driver names) rather than an array of driver objects.

{
  "teamId" : 301,
  "teamName" : "McLaren Mercedes",
  "driverNames" :
  **[
    "Lando Norris",
    "Oscar Piastri"
  ]**
}
{
  "teamId" : 302,
  "teamName" : "Ferrari",
  "driverNames" :
  **[
    "Charles Leclerc",
    "Carlos Sainz Jr."
  ]**
}
...