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('
    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('
    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('
    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."
  ]
}
...