Query API - An Overview

CRM Object Query Language (COQL) is a query language based on the SQL syntax. It helps the users write their own queries to get records from Zoho CRM by using field API names instead of column names and module API names instead of table names.

You can use this API to filter and fetch records based on certain criteria instead of creating custom views and passing that cvid(custom view ID) as a parameter in your request. Using this API, you can retrieve 200 records per API call and a maximum of 10,000 records through multiple requests.

  • COQL supports only SELECT query with the clauses WHERE, FROM, ORDER BY, GROUP BYLIMIT, and OFFSET

  • COQL keywords are not case-sensitive. SELECT is the same as select.

  • By default, the system sorts the records in ascending order based on the record ID, if you do not include order by in the query.

  • The default value for LIMIT is 200 and OFFSET is 0. Modify the values of these properties to navigate and fetch the next set of records. You can retrieve a maximum of 10,000 records(with LIMIT=200 and OFFSET=9800) in multiple API calls.

  • Tip: Consider that you have retrieved 10,000 records, previously, and want to query for the next 10,000 records. To achieve this, in the first query, you can sort by created time in the descending order, which will give you 10,000 records. In the second query, besides your criteria, you can add Created_time < (last_record's_created_time). The created time here should be that of the 10,000th record's in the previous API call.

  • When constructing queries, it is crucial to parse special characters and SQL reserved words properly to avoid issues. They should be enclosed within quotes for proper handling. For example, the special characters like "->" used with What_Id must be enclosed within quotes to prevent errors. For instance, here is a valid query using What_Id.

    {
        "select_query": "select 'What_Id->Leads.Last_Name' from Tasks where (('What_Id->Leads.id' in ('4876876000000900134','4876678000000900987')) AND ('What_Id->Leads.Industry' != 'ERP'))"
    }

    Similarly, when using an SQL reserved keyword in the SELECT query, it should be enclosed in quotes. For instance, if you want to include an SQL reserved keyword as a field name, you should enclose it in quotes. Here's an example of the correct usage where Dynamic is a reserved SQL keyword:

    {
        "select_query": "select Last_Name, 'Dynamic' from Contacts where Last_Name is not null limit 2"
    }

Advantages of COQL

  • COQL is based on SQL syntax and is very simple to use.

  • No need to create custom views based on certain criteria and pass that cvid in the request. Instead, you can use the COQL Select query with different comparators directly in the input to fetch records that match the criteria.

  • The supported COQL comparators cover a wide range of search possibilities. For example, you can use the in comparator to search for records from a list of values which is not possible using the Search Records API.

  • The limit clause allows you to set the number of records you want to fetch. You can fetch a maximum of 10,000 records using this API.

  • Easy to join or relate two modules with the help of lookup fields using .(dot)

  • No need to specify the alias of the module explicitly when establishing a relation between two modules, as the API names are unique for every module.

How to build a COQL Query?

Use SQL Select query to build a COQL query with module_API_names as table names and field_API_names as column names.

Syntax

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

Consider the following example query

SELECT First_Name, Last_Name, Full_Name
FROM Leads
WHERE Last_Name = 'Boyle' and First_name = 'Patricia'
ORDER BY Full_Name
LIMIT 10, 2

The above query will return the record from the Leads module with the specified first name and last name, sorted based on the full name of the leads. The limit is set to 2 and offset is set to 10, meaning that the system skips the first 10 rows and fetches the records from the next two rows. The response is as follows.

{
"data": [
    {
        "First_Name": "Patricia",
        "Full_Name": "Patricia Boyle",
        "Last_Name": "Boyle",
        "id": "554023000000322019"
    }
],
"info": {
    "count": 1,
    "more_records": false
}
}

How to use Joins in COQL?

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.

Consider the following "Contacts" table where Account_Name is the lookup field that represents the account name associated with the contact.

First_NameLast_NameFull_NameAccount_Name
PatriciaBoylePatricia BoyleABC Inc
JohnSmithJohn SmithABC Inc
AliceMasonAlice MasonAll Tyres

Consider the following Accounts table.

Account_NameParent_AccountBilling_City
ABC IncZylkerNYC
All Tyres--Washington DC
Zylker--NYC

The SQL query to fetch all contacts associated with the account, ABC Inc and the billing city, NYC, sorted in ascending order based on the full name of the contact will be

SELECT First_Name, Last_Name, Full_Name
FROM Contacts AS C LEFT JOIN Accounts AS A ON (C.id = A.id)
WHERE A.Account_Name = 'ABC Inc' and A.Billing_City = 'NYC'
ORDER BY Full_Name ASC;

The COQL query for the same will be

SELECT First_Name, Last_Name, Full_Name
FROM Contacts
WHERE Account_Name.Account_Name = 'ABC Inc' and Account_Name.Billing_City = 'NYC'
ORDER BY Full_Name ASC;

In the COQL query, a relation is established with the .(dot) using the lookup field.

Consider you have an account lookup in contacts module that points to the Account_Name that the contact is associated with. The Account has a parent account. So, to fetch the parent account name of the account that is associated with the contact, use relations between the modules as below.

SELECT Last_Name, Account_Name.Parent_Account, Account_Name.Parent_Account.Account_Name
FROM Contacts
WHERE Last_Name is not null and Account_Name.Parent_Account.Account_Name is not null

In the above sample, Account_Name.Parent_Account returns the ID of the parent account and Account_Name.Parent_Account.Account_Name returns the name of the parent account. The response is as follows.

{
    "data": [
        {
            "Account_Name.Parent_Account.Account_Name": "Zylker",
            "Last_Name": "Boyle",
            "Account_Name.Parent_Account": {
                "id": "554023000000238121"
            },
            "id": "554023000000310003"
        },
        {
            "Account_Name.Parent_Account.Account_Name": "Zylker",
            "Last_Name": "Patricia",
            "Account_Name.Parent_Account": {
                "id": "554023000000238121"
            },
            "id": "554023000000310012"
        }
    ],
    "info": {
        "count": 2,
        "more_records": false
    }
}

What is the GROUP BY clause?

The "GROUP BY" clause is used to retrieve data from a table and group it based on the given conditions. Grouping records by specifying the field names (field API names) as conditions in a query. It helps you filter and organize the records efficiently, and group the results of a query based on one or more columns as the objects in the response.

Currently, the Query API does not support the distinct keyword to get distinct or unique values for multiple fields of a table. Instead, use the GROUP BY clause to achieve this.

Example:

Sample Request
{
    "select_query": "select Last_Name from Leads where Last_Name is not null group by Last_Name"
}
Sample Response
{	 
    "data": [
        {
            "Last_Name": "Benjamin"
        },
        {
            "Last_Name": "Boyle"
        },
        {
            "Last_Name": "Candy"
        },
        {
            "Last_Name" : "John"
        }
      
    ],
    "info": {
        "count": 4,
        "more_records": false
    }
}


Note
  • Refer to COQL Limitations for more details.

  • For different field types and the allowed comparators in COQL that you can use in the input query, refer to Get Records through a COQL Query.

  • COQL supports only left join and two relations(joins) in a select query.

  • If you include more than two relations in the query, the system validates only the last two relations. Consider the following example where Account_Name is a lookup field in the Contacts module, Parent_Account and Vendor are lookup and custom lookup fields, respectively, in the Accounts module, and Vendor is a custom lookup field in the Products module.

{
	"select_query": "select Last_Name, Account_Name.Parent_Account.Vendor.Product
	from Contacts where Last_Name = 'Boyle1'"
}

This query contains more than two relations. So, the system validates only the last two relations and returns the following response.

{
    "data": [
        {
            "Last_Name": "Boyle1",
            "Account_Name.Vendor.Product": {
                "id": "554023000000377090"
            }
            "id": "554023000000316022"
        },
        ],
    "info": {
        "count": 1,
        "more_records": false
    }
}