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, LIMIT, 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.

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
    }
}
Note
  • Refer COQL Limitations for more details.

  • For different field types and the allowed comparators in COQL that you can use in the input query, refer 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
    }
}