Query API - An Overview

COQL, abbreviated for CRM Object Query Language, allows users to write SQL-like queries to fetch records from Zoho CRM. Like SQL that uses table names, Query API uses module and field API names in the query.
While Search API allows module-specific search, Query API allows you to retrieve records from cross-modules that are linked via lookup fields, thereby establishing joins.

Table of Contents

Syntax and Supported Clauses

Query API supports the SELECT query and the following clauses that you can use to apply conditions and retrieve records based on them from a module. In the SELECT column, you must use field API name as column names.

• SELECT - This statement is to select the fields you want to receive in the response. You can specify up to 50 field API names in the SELECT column of the query. You can use the Field Metadata API to get the API name of a field in a module, or go to Setup > Developer Hub > APIs and SDKs, and click the API Names tab. Choose the required module from the list of available modules and look for the API name of the desired field.
• WHERE - This clause is to specify the condition or criteria to filter and fetch records. You can include up to 25 criteria in this clause. Ensure to enclose the criteria in brackets properly to achieve the desired results.
• FROM - This clause is to specify the module's API name from which you want to retrieve records. To retrieve records from a Subform, specify the Subform module's API name. Similarly, for queries with multi-select lookup fields, query to the linking module.
• ORDER BY - Use this clause to sort the records in the response based on specific fields either in ascending or descending order. The default sorting is based on record ID in the ascending order.
• LIMIT and OFFSET - The LIMIT clause is to specify the number of matching records you want to receive in the response. The default value is 200 and the max value is 2000.
  OFFSET is to paginate through records. For example, if you have set this value as 10, then the system skips the first ten matching records, and retrieves the others from the 11th index.
  In COQL, the syntax is LIMIT offset, limit. The first value is the offset, while the next one is the limit.
• GROUP BY - Use this clause to retrieve data from a table by specifying the field API names as conditions in a query and group them. It helps you filter and organize the records efficiently, and group the results of a query based on one or more columns as objects in the response. In general, you can use this clause with both aggregate and non-aggregate functions.

Syntax

Here is the syntax of a COQL query.

SELECT field_api_name1, field_api_name2
    FROM base_module_api_name
    WHERE field_api_name comparator logical_operator field_api_name comparator
    ORDER BY field_api_name ASC/DESC
    LIMIT offset, limit

Here is a sample SQL query and it's equivalent COQL query. In the query, we want to fetch the Last_Name, Created_Time fields from the Leads module. The WHERE clause has the condition. This query sorts the records in the response based on Created_Time and record ID in the ascending order as specified in the ORDER BY clause. The offset is 10 and the limit is 2, meaning that the first 10 matching records are skipped, and the next two records are retrieved in the response. 
 

SELECT Last_Name, Created_Time
    FROM Leads
    WHERE Last_Name IS NOT NULL 
      AND Created_Time >= '2019-02-19T12:57:55+05:30'
    GROUP BY Last_Name, Created_Time
    ORDER BY Created_Time, id ASC
    LIMIT 10 OFFSET 2;

Equivalent COQL query

{
        "select_query" : "select Last_Name, Created_Time from Leads where (Last_Name is not null and Created_Time >= '2019-02-19T12:57:55+05:30')  group by Last_Name, Created_Time order by Created_Time, id asc limit 10, 2"
    }

Response

{
        "data": [
            {
                "Last_Name": "Smith",
                "Created_Time": "2019-11-26T12:09:48+05:30"  
            },
            {
                "Last_Name": "Gerald",
                "Created_Time": "2019-11-26T12:10:43+05:30"
            }
        ],
        "info": {
            "count": 2,
            "more_records": true
        }
    }

Fields and Criteria

The main aim of querying for records is to filter them based on some criteria. Query API lets you apply multiple criteria to various fields in the WHERE clause. When the criteria are satisfied, the matching records are rendered in the response.

Consider this simple query.

{
        "select_query":"select Last_Name, Company, Lead_Status from Leads where (Lead_Status = Contacted and Last_Name is not null) limit 2 "
    }
    }

In this query, Last_Name, Company, and Lead_Status are the API names of the fields in the Leads module that we want to be retrieved in the response.
The WHERE clause contains the condition that the Lead_Status must have the value Contacted and the Last_Name must not be null. The = and is not null are the comparators for the respective fields. and is the group operator applied over the criteria.

Like this, you can have a maximum of 25 criteria in your WHERE clause. Meaning, if your WHERE clause looks like ((A and B) and C)), it means there are three conditions/criteria applied over three fields or the same fields. This way, you can have up to 25 criteria applied over different fields or same field in the WHERE clause.

Every field type(data type) can be used with its own set of comparators. Refer to the Get Records through a COQL query page for details and examples about each field type.

