There is an undocumented API on the Salesforce Sales Cloud element that lets you pass an SOQL query directly to Salesforce. CEQL (Cloud Elements Query Language) supports, for the most part, the most basic functionality of SQL. Typically something like this:
select * from Account where Name = 'TestAccount'
Salesforce actually supports much more than this. Such as aggregate functions like count(Id) and even INNER JOIN and OUTER JOIN.
See this documentation for what salesforce supports: https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql.htm
On Cloud Elements, you can use this extra querying functionality through the GET /query api.
The syntax is as follows:
GET /query?q=SELECT count(Id) FROM Contact WHERE name = 'test'&page=1&pageSize=200
GET /query?q=SELECT Amount, Id, Name, (SELECT Quantity, ListPrice, PricebookEntry.UnitPrice, PricebookEntry.Name FROM OpportunityLineItems) FROM Opportunity
This API is undocumented and only available on the Salesforce Sales Cloud element.
The SFDC element also supports a raw query endpoint (GET /raw-query) that can be used when nested select statements are required. Similar to the /query resource, this endpoint is not in the API Docs, but is accessible when you make API calls against SFDC element instances. An example of using this endpoint is:
/hubs/crm/raw-query?q=select%20id%20from%20Account%20where%20OwnerId%20in%20(select%20User.Id%20from%20User%20where%20User.Id%20=%20'005f4111002Nmt1XXX')
