Query Service
Use the query service to quickly retrieve precise, relevant data from your Sage Intacct company, based on conditions you specify. It lets you build custom, flexible queries to filter, sort, and return data tailored to your business needs.
- Flexible, condition-based filtering - Retrieve exactly the data you need using operators and logic across standard and custom fields.
- Cross-object access - Query fields from related objects enabling richer and more connected data retrieval in a single request.
- Built for precise control - Tailor each query to your exact needs by controlling field selection, sort order and filtering.
To learn more about issuing basic query requests, read the query information in the REST API reference. For examples of specific types of queries and to learn how to sort and filter results, read the sections of this guide below.
Object queries
This section explains how to structure queries based on a single object and how to retrieve data from referenced objects, to access information contained directly in an object and information contained in a related object. The following sections describe the types of object queries you can perform:
- Single object - Queries focus on one main object, using it's fields and related data to filter and return results.
- Multiple objects - Retrieve several distinct data sets in one query, each with it's own structure and parameters.
- Referenced objects - Access fields from related objects using dot notation.
Querying a single object
When querying a single object, the focus is on one main object, using its fields and related data to filter and return results in a simple, direct way. Each single query request can only have one object.
The following basic query request returns account groups in the Chart of Accounts:
{
"object": "general-ledger/account-group",
"fields": [
"key",
"id",
"href"
]
}The response returns the key, id, and href for all the account group objects:
{
"ia::result": [
{
"key": "381",
"id": "Customer Count End of Period",
"href": "/objects/general-ledger/account-group/381"
},
{
"key": "382",
"id": "Customer Churn Rate Net",
"href": "/objects/general-ledger/account-group/382"
},
{
"key": "383",
"id": "Existing Customers Kept",
"href": "/objects/general-ledger/account-group/383"
}
],
"ia::meta": {
"totalCount": 3,
"start": 1,
"pageSize": 100,
"next": null,
"previous": null
}
}Querying multiple (batch) objects
You can construct an array of distinct objects within a single query request to retrieve multiple datasets simultaneously, each with its own parameters and structure.
For more information, read about batch, bulk and composite requests.
This example query request filters both active vendors and active vendor payment providers:
[
{
"object": "accounts-payable/vendor",
"fields": [
"key",
"id",
"name",
"href"
],
"filters": [
{"$eq": {"status": "active"}}
]
},
{
"object": "accounts-payable/vendor-payment-provider",
"fields": [
"key",
"id",
"href"
],
"filters": [
{"$eq": {"status": "active"}}
]
}
]The response returns the key, id, name, and href for all the vendor and vendor-payment-provider objects:
{
"ia::result": [
{
"ia::result": [
{
"key": "274",
"id": "GK144",
"name": "GK144",
"href": "/objects/accounts-payable/vendor/274"
},
{
"key": "275",
"id": "GK145",
"name": "GK145",
"href": "/objects/accounts-payable/vendor/275"
},
{
"key": "276",
"id": "GK146",
"name": "GK146",
"href": "/objects/accounts-payable/vendor/276"
}
],
"ia::meta": {
"totalCount": 3,
"start": 1,
"pageSize": 100,
"next": null,
"previous": null
}
},
{
"ia::result": [
{
"key": "11242",
"id": "vendor_624B435F",
"name": "vendor_624B435F",
"href": "/objects/accounts-payable/vendor-payment-provider/11242"
},
{
"key": "11243",
"id": "vendor_299B1615",
"name": "vendor_299B1615",
"href": "/objects/accounts-payable/vendor-payment-provider/11243"
},
{
"key": "11244",
"id": "vendor_5F7C36D6",
"name": "vendor_5F7C36D6",
"href": "/objects/accounts-payable/vendor-payment-provider/11244"
},
{
"key": "11245",
"id": "vendor_79363876",
"name": "vendor_79363876",
"href": "/objects/accounts-payable/vendor-payment-provider/11245"
}
],
"ia::meta": {
"totalCount": 4,
"start": 1,
"pageSize": 100,
"next": null,
"previous": null
}
}
],
"ia::meta": {
"totalCount": 2,
"totalSuccess": 2,
"totalError": 0
}
}Referenced objects
When querying an object that contains references to other objects, you can use dot notation to access fields from the referenced objects.
The following example returns bank transaction rule maps and their associated rules for a specified bank transaction rule set:
{
"object": "cash-management/bank-txn-rule-map",
"fields": [
"key",
"id",
"bankTxnRule.id",
"bankTxnRule.name",
"href"
],
"filters": [
{
"$eq": {
"bankTxnRuleSet.id": "2"
}
}
]
}The response returns the key, id, bankTxnRule.id, bankTxnRule.name, and href for all the bank-txn-rule-map objects:
{
"ia::result": [
{
"key": "1",
"id": "1",
"bankTxnRule.id": "21",
"bankTxnRule.name": "Match by amount and doc number",
"href": "/objects/cash-management/bank-txn-rule-map/1"
},
{
"key": "2",
"id": "2",
"bankTxnRule.id": "28",
"bankTxnRule.name": "Match amount plus day range",
"href": "/objects/cash-management/bank-txn-rule-map/2"
},
{
"key": "3",
"id": "3",
"bankTxnRule.id": "27",
"bankTxnRule.name": "BOA Create CC txn from bank txn",
"href": "/objects/cash-management/bank-txn-rule-map/3"
}
],
"ia::meta": {
"totalCount": 3,
"start": 1,
"pageSize": 100,
"next": null,
"previous": null
}
}Sorting query results
Sorting controls the order of query results by specifying fields and sort direction (ascending or descending). You can also sort fields from related objects.
Order by a single field
Use orderBy to sort query results by a single field, specifying the field name and the desired sort direction (ascending or descending).
This query returns journal entries sorted in ascending order by posting date:
{
"object": "general-ledger/journal-entry",
"fields": [
"key",
"id",
"postingDate",
"href"
],
"orderBy": [
{
"postingDate": "asc"
}
]
}The response returns the key, id, postingDate, and href for all the journal-entry objects, sorted by postingDate in ascending order:
{
"ia::result": [
{
"key": "433",
"id": "433",
"postingDate": "2025-01-14",
"href": "/objects/general-ledger/journal-entry/433"
},
{
"key": "434",
"id": "434",
"postingDate": "2025-02-02",
"href": "/objects/general-ledger/journal-entry/434"
}
],
"ia::meta": {
"totalCount": 2,
"start": 1,
"pageSize": 100,
"next": null,
"previous": null
}
}Order by multiple fields
You can use orderBy to sort by multiple fields, for example sorting in ascending order by date, then in descending order by amount.
In this query example, the invoice object is ordered ascending by the date posted to the general ledger and descending by the total transaction amount for each date posted:
{
"object": "accounts-receivable/invoice",
"fields": [
"key",
"id",
"invoiceSummary.glPostingDate",
"totalTxnAmount",
"href"
],
"orderBy": [
{
"invoiceSummary.glPostingDate": "asc"
},
{
"totalTxnAmount": "desc"
}
]
}The response shows the invoice objects, with the invoiceSummary.glPostingDate in ascending order and the totalTxnAmount in descending order for each posting date:
{
"ia::result": [
{
"key": "230",
"id": "230",
"invoiceSummary.glPostingDate": "2016-01-01",
"totalTxnAmount": "131493.57",
"href": "/objects/accounts-receivable/invoice/230"
},
{
"key": "170",
"id": "170",
"invoiceSummary.glPostingDate": "2016-01-01",
"totalTxnAmount": "48890.06",
"href": "/objects/accounts-receivable/invoice/170"
},
{
"key": "20",
"id": "20",
"invoiceSummary.glPostingDate": "2016-11-11",
"totalTxnAmount": "127319.19",
"href": "/objects/accounts-receivable/invoice/20"
},
{
"key": "38",
"id": "38",
"invoiceSummary.glPostingDate": "2016-11-11",
"totalTxnAmount": "58441.60",
"href": "/objects/accounts-receivable/invoice/38"
},
{
"key": "29",
"id": "29",
"invoiceSummary.glPostingDate": "2016-11-11",
"totalTxnAmount": "10436.00",
"href": "/objects/accounts-receivable/invoice/29"
},
{
"key": "172",
"id": "172",
"invoiceSummary.glPostingDate": "2017-01-01",
"totalTxnAmount": "32215.93",
"href": "/objects/accounts-receivable/invoice/172"
},
{
"key": "178",
"id": "178",
"invoiceSummary.glPostingDate": "2017-01-01",
"totalTxnAmount": "32116.79",
"href": "/objects/accounts-receivable/invoice/178"
},
{
"key": "163",
"id": "163",
"invoiceSummary.glPostingDate": "2017-01-01",
"totalTxnAmount": "3756.96",
"href": "/objects/accounts-receivable/invoice/163"
}
],
"ia::meta": {
"totalCount": 8,
"start": 1,
"pageSize": 10,
"next": null,
"previous": null
}
}Pagination filter parameters
Pagination filtering allows you to retrieve large datasets in manageable chunks by specifying the starting record and the number of records to return in each query response.
start
Specify the first result to return, useful for skipping objects in queries.
This query request returns the account-group objects, in ascending order, starting at record number 10:
{
"object": "general-ledger/account-group",
"fields": [
"key",
"id",
"href"
],
"orderBy": [
{
"key": "asc"
}
],
"start": 10
}The response shows the account-group objects, with the key field in ascending order, starting at record number 10:
{
"ia::result": [
{
"key": "10",
"id": "Accounts Receivable",
"href": "/objects/general-ledger/account-group/10"
},
{
"key": "11",
"id": "Inter Entity Receivable",
"href": "/objects/general-ledger/account-group/11"
},
{
"key": "12",
"id": "Inter Entity Receivable - ADV",
"href": "/objects/general-ledger/account-group/12"
}
],
"ia::meta": {
"totalCount": 3,
"start": 10,
"pageSize": 100,
"next": null,
"previous": 1
}
}size
Limit the maximum number of results to return per page.
This query request request returns the account-group objects, in ascending order, starting at record number 10 and displaying 5 results per page:
{
"object": "general-ledger/account-group",
"fields": [
"key",
"id",
"href"
],
"orderBy": [
{
"key": "asc"
}
],
"start": 10,
"size": 5
}The response returns the account-group objects, in ascending order, displaying "start": 10 indicating the result set begins at record number 10 and "pageSize": 5 indicating the page size has 5 records:
{
"ia::result": [
{
"key": "10",
"id": "Accounts Receivable",
"href": "/objects/general-ledger/account-group/10"
},
{
"key": "11",
"id": "Inter Entity Receivable",
"href": "/objects/general-ledger/account-group/11"
},
{
"key": "12",
"id": "Inter Entity Receivable - ADV",
"href": "/objects/general-ledger/account-group/12"
},
{
"key": "13",
"id": "Other Receivable",
"href": "/objects/general-ledger/account-group/13"
},
{
"key": "14",
"id": "Other Current Assets",
"href": "/objects/general-ledger/account-group/14"
}
],
"ia::meta": {
"totalCount": 164,
"start": 10,
"pageSize": 5,
"next": 15,
"previous": 5
}
}Query filters
The filters array defines filter conditions that can be applied to a query request.
Single filter condition
Using the filters array, you can apply a single filter condition to a query request by specifying the target field, an operator, and a corresponding value.
The following query request returns all locations whose parent location is California using the $eq (equals to) operator:
{
"object": "company-config/location",
"fields": [
"id",
"name",
"status",
"href"
],
"filters": [
{
"$eq": {
"parent.id": "CA"
}
}
]
}The response returns all location objects where the parent location is CA (California):
{
"ia::result": [
{
"id": "LAS",
"name": "Los Angeles",
"status": "active",
"href": "/objects/company-config/location/60"
},
{
"id": "SDI",
"name": "San Diego",
"status": "active",
"href": "/objects/company-config/location/61"
},
{
"id": "SFO",
"name": "San Francisco",
"status": "inactive",
"href": "/objects/company-config/location/62"
},
{
"id": "SNJ",
"name": "San Jose",
"status": "inactive",
"href": "/objects/company-config/location/66"
}
],
"ia::meta": {
"totalCount": 4,
"start": 1,
"pageSize": 100,
"next": null,
"previous": null
}
}Multiple filter conditions
The filters array can also be used to define multiple filter conditions in a query request. Each condition in the array represents a discrete logical statement. You can reference these conditions in a filter expression using 1-based array indexing to construct complex logic using the and and or operators. Parentheses are also supported to group conditions and control evaluation order.
The example below returns only active locations whose parent location is California:
{
"object": "company-config/location",
"fields": [
"id",
"name",
"status",
"parent.id",
"href"
],
"filters": [
{
"$eq": {
"status": "active"
}
},
{
"$eq": {
"parent.id": "CA"
}
}
]
}The response returns all location objects where the status is active, and the parent location is CA (California):
{
"ia::result": [
{
"id": "LAS",
"name": "Los Angeles",
"status": "active",
"parent.id": "CA",
"href": "/objects/company-config/location/60"
},
{
"id": "SDI",
"name": "San Diego",
"status": "active",
"parent.id": "CA",
"href": "/objects/company-config/location/61"
}
],
"ia::meta": {
"totalCount": 2,
"start": 1,
"pageSize": 100,
"next": null,
"previous": null
}
}Date range filter
The filters array can be applied to a query request to list results between two dates using the between filter.
This query retrieves order entry document lines and applies a filter based on the due date field from the related document header, returning only lines within the specified date range:
{
"object": "order-entry/document-line",
"fields": [
"key",
"id",
"documentHeader.documentType",
"documentHeader.customer.id",
"documentHeader.documentNumber",
"href"
],
"filters": [
{
"$between": {
"documentHeader.dueDate": ["2020-06-01", "2025-06-30"]
}
}
]
}The response returns the order entry document-line objects, with associated documentType, customer.id, and documentNumber for the associated contract invoices.
{
"ia::result": [
{
"key": "527",
"id": "527",
"documentHeader.documentType": "Contract Invoice",
"documentHeader.customer.id": "001",
"documentHeader.documentNumber": "CONIN#234",
"href": "/objects/order-entry/document-line::Contract%20Invoice/527"
},
{
"key": "528",
"id": "528",
"documentHeader.documentType": "Contract Invoice",
"documentHeader.customer.id": "001",
"documentHeader.documentNumber": "CONIN#235",
"href": "/objects/order-entry/document-line::Contract%20Invoice/528"
},
{
"key": "598",
"id": "598",
"documentHeader.documentType": "Contract Invoice",
"documentHeader.customer.id": "001",
"documentHeader.documentNumber": "CONIN#250",
"href": "/objects/order-entry/document-line::Contract%20Invoice/598"
}
],
"ia::meta": {
"totalCount": 3,
"start": 1,
"pageSize": 100,
"next": null,
"previous": null
}
}Date macros
Date macros allow you to apply dynamic, relative date filters in your query requests without specifying exact calendar dates. These macros can be used with the asOfDate filter parameter.
The following example uses the currentYear date macro to find all employees who started in the current year:
{
"object": "company-config/employee",
"fields": [
"primaryContact.lastName",
"primaryContact.firstName",
"primaryContact.id",
"startDate",
"href"
],
"filters": [
{
"$eq": {
"startDate": "currentYear"
}
}
]
}The response returns the firstName, lastName, and the id for the primary contact for the employee objects, where the startDate is the currentYear:
{
"ia::result": [
{
"primaryContact.lastName": "Reyes",
"primaryContact.firstName": "Anthony C.",
"primaryContact.id": "Reyes",
"startDate": "2025-01-08",
"href": "/objects/company-config/employee/29"
},
{
"primaryContact.lastName": "Reser",
"primaryContact.firstName": "Wanda E.",
"primaryContact.id": "Reser",
"startDate": "2025-04-15",
"href": "/objects/company-config/employee/1"
},
{
"primaryContact.lastName": "Smith",
"primaryContact.firstName": "John",
"primaryContact.id": "Smith, John",
"startDate": "2025-07-29",
"href": "/objects/company-config/employee/43"
}
],
"ia::meta": {
"totalCount": 3,
"start": 1,
"pageSize": 100,
"next": null,
"previous": null
}
}The following are supported date macros:
- today
- yesterday
- currentWeek
- lastWeek
- currentMonth
- priorMonth
- currentQuarter
- priorQuarter
- currentYear
- priorYear
Filter expressions
Use the filterExpression element to define how filter conditions are combined. This allows you to apply logical operators (and, or) to the filters array.
The query request below returns locations either in Arizona or in California:
{
"object": "company-config/location",
"fields": [
"id",
"name",
"parent.id",
"href"
],
"filters": [
{
"$eq": {
"parent.id": "AZ"
}
},
{
"$eq": {
"parent.id": "CA"
}
}
],
"filterExpression": "or"
}The response returns location objects based in either AZ (Arizona) or CA (California):
{
"ia::result": [
{
"id": "MES",
"name": "Mesa",
"parent.id": "AZ",
"href": "/objects/company-config/location/73"
},
{
"id": "PHO",
"name": "Phoenix",
"parent.id": "AZ",
"href": "/objects/company-config/location/74"
},
{
"id": "LAS",
"name": "Los Angeles",
"parent.id": "CA",
"href": "/objects/company-config/location/60"
},
{
"id": "SDI",
"name": "San Diego",
"parent.id": "CA",
"href": "/objects/company-config/location/61"
},
{
"id": "SFO",
"name": "San Francisco",
"parent.id": "CA",
"href": "/objects/company-config/location/62"
}
],
"ia::meta": {
"totalCount": 4,
"start": 1,
"pageSize": 100,
"next": null,
"previous": null
}
}The filterExpression element also uses 1-based indexing of the filters array, parentheses, and logical operators to group conditions, for example, (1 and 2) or 3. By default, conditions are combined using and, so this does not need to be specified explicitly. All logical operators must be written in lowercase.
The example below returns active locations in Arizona whose start date is the current year or any locations in California:
{
"object": "company-config/location",
"fields": [
"key",
"id",
"name",
"parent.id",
"startDate",
"href"
],
"filters": [
{
"$eq": {
"startDate": "currentYear"
}
},
{
"$eq": {
"parent.id": "AZ"
}
},
{
"$eq": {
"parent.id": "CA"
}
}
],
"filterExpression": "(1 and 2) or 3"
}The response returns location objects in AZ (Arizona) whose startDate is the currentYear or any location objects in CA (California):
{
"ia::result": [
{
"key": "60",
"id": "LAS",
"name": "Los Angeles",
"parent.id": "CA",
"startDate": null,
"href": "/objects/company-config/location/60"
},
{
"key": "61",
"id": "SDI",
"name": "San Diego",
"parent.id": "CA",
"startDate": null,
"href": "/objects/company-config/location/61"
},
{
"key": "74",
"id": "PHO",
"name": "Phoenix",
"parent.id": "AZ",
"startDate": "2025-01-25",
"href": "/objects/company-config/location/74"
}
],
"ia::meta": {
"totalCount": 3,
"start": 1,
"pageSize": 100,
"next": null,
"previous": null
}
}"$ne": {
"status": "inactive"
}
Supported operators
The following common operators can be used in filter expressions:
| Operator | Description | Example filter expression | Description of the example filter |
|---|---|---|---|
$eq |
equals | "$eq" { "startDate": "currentYear" } |
startDate is in the currentYear. |
$ne |
not equals | "$ne" { "status": "inactive" } |
status is not inactive. |
$lt |
less than | "$lt":{ "totalDue": "5000" } |
totalDue is less than 5000. |
$lte |
less than or equal to | "$lte": { "dueDate": "2025-05-31" } |
dueDate is on or before 2025-05-31 |
$gt |
greater than | "$gt": { "numberOfUnits": 5 } |
numberOfUnits is greater than 5. |
$gte |
greater than or equal to | "$gte": { "hours": "40" } |
hours is equal to or above 40. |
$in |
in array | "$in": { "state": ["posted", "approved"] } |
state is either posted or approved. |
$notIn |
not in array | "$notIn": { "category": ["internal" ","archive] } |
category is either internal or chive`. |
$between |
between | "$between": { "transactionDate": ["2025-01-01", "2025-07-27"] } |
transactionDate is between 2025-01-01 and 2025-07-27. |
$notBetween |
not between | "$notBetween": { "totalDue": ["100", "500"] } |
totalDue is not between 100 and 500. |
$contains |
contains | "$contains": { "name": "tech" } |
name contains the string tech. |
$notContains |
not contains | "$notContains": { "name": "global" } |
name does not contain the string global in the name. |
$startsWith |
starts with | "$startsWith": { "id": "CUST-" } |
id begins with CUST-. |
$notStartsWith |
not starts with | "$notStartsWith": { "email": "noreply" } |
email does not start with noreply. |
$endsWith |
ends with | "$endsWith": { "email": ".com" } |
email ends with .com. |
$notEndsWith |
not ends with | "$notEndsWith": { "name": "archive" } |
name does not end with archive. |
Filter parameters
Filter parameters modify the behavior of the query, rather than specifying data-matching conditions like standard filters. The caseSensitiveComparison, includePrivate, and asOfDate filter parameters may be specified in the filterParameters section. When filter parameters are used with a view execution, the query service overrides the corresponding filterParameters section in an existing view or adds a filterParameters section, if not already present.
asOfDate
The asOfDate filter parameter is the offset date to use when using the date macros at any point in time. If asOfDate is not set, the default is the current date.
The following example uses the asOfDate filter parameter with the priorYear date macro to list journal entries created in the prior year as of March 31st, 2025:
{
"object": "general-ledger/journal-entry",
"fields": [
"id",
"key",
"postingDate",
"description",
"href"
],
"filters": [
{
"$gt": {
"audit.createdDateTime": "priorYear"
}
}
],
"filterParameters": {
"asOfDate": "2025-03-31"
}
}The response returns journal-entry objects created in the priorYear, as of 2025-03-31:
{
"ia::result": [
{
"id": "30368",
"key": "30368",
"postingDate": "2024-12-31",
"description": "Daily 1 created on December 31st 2024",
"href": "/objects/general-ledger/journal-entry/30368"
},
{
"id": "30371",
"key": "30371",
"postingDate": "2025-01-31",
"description": "Daily 1 created on January 31st 2025",
"href": "/objects/general-ledger/journal-entry/30371"
},
{
"id": "30373",
"key": "30373",
"postingDate": "2025-02-28",
"description": "Daily 1 created on February 28th 2025",
"href": "/objects/general-ledger/journal-entry/30373"
}
],
"ia::meta": {
"totalCount": 3,
"start": 1,
"pageSize": 100,
"next": null,
"previous": null
}
}caseSensitiveComparison
The caseSensitiveComparison filter parameter ensures that letter casing is evaluated in a query request. This filter parameter is true by default, meaning that only exact matches in letter casing will be returned. To extend a query to include matches regardless of case, set this parameter to false.
The following example retrieves employees with Admin as their job title, and includes matches regardless of case, for example Admin, admin, and ADMIN:
{
"object": "company-config/employee",
"fields": [
"key",
"id",
"earningType.id",
"href"
],
"filters": [
{
"$eq": {
"earningType.id": "Salaries"
}
}
],
"filterParameters": {
"caseSensitiveComparison": false
}
}The response returns employee objects where the earningType.id matches regardless of case, Salaries, salaries, and SALARIES.
{
"ia::result": [
{
"key": "13",
"id": "EM M",
"name": "Aaron",
"earningType.id": "Salaries",
"href": "/objects/company-config/employee/13"
},
{
"key": "31",
"id": "23",
"name": "Jurasek",
"earningType.id": "salaries",
"href": "/objects/company-config/employee/31"
},
{
"key": "42",
"id": "001",
"name": "Kim, Gabriel",
"earningType.id": "SALARIES",
"href": "/objects/company-config/employee/42"
}
],
"ia::meta": {
"totalCount": 3,
"start": 1,
"pageSize": 100,
"next": null,
"previous": null
}
}includePrivate
The includePrivate filter parameter lets you access private objects from other entities in the query (false by default). It is only available at the top company level and respects user permissions. See Include private records from other entities in a list for more information.
This example includes all journal entries where there is a value in the entity identifier, across all entities:
{
"object": "general-ledger/journal-entry",
"fields": [
"key",
"id",
"entity.id",
"entity.name",
"glJournal.id",
"href"
],
"filters": [
{
"$ne": {
"entity.id": "null"
}
}
],
"filterParameters": {
"includePrivate": true
}
}The response returns journal-entry objects in different entities, where the entity.id is not equal to null.
{
"ia::result": [
{
"key": "12300",
"id": "12300",
"entity.id": "1",
"entity.name": "United States of America",
"glJournal.id": "APJ",
"href": "/objects/general-ledger/journal-entry/12300"
},
{
"key": "5735",
"id": "5735",
"entity.id": "2",
"entity.name": "India",
"glJournal.id": "ASC606REVREC",
"href": "/objects/general-ledger/journal-entry/5735"
},
{
"key": "5736",
"id": "5736",
"entity.id": "3",
"entity.name": "United Kingdom",
"glJournal.id": "OpDBB",
"href": "/objects/general-ledger/journal-entry/5736"
}
],
"ia::meta": {
"totalCount": 3,
"start": 1,
"pageSize": 100,
"next": null,
"previous": null
}
}Aggregate functions
Aggregate functions are specified inside the fields element as <function>:<dot.separated.fieldName>. Supported functions are count, avg, sum, min, and max. If at least one aggregate function is present, all non-aggregate fields are treated as an implicit groupBy for the aggregation and response.
sum
Returns the total of a numeric field across all matching objects.
The following query retrieves Accounts Payable (AP) bills, displaying the total amount due for all vendors that have not been paid:
{
"object": "accounts-payable/bill",
"fields": [
"sum:vendor.vendorDue"
],
"filters":[
{"$ne": {
"state": "paid"
}}
],
"orderBy": [
{
"sum:vendor.vendorDue": "desc"
}
]
}The response returns a single result with the total (sum) amount due for all vendors where the state is not equal to paid:
{
"ia::result": [
{
"sum:vendor.vendorDue": "54395.02"
}
],
"ia::meta": {
"totalCount": 1,
"start": 1,
"pageSize": 100,
"next": null,
"previous": null
}
}The following query finds Accounts Payable (AP) bills and uses the sum aggregate field to calculate the total amount due. It groups the aggregation by the non-aggregate field vendor.name so the response displays the total amount due for each vendor that have not been paid:
{
"object": "accounts-payable/bill",
"fields": [
"vendor.name",
"sum:vendor.vendorDue"
],
"filters":[
{"$ne": {
"state": "paid"
}}
],
"orderBy": [
{
"sum:vendor.vendorDue": "desc"
}
]
}The response returns multiple results with the total (sum) amount due for each vendor where the state is not equal to paid:
{
"ia::result": [
{
"vendor.name": "PG & E",
"sum:vendor.vendorDue": "21200.00"
},
{
"vendor.name": "UTC testing",
"sum:vendor.vendorDue": "17589.00"
},
{
"vendor.name": "1099 Int",
"sum:vendor.vendorDue": "13682.30"
},
{
"vendor.name": "1099 IntCA",
"sum:vendor.vendorDue": "1923.72"
}
],
"ia::meta": {
"totalCount": 4,
"start": 1,
"pageSize": 100,
"next": null,
"previous": null
}
}min
Finds the smallest or earliest value in a specified field.
Use this query to find the smallest credit limit for vendors that have a credit limit applied:
{
"object": "accounts-payable/vendor",
"fields": [
"min:creditLimit"
],
"filters": [
{
"$ne": {
"creditLimit": null
}
}
],
"filterParameters": {
"includePrivate": true
},
"orderBy": [
{
"min:creditLimit": "asc"
}
],
"size": 1
}The response returns the minimum (min) credit limit for vendors where the creditLimit is not equal to null:
{
"ia::result": [
{
"min:creditLimit": 10000
}
],
"ia::meta": {
"totalCount": 1,
"start": 1,
"pageSize": 1,
"next": null,
"previous": null
}
}max
Finds the largest or most recent value in a specified field.
Use this query to find the invoice with the largest amount due:
{
"object": "accounts-receivable/invoice",
"fields": [
"max:totalTxnAmountDue"
],
"filters":[
{"$ne": {
"state": "paid"
}}
],
"orderBy": [
{
"max:totalTxnAmountDue": "desc"
}
]
}The response returns the maximum (max) amount in the totalTxnAmountDue field for all invoices where the state is not equal to paid:
{
"ia::result": [
{
"max:totalTxnAmountDue": "9275705.52"
}
],
"ia::meta": {
"totalCount": 1,
"start": 1,
"pageSize": 100,
"next": null,
"previous": null
}
}count
Returns the total number of objects that meet the filter criteria.
Use this query to count the number of vendors and their preferred payment method:
{
"object": "accounts-payable/vendor",
"fields": [
"count:key",
"preferredPaymentMethod"
],
"filters": [
{
"$ne": {
"preferredPaymentMethod": null
}
}
],
"orderBy": [
{
"preferredPaymentMethod": "desc"
}
]
}The response returns the count of vendor objects where preferredPaymentMethod is not equal to null:
{
"ia::result": [
{
"count:key": "3",
"preferredPaymentMethod": "cash"
}
],
"ia::meta": {
"totalCount": 1,
"start": 1,
"pageSize": 100,
"next": null,
"previous": null
}
}Combining functions
Applies multiple aggregate functions, such as sum, avg, min, max, and count to be used together in a single query.
This query example uses an array to find details of unpaid invoices due between two dates, the first query in the array identifies invoices in the time period and the second query in the array lists the specific invoice details:
[
{
"object": "accounts-receivable/invoice",
"fields": [
"customer.name",
"sum:customer.customerDue",
"sum:totalTxnAmountDue",
"min:totalTxnAmountDue",
"max:totalTxnAmountDue",
"min:invoiceDate",
"max:invoiceDate"
],
"filters":[
{"$ne": {
"state": "paid"
}},
{"$between": {
"dueDate": ["2020-06-01", "2020-06-30"]
}}
],
"orderBy": [
{
"customer.name": "desc"
}
]
},
{
"object": "accounts-receivable/invoice",
"fields": [
"customer.name",
"invoiceNumber",
"invoiceDate",
"totalTxnAmountDue"
],
"filters":[
{"$ne": {
"state": "paid"
}},
{"$between": {
"dueDate": ["2020-06-01", "2020-06-30"]
}}
],
"orderBy": [
{
"invoiceNumber": "asc"
}
]
}
]The first result in the response indicates multiple invoices in the specified time period, displaying a difference between the earliest invoiceDate and the latest invoiceDate. It also shows a difference between the min:totalTxnAmountDue and the max:totalTxnAmountDue. The second result in the response displays specific details of each invoice in the time period:
{
"ia::result": [
{
"ia::result": [
{
"customer.name": "Sonic Corporation",
"sum:customer.customerDue": "6452675.86",
"sum:totalTxnAmountDue": "780609.05",
"min:totalTxnAmountDue": "31308.00",
"max:totalTxnAmountDue": "749301.05",
"min:invoiceDate": "2020-06-07",
"max:invoiceDate": "2020-06-30"
}
],
"ia::meta": {
"totalCount": 1,
"start": 1,
"pageSize": 100,
"next": null,
"previous": null
}
},
{
"ia::result": [
{
"customer.name": "Sonic Corporation",
"invoiceNumber": "SONIC#234",
"invoiceDate": "2020-06-07",
"totalTxnAmountDue": "31308.00"
},
{
"customer.name": "Sonic Corporation",
"invoiceNumber": "SONIC#235",
"invoiceDate": "2020-06-30",
"totalTxnAmountDue": "749301.05"
}
],
"ia::meta": {
"totalCount": 2,
"start": 1,
"pageSize": 100,
"next": null,
"previous": null
}
}
],
"ia::meta": {
"totalCount": 2,
"totalSuccess": 2,
"totalError": 0
}
}Constructing a complete query
Use a complete query structure to retrieve exactly the information you need by combining multiple elements, such as field selection, filter conditions, sorting and pagination. This section provides an example that shows how to bring all these components together in a single request.
The following example query uses a filter to find all credit card transactions that are paid, and then displays the results in ascending order:
{
"object": "cash-management/credit-card-txn",
"fields": [
"key",
"id",
"whenPaid",
"totalPaid",
"href"
],
"filters": [
{
"$eq": {
"state": "paid"
}
}
],
"orderBy": [
{
"id": "asc"
}
]
}The response returns all credit card transactions, displaying the key, id, whenPaid, totalPaid, and href fields included for each object,and sorting the results by id in ascending order:
{
"ia::result": [
{
"key": "1092",
"id": "1092",
"whenPaid": "2023-12-15",
"totalPaid": "100.00",
"href": "/objects/cash-management/credit-card-txn/1092"
},
{
"key": "1148",
"id": "1148",
"whenPaid": "2024-01-01",
"totalPaid": "500.00",
"href": "/objects/cash-management/credit-card-txn/1148"
},
{
"key": "1330",
"id": "1330",
"whenPaid": "2024-02-15",
"totalPaid": "1000.00",
"href": "/objects/cash-management/credit-card-txn/1330"
},
{
"key": "1332",
"id": "1332",
"whenPaid": "2024-04-30",
"totalPaid": "2000.00",
"href": "/objects/cash-management/credit-card-txn/1332"
}
],
"ia::meta": {
"totalCount": 4,
"start": 1,
"pageSize": 100,
"next": null,
"previous": null
}
}Navigating large datasets
When retrieving large sets of objects using the Sage Intacct REST API, it's important to implement iteration to ensure reliable and complete data retrieval. The query endpoint supports a batching mechanism that enables you to fetch results incrementally using the start, size, and next parameters. See pagination filtering for request and response examples using the start and size filter parameters.
Record limit per request
The maximum number of records that can be returned in a single response is 4,000. If your dataset exceeds this limit, the query response will contain a next field that indicates that more results are available. If next is greater than 0, update the start value in your next query request to match the next value. Continue this process to fetch subsequent batches of results until next is null, which means all results have been retrieved.
Example workflow
-
Make an initial query request with a
startvalue (typically 1) and asizevalue up to 4000. -
Inspect the
nextvalue in the response. -
If
nextis not null, setstartin your next request to thenextvalue and reissue the query. -
Repeat the process until
nextis null.
Troubleshooting and FAQs
-
Does the query service support both single requests and collections of requests?
Yes, the query service supports both. You can submit a single query request, or a collection of requests (array), including dynamic queries. This allows you to execute multiple queries in a singe call.
-
Can I use
hreffields infilterororderByclauses in the query service?No, the query service does not support using
hreffields infilterororderByclauses. Thehreffield is not valid for filtering or sorting within query requests. -
Can I use an asterisk (
*) to list all fields in the query service?No, the query service does not support the use of an asterisk (
*) to select all fields. You must explicitly specify each field you want to include in the query response. -
Can I query related collections of objects in the query service?
No, the query service does not support querying related collections of objects. For example, you cannot query journal entry lines for a specific journal entry. However, you can query journal entries and include or retrieve their associated lines.
-
Why might certain fields not be queryable in the query service?
A field may not be queryable for several reasons:
-
It is marked with
writeOnly: truein the model, meaning it is not intended for retrieval. - It returns a list of data, which is not supported for querying.
-
It is marked as
deprecatedand therefore its availability and support are limited.
If a field does not fall into these three categories, but still cannot be queried, you should open a defect described in the what support is available for developers section of the Frequently asked questions.
-
It is marked with
-
Can I query owned (child) objects by starting from the parent object in the query service?
No, the query service does not support query in owned (child) objects by navigating from the parent. You can query the owning (parent) object for a list of the owned (child) objects. To retrieve an owned (child) object, you must start the query from the owned (child) object itself and, if needed, include fields from the owning (parent) object. Creating a separate query for the owned (child) object is the correct approach.