Joins and Alias Support in COQL Queries

The SQL JOIN clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.
In COQL, you can establish a join or a relation with the help of lookup fields that relate one module with the other using the .(dot) operator.

Consider that you want to establish a join between two tables - Contacts and Accounts. 
In Contacts, Account_Name is a lookup field to the Accounts module.

In Accounts, the Parent_Account field is another lookup to Accounts itself, that represents the parent account of an account.

Consider that you want to fetch records from Contacts, where the parent account of the account associated with the contact is "King".

COQL Query

{
        "select_query":"select 'Account_Name','Account_Name.Account_Name' ,'Account_Name.Parent_Account','Account_Name.Parent_Account.Account_Name' from Contacts where (Account_Name.Parent_Account.Account_Name = 'King') limit 1 "
    }
    }

Here, Account_Name will give the ID of Account that the contact is associated with, Account_Name.Account_Name will give the name of the account, Account_Name.Parent_Account will give the ID of the parent account, Account_Name.Parent_Account.Account_Name will give the account name of the parent account. As you can see, you can use the .(dot) operator to retrieve records that are joined via lookup fields.

Response

{
        "data": [
            {
                "Account_Name.Parent_Account.Account_Name": "King",
                "Account_Name.Parent_Account": {
                    "id": "3652397000000190102"
                },
                "Account_Name.Account_Name": "Zylker",
                "Account_Name": {
                    "id": "3652397000000624046"
                },
                "id": "3652397000000269089"
            }
        ],
        "info": {
            "count": 1,
            "more_records": true
        }
    }

Alias support

You can see in the above response that the key names are long. If you want to have them short or have names that make more sense to you, you can use the alias support in COQL. Use the keyword AS(case-insensitive) in your query to customize the key names in the response. Here is a sample.

{
"select_query":"select 'Account_Name' AS 'Account id','Account_Name.Account_Name' as 'Account Name','Account_Name.Parent_Account' as 'Parent Account id','Account_Name.Parent_Account.Account_Name' As 'Parent Account Name' from Contacts where (Account_Name.Parent_Account.Account_Name = 'King') limit 1"
    }

Response

{
        "data": [
            {
                "Account id": {
                    "id": "3652397000000624046"
                },
                "Account Name": "Zylker",
                "Parent Account id": {
                    "id": "3652397000000190102"
                },
                "Parent Account Name": "King",
                "id": "3652397000000269089"
            }
        ],
        "info": {
            "count": 1,
            "more_records": true
        }
    }

GROUP BY and Aggregate Functions

As discussed in the Syntax and Supported Clauses section, GROUP BY clause is to filter and organize the records efficiently, and group the results of a query based on one or more columns as objects in the response. You can use the GROUP BY clause on both aggregate and non-aggregate functions.

Aggregate Functions

The advantage of using aggregate functions in the COQL API in Zoho CRM is that they allow for powerful data analyses directly within CRM. Aggregate functions like SUM(), AVG(), COUNT(), MIN(), and MAX() enable you to perform calculations on data, such as finding totals, averages, and other statistical measures.
This capability helps in generating insights and reports without the need to export data to external tools, thus improving efficiency and decision-making processes within the CRM environment. 
Refer to the Get Records through a COQL query page for details and examples about each aggregate function.

Pagination

With Query API, you can retrieve up to 2000 records per API call using LIMIT 0, 2000. This fetches the first 2000 records that match the criteria in the WHERE clause. 
When you want to fetch more records, you can use OFFSET with the LIMIT clause. For example, LIMIT 2000, 1500 will fetch the 1500 records from the 2001th index, that is, it skips the records in the index 1-2000.
With the same criteria, you can paginate up to 10,000 records. For the next set of records, add another criteria id > {last_record_id} with the "AND" operator, and go on to fetch the next 10,000 records. If you have custom sorting in your query, adjust the criteria accordingly. In the following example, sorting is done based on Created_Time and ID fields.

Query 1

{
 "select_query" : "select Last_Name, Created_Time from Leads where Last_Name is not null order by Created_Time, id asc limit 0, 200"
    }

When you use the above query and change the value of OFFSET(at 8000), you can retrieve records from the 8001-10000th index. 
Note down the created time and ID of the last record in the response. To fetch records more than the previously fetched 10000, you must construct a query to retrieve records that were created after the 10000th record and whose record ID is greater than that of the 10000th record's.

Query 2

{
"select_query" : "select Last_Name, Created_Time from Leads where ((Last_Name is not null and Created_Time > '2019-11-26T12:14:52+05:30') and id > 3652397000001257649) order by Created_Time, id asc limit 0, 10"
}