Data queries

The query operation is the method for creating a guided query against an entity. The select statement in the request enables you to specify the selection criteria, entity properties, sort order, and pagination. It is similar to a pared down SQL query select statement with constraints in place to ensure the request doesn’t overload server-side resources.  As such, the following are query operation limitations:

  • The response set always returns all properties for each object. That is, projections are not supported.
  • Only those properties with values are returned in the response set.
  • Wild card character support with LIKE clauses is limited to “%” (wildcard that substitutes for 0 or more characters).
  • The OR operation is not supported in WHERE clauses.
  • The GROUP BY clause is not supported.
  • The JOIN clause is not supported.

URI for query

The query operation supports the GET method. The select statement is specified as a URI parameter.

HTTP MethodURI Format and Example
GET

https://quickbooks.api.intuit.com/v3/company/<realmID>/query?query=select_statement
 

https://quickbooks.api.intuit.com/v3/company/1234/query?query=SELECT%20FROM%20Customer%20WHERE%20Metadata.LastUpdatedTime
%20%3E+%272011-08-10T10%3A20%3A30-0700%27

Response body for query

A successful response body contains a <QueryResponse> element corresponding to the SELECT statement in the request.  The <QueryResponse> element contains the entities that match the selection criteria of the select statement in the request. The <QueryResponse> element can include the following attributes:

  • startposition—The starting count of the response for pagination.
  • maxresults—The number of entity elements in the <QueryResponse> element.
  • sparse—Set to true if not all properties of the entity are in the response.

Here's an example of a successful query response body:

<IntuitResponse  xmlns="http://schema.intuit.com/finance/v3" time="2013-04-03T10:22:55.766Z">
    <QueryResponse startPosition="10" maxResults="2">
        <Customer>
            <Id>2123</Id>
            <SyncToken>0</SyncToken>
            ...
            <GivenName>Srini</GivenName>
        </Customer>
        <Customer>
            <Id>2124</Id>
            <SyncToken>0</SyncToken>
            ...
            <GivenName>Peter</GivenName>
        </Customer>
    </QueryResponse>
</IntuitResponse>

If the query operation detects an error, then the <QueryResponse> element contains the <Fault> element, for example:

<IntuitResponse xmlns="http://schema.intuit.com/finance/v3" time="2013-04-03T10:22:55.766Z">
   <QueryResponse>
      <Fault type="Validation">
        <Error code=”100” element="query">
            <Message>OBJECT name not available </Message>
        </Error>
        <Error code="300" element="query">
            <Message>Time value is incorrect</Message>
        </Error>
      </Fault>
  </QueryResponse>
</IntuitResponse>

Maximum number of entities in a response

The maximum number of entities that can be returned in a response is 1000.  If the result size is not specified, the default number is 100. If a query returns many entities, fetch the entities in chunks, as described in Pagination.  To determine the number of entities that a particular query returns, probe by using the COUNT keyword in the query. See Count for details.

Multi-object read

Retrieve multiple objects with one request via the query endpoint.

  • Query support is available with most resources.  Refer to individual resource documentation in the the QuickBooks Online API Reference for further information about query support.

