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.

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.

Consider the following Accounts table.

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

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

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