Are you using the REST API to query Salesforce data? Do you need to query related objects, traversing parent/child relationships that are multiple levels deep? If so, then you will be making multiple API calls, handling multiple responses, and relating records in one response to records in another response in order to maintain the parent/child relationship between records across different responses.

This blog post will show you how to combine those multiple API calls into a single API call, both simplifying your code and reducing the number of API calls counted against your daily API request limit.

An introduction to nested SOQL queries

Prior to the Summer ’23 release, SOQL only supported parent/child relationship queries that were one level from the root:

For example, the following REST API call against the /query endpoint executes a SOQL query against the Account object and the related child Contact objects.

GET .../services/data/v59.0/query?q=SELECT Name, Description, (SELECT FirstName, LastName FROM Contacts) FROM Account

Note: the above query, and all other queries in this blog, are not URL encoded for readability. When making these REST API calls, you should URL encode your queries.

The query below returns a single JSON response containing the selected fields from the Account object and the related child Contact objects.

What happens if you need to query related parent/child objects deeper than this limit? For example, what if you query the Account → Contact → Asset → WorkOrder → WorkOrderLineItem objects, traversing parent/child relationships? Prior to the Summer ’23 release, you would need to make a series of API calls to execute multiple SOQL queries.

First, perform the following API call to execute the SOQL query to get the Account and Contact records.

GET .../services/data/v59.0/query?q=SELECT Name, (SELECT Id, FirstName, LastName) FROM Contacts) FROM Account

Second, extract all of the Contact Ids from the result, and then perform the API call below to execute the following SOQL query to get the Asset and WorkOrder records.

GET .../services/data/v59.0/query?q=SELECT Name, ContactId, (SELECT Id, WorkOrderNumber, Subject, Description FROM WorkOrders) FROM Asset WHERE ContactId IN (...list of Contact Ids...)

The ContactId field in the Asset object needs to be returned, so that the Asset records can be correctly associated with the parent Contact records from the first query.

Third, extract all of the WorkOrder Ids from the result, and then perform the API call below to execute the following SOQL query to get the WorkOrderLineItem records.

GET .../services/data/v59.0/query?q=SELECT LineItemNumber, Description, WorkOrderId FROM WorkOrderLineItem WHERE WorkOrderId IN (...list of WorkOrder Ids...)

The WorkOrderId field in the WorkOrderLineItem object needs to be returned so that the WorkOrderLineItem records can be correctly associated with the parent WorkOrder records from the second query.

With this approach, your code needs to make three API calls and handle three query results. It also needs to manage relating the Asset records in the second query to the Contact records from the first query, and relating the WorkOrderLineItem records in the third query to the WorkOrder records from the second query.

Expanding the depth of nested SOQL queries

With the Summer ’23 release, SOQL supports parent/child relationship queries that are four levels deep from the root when called via the REST API.

Re-visiting the example above, using the new nested SOQL query support means that you can now query the Account → Contacts → Assets → WorkOrders → WorkOrderLineItems objects and traverse the parent/child relationships using a single API call. Also, you only need to handle a single query result where the relationship between the Contact and Asset records is explicitly defined in the JSON response, enabling a simpler implementation.

The following REST API call against the /query endpoint executes a nested SOQL query that traverses the Account → Contacts → Assets → WorkOrders → WorkOrderLineItems parent/child relationships.

GET .../services/data/v59.0/query?q=SELECT Name, Description, (SELECT FirstName, LastName, (SELECT Name, (SELECT WorkOrderNumber, Subject, Description, (SELECT LineItemNumber, Description FROM WorkOrderLineItems) FROM WorkOrders) FROM Assets) FROM Contacts) FROM Account

The query returns a single, nested JSON response containing all the selected fields from the Account object and the related Contacts →Assets → WorkOrders → WorkOrderLineItems child objects.

Conclusion

As illustrated above, the introduction of nested SOQL support in the REST API greatly simplifies querying related parent/child objects multiple levels deep, allowing two API calls to be collapsed into a single API call and simplifying the processing of the results. In addition, it reduces the number of API calls counted against your daily API limit.

Resources

• Platform API Basics
• Salesforce Platform APIs Collections for Postman
• Quick Start: Connect Postman to Salesforce

About the author

Jeremy Westerman is a Director of Product Management at Salesforce where he’s responsible for Salesforce APIs to access data on the core Salesforce Platform. Follow him on LinkedIn and the Trailblazer Community.