Query language operations

    Case

    In the query language, reserved words, entity names, and property names are case insensitive. Additionally, property values are case insensitive. For example, although the following two statements use different cases, they are equivalent:

    SELECT * FROM Customer WHERE GivenName = 'greg' STARTPOSITION  10
    SELECT * from cUstomer where givenname = 'Greg' Startposition 10

    Escape Character

    Use backslash (\) to escape special characters like apostrophe (').  For example, to find a Customer object whose company name is Adam's Candy Shop, submit the following query:

    SELECT * FROM Customer WHERE CompanyName= ‘Adam\’s Candy Shop’

    Filters

    The filter in the WHERE clause determines which entities are returned by a query.  The filter criteria is based on the value of a property.  For example, the following query returns all Invoice entities with a TotalAmt property greater than 1000.0:

    SELECT * FROM Invoice WHERE TotalAmt > '1000.0'

    Note that not all properties support filters.  To see which properties support filters, go to the entity's reference page to check filtering support for a particular property.

    Multiple filters are AND'd together. Note that the OR operation is not supported. The next query returns all Invoice entities created during the specified time range:

    SELECT * FROM Invoice WHERE MetaData.CreateTime >= '2009-10-14T04:05:05-07:00' AND MetaData.CreateTime <= '2012-10-14T04:05:05-07:00'
    

    The following statement has two AND operators:

    SELECT * FROM Invoice WHERE id in ('64523', '18761', '35767') AND MetaData.CreateTime >= '1990-12-12T12:50:30Z' AND MetaData.LastUpdatedTime <='1990-12-12T12:50:30Z'

    If the WHERE clause is omitted, all entities, up to the maximum number, are returned. If a query returns many entities, you should fetch the entities in chunks, as described in Pagination.

    More filter examples

    Get all invoices for a given customer. Filter on the CustomerRef attribute from the invoice:

    SELECT * FROM Invoice WHERE CustomerRef = '123'

    Get all active and inactive customers (only list entities like Customer, Vendor, Item, and so on, support concept of Active and InActive)

    SELECT * FROM Customer WHERE Active IN (true, false)

    Get all active Customer entities:

    SELECT * FROM Customer WHERE active = true

    Get the Customer entity for the specified identifier:

    SELECT * FROM Customer WHERE Id = '123456'

    Get all properties for the Customer entities whose first name starts with "K" and ends with "h", and whose last name starts with "Palm".

    SELECT * FROM Customer WHERE GivenName LIKE 'K%h' AND FamilyName LIKE 'Palm%'

    Get the header properties for the Invoice entities whose CustomerId properties match the values of the IN clause.

    SELECT * FROM Invoice WHERE CustomerId IN ('12')

    Get all properties for the Invoice entities with transaction dates between Jan. 1, 2011 and the current date.

    SELECT * FROM Invoice WHERE TxnDate > '2011-01-01' AND TxnDate <= CURRENT_DATE

    Sorting

    To sort the results of a query, include the ORDERBY clause, specifying a property as the sort field.  Note that not all properties can be used for sorting.  To see which properties can be sorted on, go to the entity's reference page to check sorting support for a particular property.

    For example, the following query sorts Customer objects by the FamilyName property:

    SELECT * FROM Customer ORDERBY FamilyName

    The default sort order is ascending.  To indicate descending sort order, include DESC, for example:

    SELECT * FROM Customer ORDERBY FamilyName DESC

    Pagination

    To page through the results, specify STARTPOSITION (position of the entity in the query results) and MAXRESULTS (maximum number of entities in the result).

    For example, suppose you have 25  invoices.  The following query gets invoices 1 - 10:

    SELECT * FROM Invoice STARTPOSITION 1 MAXRESULTS 10
    

    The next statement gets invoices 11 - 20:

    SELECT * FROM Invoice STARTPOSITION 11 MAXRESULTS 10
    

    The following query gets invoices 21 - 25:

    SELECT * FROM Invoice STARTPOSITION 21 MAXRESULTS 10

    Count

    To find out how many object will be returned by a query, specify the COUNT keyword.  The number of entities is returned in the totalCount attribute of the <QueryResponse> element.  The QuickBooks Online data objects, such as Customer, are not in the response.

    The following query returns the total number of customers that belong to the QuickBooks company:

    SELECT COUNT(*)FROM Customer
    

    Select statement syntax

    The following  shows the simplified syntax of a select statement. 

    Select Statement = SELECT * | count(*)
                       FROM IntuitEntity
                       [WHERE WhereClause]
                       [ORDERBY OrderByClause]
                       [STARTPOSITION  Number] [MAXRESULTS  Number]

    where:

    ItemDescription
    *All fields are returned. Projections are not supported.
    count(*)The number of records that satisfy the query criteria, returns a single value.
    IntuitEntity = Customer | Vendor | Invoice | etc.The IntuitEntity is the name of the entity.  Only one entity may be specified. Case sensitive.
    WhereClause  = PropertyName
                  | Operator Value [AND WhereClause]*
    The WhereClause filters the returned data according to the value of a property. 
    Note: Multiple clauses (filters) are AND'd.  The OR operation is not supported.
         Operator =  = | IN | < | > | <=| >= | LIKEWild card character support with LIKE clauses is limited to "%" (Wildcard that substitutes for zero or more characters).

         Value = (Value [,Value]) 
                  | 'value_in_quote'
                  | value_without_quote 
                  | true|false
                  | ' '

    This construct is supported only with IN clauses. 
    Null is represented as the string,  ' '. That is, a space within single quotes. 
    Expression evaluation is not supported.

    OrderByClause = PropertyName [ASC | DESC]
                        [, OrderByClause]*
    The OrderByClause is for sorting the result
    NumberA positive integer.

     Got Questions? Get Answers in our developer forums.