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 Vertical Solutions 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 2000 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, GROUP BY, and OFFSET, and aggregate functions SUM(), MAX(), MIN(), AVG(), and COUNT(), and non-aggregate functions.
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. You can set the value of LIMIT to 2000 to retrieve 2000 records per API call. Modify the values of these properties to navigate and fetch the next set of records. You can retrieve a maximum of 10,000 records in multiple API calls.
- API Credits based on the value of LIMIT:
- LIMIT between 1-200 - 1 API credit
- LIMIT between 201-1000 - 2 API credits
- LIMIT between 1001-2000 - 3 API credits
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.
You can query for records using the external fields and their values.
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_Name	Last_Name	Full_Name	Account_Name
Patricia	Boyle	Patricia Boyle	ABC Inc
John	Smith	John Smith	ABC Inc
Alice	Mason	Alice Mason	All Tyres

Consider the following Accounts table.

Account_Name	Parent_Account	Billing_City
ABC Inc	Zylker	NYC
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. In general, you can use this clause with both aggregate and non-aggregate functions.

Refer to this help page for more on aggregate functions, including sample requests and their responses.

The following is a sample request with the response of a non-aggregate function.

Sample Request

    SELECT Last_Name,Company,Lead_Source 
        FROM Leads 
        WHERE Last_Name is not null 
        GROUP BY Last_Name,Company,Lead_Source

Sample Response

  {
        "data": [
            {
                "Company": "Itech solutions",
                "Last_Name": "Benjamin",
                "Lead_Source": null
            },
            {
                "Company": "Dal Tile Corporation",
                "Last_Name": "Frey",
                "Lead_Source": "Cold Call"
            },
            {
                "Company": "Chapman",
                "Last_Name": "Hirpara",
                "Lead_Source": "Online Store"
            },
            {
                "Company": "test",
                "Last_Name": "John",
                "Lead_Source": null
            },
            {
                "Company": "Oh My Goodknits Inc",
                "Last_Name": "Kidman",
                "Lead_Source": "Advertisement"
            },
            
            {
                "Company": "Rangoni Of Florence",
                "Last_Name": "Maclead",
                "Lead_Source": "Cold Call"
            },
            {
                "Company": "Morlong Associates",
                "Last_Name": "Sweely",
                "Lead_Source": "Seminar Partner"
            },
            
            {
                "Company": "Grayson",
                "Last_Name": "Tjepkema",
                "Lead_Source": "External Referral"
            },
            {
                "Company": "Itech Solutions",
                "Last_Name": "Wick",
                "Lead_Source": null
            }
        ],
        "info": {
            "count": 9,
            "more_records": false
        }
    }

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
            }
        }