COQL API in Zoho CRM - Part I

COQL API in Zoho CRM - Part I

Hi everyone! Welcome back to another week of Kaizen! 

This week, we will discuss the COQL Queries in detail.

COQL (CRM Object Query Language) is a powerful query language based on SQL syntax that allows users to write their own queries and fetch records using module API names and field API names. In this post, we will discuss the operators supported by COQL in detail and also provide examples to help you understand better.  Please note that the information in the article holds true for version 4 of Zoho CRM APIs.

When should you use the Query API?

Use Query API when you want to query for a module's data and/or it's look up related data using various comparators, or for records that fall into a custom view without actually creating one. For example, if you want to query for all products in a specific price range, with a 5 star rating and sort the results by price, use the Query API. Similarly you can use Query API to query for records from cross-modules (linked via lookup field), such as filtering products based on the vendor's details. 

What types of queries are supported by COQL?

COQL supports only the SELECT query, which is used to select data from the CRM, based on the conditions specified by the clauses. 

Here is a sample query:
SELECT {field_api_names} FROM {module_api_name} WHERE {field_api_name} {comparator} {logical_operator} {value} ORDER BY {field_api_name} ASC/DESC LIMIT {limit} OFFSET {offset}

What are the clauses & aggregate functions supported by COQL?

WHERE - used to select records based on specific conditions.
FROM - specifies the module from which to fetch the records.
ORDER BY - used to sort the results in ascending or descending order.
LIMIT - used to limit the number of records returned by the query.
OFFSET - used to skip a specified number of records while retrieving the records.


{
 "select_query" : "select Last_Name, First_Name, Mobile, Final_Score from Leads where (Lead_Status = 'Not Contacted') ORDER BY Final_Score DESC LIMIT 5 OFFSET 10"
}


For example, the above query retrieves the Last_Name, First_Name, Mobile and Final_score of all Leads whose status equals 'Not Contacted'. The results will be sorted in descending order based on the Final_Score field, and only the 5 records after skipping the first 10 will be returned.

NOTE : You can also use the syntax "LIMIT offset, limit" to achieve the same result. For example :


{
 "select_query" : "select Last_Name, First_Name, Mobile, Final_Score from Leads where (Lead_Status = 'Not Contacted') ORDER BY Final_Score DESC LIMIT 5, 10"
}
​

Aggregate functions can perform calculations on a set of values and return a single value. 
SUM() - to get the sum of the values of an aggregate field in a module.
MAX() - to get the maximum value of an aggregate field.
MIN() - to get the minimum value of an aggregate field.
AVG() - to get the average value of the values of a field.
COUNT() - to get the number of records that satisfy the criteria.

Wildcard Character Support in COQL

In COQL queries, the only supported wildcard character is %. This % wildcard can be used with the like operator to achieve functionality similar to the contains, starts_with and ends_with operators. For instance, '%tech" queries for field values ending with tech, 'C%' queries for the values starting with C, and '%tech%' translates to contains 'tech'.

Supported field types and Comparators

The following table gives a gist of the field types whose data you can query for, and the comparators for each field type.
Field TypeComparators
Text and Picklist=, !=, like, not like, in, not in, is null, is not null
Lookup=, !=, in, not in, is null, is not null
Date, DateTime, Number, Currency=, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null
Boolean=

Supported field types and Comparators

1) Text and Picklist Fields

Supported comparators : =, !=, like, not like, in, not in, is null, is not null. Please note that the like operator is used for starts_with, ends_with, contains, and not like operator is used for not contains. 

Sample Query 1: (=, like, in)

{
    "select_query": "SELECT First_Name, Last_Name FROM Leads WHERE (((Lead_Status = 'Pre-Qualified') and (Company like '%zylker%')) and Industry in ('Technology', 'ERP'))"
}

This SELECT query retrieves the First_Name and Last_Name of all Leads whose Lead_Status is Pre-Qualified, Company contains zylker, and Industry is either Technology or ERP. 

Sample Query 2:(not in, like, not null)

{
"select_query": "SELECT First_Name, Last_Name FROM Leads WHERE (((Lead_Status not in ('Closed-Won', 'Closed-Lost')) and (Lead_Source like '%Web%')) and (Skype_ID is not null)) LIMIT 2"
}

This query will return the first name and last name of all leads whose lead status is not Closed-Won or Closed-Lost, whose Lead Source fields contains web, and whose Skype ID field is not empty.

Sample Query 3:( !=, not like, null)

{
    "select_query": "SELECT First_Name, Last_Name, Mobile FROM Leads WHERE (((Lead_Source != 'Webinar') and (City not like '%New York%')) and (Skype_ID is null))"
}

This query retrieves the first name, last name and mobile of leads where the lead source is not a webinar, the city is not New York, and the Skype ID field is empty or null.

2. Lookup Fields

=, !=, in, not in, is null, is not null are the supported comparators for lookup fields. If you want to get the name of the lookup field in the response, you must include the field API name in the query. Otherwise, the system will return only the ID of the field.

Sample Query 1: (=, not in, !=)

{
 "select_query": "select Last_Name, First_Name, Full_Name, Account_Name, Owner from Contacts where (((Account_Name.Account_Name != 'Zylker') and (Owner = 4876876000000327001)) and Vendor_Name.Vendor_Name not in ('Skytech','SR')) LIMIT 2"
}

This query retrieves the last name, first name, full name, account name, and owner of two contacts whose account name is not Zylker, whose owner ID is 4876876000000327001, and whose vendor name does not contain Skytech or SR.
Sample Response:
{
    "data": [
        {
            "First_Name": "John",
            "Full_Name": "John Wilson  ",
            "Owner": {
                "id": "4876876000000327001"
            },
            "Last_Name": "Wilson  ",
            "Account_Name": {
                "id": "4876876000000333089"
            },
            "id": "4876876000000333182"
        },
        {
            "First_Name": "Josephine",
            "Full_Name": "Josephine Darakjy",
            "Owner": {
                "id": "4876876000000327001"
            },
            "Last_Name": "Darakjy",
            "Account_Name": {
                "id": "4876876000000333090"
            },
            "id": "4876876000000333183"
        }
    ],
    "info": {
        "count": 2,
        "more_records": true
    }
}

In this query, the id of the account is returned but not the name since we have not specified the field API name in the query. To fetch the field name, specify the field API name in the query. Refer to the following sample query to know how.

Sample Query 2:  (in, is null, is not null)

{
    "select_query": "SELECT Deal_Name, Account_Name.Account_Name, Created_Time FROM Deals WHERE (((Account_Name.Account_Name in ('Grayson','Zylker')) and (Owner is not null)) and (Contact_Name is null)) ORDER BY Created_Time DESC LIMIT 2"
}

This query retrieves the Deal Name, Account Name, and Created Time of the 2 most recently created Deals whose Account Name contains Zylker or Grayson, and do not have a Contact Name associated with them but have a Deal Owner associated with them. The results will be ordered in descending order by the Created Time.
 
Sample Response:
{
    "data": [
        {
            "Deal_Name": "Westborne Deal",
            "Created_Time": "2023-04-06T10:04:02+05:30",
            "Account_Name.Account_Name": "Grayson",
            "id": "4876876000003548001"
        },
        {
            "Deal_Name": "Eastwing Deal",
            "Created_Time": "2023-04-05T19:10:55+05:30",
            "Account_Name.Account_Name": "Grayson",
            "id": "4876876000003526011"
        }
    ],
    "info": {
        "count": 2,
        "more_records": true
    }
}

In the same query, if you want to use the Account ID instead of the name, replace ((Account_Name.Account_Name in ('Grayson','Zylker')) with ((Account_Name in (4876876000001236083, 4876876000002799001)) to include the IDs instead of the field API names and the account names.
{
    "select_query": "SELECT Deal_Name, Account_Name.Account_Name, Created_Time FROM Deals WHERE (((Account_Name in (4876876000001236083, 4876876000002799001)) and (Owner is not null)) and (Contact_Name is null)) ORDER BY Created_Time DESC LIMIT 2"
}

3. Date, DateTime, Number, Currency Fields

=, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null are the supported comparators for these fields.

Sample Query 1: (between, <, >)

{

    "select_query": "SELECT Deal_Name, Amount, Stage, Probability FROM Deals WHERE (((Closing_Date between '2023-01-01' and '2023-03-31') and (Probability < 99)) and (Amount > 10000))"

}

This query retrieves the deal name, amount, stage, and probability of all deals whose closing date is between January 1, 2023 and March 31, 2023, and whose probability is less than 99 and amount is greater than 10000.

Sample Response:
{
    "data": [
        {
            "Deal_Name": "Chapman Deal",
            "Amount": 2500000,
            "Probability": 97,
            "Stage": "Closed Won",
            "id": "4876876000003550011"
        }
    ],
    "info": {
        "count": 1,
        "more_records": false
    }
}

Sample Query 2: (<=, !=, >=)

{
    "select_query": "SELECT Product_Name, Qty_in_Stock, Vendor_Name, Cost_Price FROM Products WHERE (((Sales_End_Date <= '2023-04-30') and (Qty_in_Stock != 0)) and (Cost_Price >= 500))"
}

This query retrieves the product name, quantity in stock, vendor name, and cost price for all products whose sales end date is on or before April 30, 2023, whose quantity in stock is not zero, and whose cost price is greater than or equal to 500. Since Vendor_Name is a lookup field, only the ID will be returned in the response.
Sample Response
{
    "data": [
        {
            "Cost_Price": 2000,
            "Vendor_Name": {
                "id": "4876876000001039017"
            },
            "Product_Name": "Sigma",
            "Qty_in_Stock": 30,
            "id": "4876876000001036109"
        }
    ],
    "info": {
        "count": 1,
        "more_records": false
    }
}

Sample Query 3: (not between, is not, >)

{
    "select_query": "SELECT Last_Name, First_Name, Referred_By.Full_Name FROM Leads WHERE (((Final_Score not between 10 and 20) and (Annual_Revenue is not null)) and (No_of_Employees > 200)) LIMIT 1"
}

This COQL query selects the last name, first name, and the full name of the lead's referred by field, for one lead whose final score is not between 10 and 20, annual revenue is not null and whose number of employees is greater than 200. 
Sample Response
{
    "data": [
        {
            "First_Name": "Chau",
            "Last_Name": "Kitzman",
            "id": "4876876000000333403",
            "Referred_By.Full_Name": "Simmons Truhlar"
        }
    ],
    "info": {
        "count": 1,
        "more_records": true
    }
}

Sample Query 4: (>, not in, =, is null)

{
   "select_query":"SELECT PO_Number, PO_Date, Status, Discount FROM Purchase_Orders WHERE (((PO_Date = '2023-04-06') and (Due_Date not in ('2023-04-10','2023-04-11', '2023-04-12'))) or ((Discount > 10) and (Requisition_No is null)))"
}

This query selects the PO_Number, PO_Date, Status, and Discount from the Purchase_Orders module where the PO_Date is equal to '2023-04-06' and Due_Date is not any of '2023-04-10', '2023-04-11', or '2023-04-12'; OR Discount is greater than 10 and Requisition_No is null. In this query, we have used both AND and OR operators to combine the clauses.
Sample Response
{
    "data": [
        {
            "Status": "Created",
            "PO_Date": "2022-05-10",
            "Discount": 9990,
            "PO_Number": "PO-JL-3876",
            "id": "4876876000001125176"
        },
        {
            "Status": "Created",
            "PO_Date": "2023-04-06",
            "Discount": 370.37,
            "PO_Number": "PO-DA-1932",
            "id": "4876876000003561019"
        }
    ],
    "info": {
        "count": 2,
        "more_records": false
    }
}

We hope you found this post useful and that it has given you a better understanding of COQL queries. In our next post, we will discuss the rest of the field types, aggregate functions with more examples, and provide more queries to help you get started.

If you have any questions or feedback, please let us know in the comments below, or write to us at support@zohocrm.com. We would love to hear from you! 

Additionally, if you have any questions about the COQL API or how to construct a query, kindly let us know in the comments.

Additional Reading:





        • Recent Topics

        • A recap of Zoho Sprints 2024

        • Unable to create custom fields for shipment order

          I'm unable to create custom fields for shipment orders, even though the custom fields are set up correctly. A request to the following endpoint: https://www.zohoapis.com/inventory/v1/settings/preferences/customfields?organization_id=${ZOHO_ORGANIZATION_ID}&entity=shipment_order

        • Records per page in New UI

          It seems the new UI lack of "Records per page" function, it is very handy if you are looking for a data that you don't know the exact search term, but you know it may "between" few entries. without a "page" function, we kind of have to keep page down and page, the autoload is not that fast, and you are dealing with thousands of entries. Could we please have the "records per page" function back to New UI (also it shows total counts of the record) Looking forward to hear from you.

        • zet pack not working

          We are using the zet pack command to package our Zoho extension. However, after running the command, the extension gets packed, but the resulting package is empty. We've attached a screenshot for reference. Could you please assist us with resolving this

        • CSV File Added to Form - Parse and Map to Fields

          Hi,  I apologize, I can't seem to find a clear explanation or help article on how to parse a CSV file. On validate, I process this deluge script. fileContent = Collection(input.File_upload.content); result = fileContent.values(); info result; What I want to do is create a record (in another form), and map certain fields to fields in the form.  I can get values, which is the whole file but no keys. If I use result = fileContent.keys(); I get "0" As you can see from CSV, my first line is my map and

        • Encountered an error while creating a bill in Zoho Inventory: {"code":6,"message":"Invalid data provided"}

          I attempted to create a bill using the Zoho Inventory API, but I received an error: {"code":6,"message":"Invalid data provided"}. However, when I made the same request again, it was successful. Does anyone have insights on why this happened?

        • Free user licenses across all Portal user types

          Greetings everyone, We're here with some exciting and extensive changes to the availability of free user licenses in CRM Portals. This update provides users with access to all Portal user types for free to help them diversify their user licenses and explore

        • Custom "Filter By" in Client Portal

          Currently our client portal only shows items for that specific person that is logged on to the portal, we want the current logged user to see all items for that user's company. An example would be invoices, so the current user would see all invoices for

        • Why do I need to send the Customer ID in the Create Purchase Order Request?

          I'm trying to create a purchase order using this endpoint https://www.zoho.com/inventory/api/v1/purchaseorders/#create-a-purchase-order Unfortunately, I'm getting this error { "code": 4, "message": "Invalid value passed for Customer ID" } The doc doesn't

        • How to Retrieve Serial Numbers of Items in Zoho Inventory via API?

          Hello, I am currently working with the Zoho Inventory API and need to retrieve the serial numbers associated with specific items in our inventory. After reviewing the documentation, I couldn’t find an endpoint dedicated to fetching serial numbers for

        • merging email accounts

          previously I was using 5 mail pop mailboxes within VO , 2 of them are becoming obsolete so I was thinking about deleting the obsolete ones and merge the remainders into my main account mailbox within VO , is this possible ? thnx in advance.

        • Queries filtered by current page/record

          I have been trying to use the new queries feature, and I can filter the query, but I'm coming unstuck because I don't understand how to make the query dynamically include the filter of the current record. ie if I'm on a deal, to filter all the records

        • ZohoPeople API - Retrieve leave type IDs

          Hi All, I have created a leave type in Zoho People UI. Now I need to fetch the  Leave Type ID of it. As per the documentation[1] I used the  curl request [2]. But I ended up with the error response from the API.  {"response":{"message":"Error occurred","errors":{"message":"Server Error Occured","code":7031},"status":1,"uri":"/api/leave/getLeaveTypes"}} The new API[3] does not tell anything on how to retrieve the Leave Type ID.  Have you done any changes to the API recently. If so please let me know

        • Inadequate Customer Support

          Hello & Greetings! I have been a pro Zoho user since the last 2 years and I would admit that the apps that are being offered are good, however the support we receive has a lot more to achieve. This being a design issue rather than a staff issue. Being

        • Incorrect Closing Stock Amount value

          Act as Zoho Inventory Expert. We are a construction company, OVAL Projects Engineering Limited. We started using Zoho Inventory for Stock Management.I have multiple warehouses. I have encountered a problem while generating custom warehouse wise inventory

        • The Next Chapter for CRM for Everyone: Moving from Early Access to Phased Rollout for Customers

          #CRM25Q1 Hello Everyone, Until now, CRM for Everyone has been available in early access mode exclusively for users who opted to try the new version. We are now transitioning to a phased release, starting with the basic edition. We are thrilled to announce

        • Zoho vault uses only password to unlock not a TouchID

          TouchID works when normally openning app but when called from keyboard while browsing or trying to log to another app it shows only password option to unlock. This behaviour is only on iPad Mini witch latest 18.2OS

        • Unable to add Guest Members

          We are having issues adding Guest Members to our Cliq account. We have sent out a number of request but it seems that only some people are able to access the platform. Others have received a message stating that they need to be granted access from an

        • Webhook when estimate is refused is not firing

          Hello, I use a workflow through make that sends estimate with zoho books (I paid books and sign). -Those estimates when accepted are firing the webhook that I create in zoho sign (photo 1) -However when refused they are not firing the webhook that I created

        • New Leave Type: Compensatory off

          Hi, there is a new Leave Type: Compensatory off. Can someone tell me how to use it, because it sounds it could work for overtime compensation for our techs. Thanks Andreas

        • Invoice status on write-off is "Paid" - how do I change this to "Written off"

          HI guys, I want to write off a couple of outstanding invoices, but when I do this, the status of the invoices shows as "Paid". Clearly this is not the case and I need to be able to see that they are written off in the customer's history. Is there a way

        • Create a custom button to modify custom fields in zoho Inventory

          I am needing a script for two buttons, 1. Button will add todays date to a custom field named cf_sent_to_sov 2. Button will mark a checkbox or unmark a checkbox field named cf_parts_ordered I have been trying to figure out deluge but have not got anywhere

        • How to add a record for a different report

          I have one form and it has two reports I need to programmatically add records to both reports For example one report is draft and other is processed After the user performs some action on the draft report I want to create a new report in Processed and

        • Create custom rollup summary fields in Zoho CRM

          Hello everyone, In Zoho CRM, rollup summary fields have been essential tools for summarizing data across related records and enabling users to gain quick insights without having to jump across modules. Previously, only predefined summary functions were

        • How do I fix this? Unable to send message; Reason:554 5.1.8 Email Outgoing Blocked.

          How do I fix this? Unable to send message; Reason:554 5.1.8 Email Outgoing Blocked.

        • Webhook 'when estimate is refused' is not firing

          Hello, I use a workflow through make that sends estimate with zoho books (I paid books and sign). -Those estimates when accepted are firing the webhook that I create in zoho sign (photo 1) -However when refused they are not firing the webhook that I created

        • Amazon Integration

          Hi, I am seller on Amazon , & I would like to sign up for Zoho books. However my question is can we automate/integrate invoicing, charges and returns in amazon with Zoho using API? Do you have a developer for this? I did take a look at zapier however it just has a create Invoice function nothing else.

        • Link project tasks to tasks in CRM and/or other modules.

          Hello, I have created and configured a project in Zoho Projects with a set of tasks. I would now like to link these tasks (I imagine according to the ID of each one) to actions in the CRM: meetings, tasks, analytics). The aim is to link project tasks

        • Zoho CRM API Credits & Limits for Workflow

          Hi Team, Just wanted to clarify how the API credits work for Zoho CRM and workflows with custom functions. API Credits are based on your subscription and are set at the account level. You can buy additional credits if needed. For Enterprise customers,

        • Count the NUMBER of Contacts for an Account automatically

          Hello. Is there any way Zoho can count the number of CONTACTS for a particular ACCOUNT and have a field in the ACCOUNT module update itself automatically? Currently we use Zoho to administer our language school and the Contacts represent students and Accounts represent Grupos (Classes). It would be very useful for us to have a feature like this enabled, and I can see other similar applications requiring something like this. The solution would be even better if the Contacts met a specified criteria,

        • How to use Twilio to send appointment notification and reminder SMS in Zoho Bookings

          Hit no-shows out of the ballpark by combining Zoho Bookings and SMS providers. SMS notifications help you remind customers of their appointments and reduce no-shows by reaching out where they are. In this guide, we'll configure an SMS provider called

        • geographic search filter in map view

          Hi, I have a recruiting and timesheet system built in Creator. The client wants to enhance the search for candidates based on their location and filter by job skills - currently they look on the Map View which uses the geo location or post code of the

        • Announcement: Upcoming changes to the permission grant flow for OAuth apps

          This announcement is intended for app developers who use the Zoho API console. We're going to implement an important update to the way users grant permission for the OAuth apps created through the API console. What’s changing? Currently, users can grant

        • Add Google Workspace Module to Zoho Flow

          Dear Zoho Flow Team, I hope this message finds you well. We’d like to request the addition of a dedicated Google Workspace module in Zoho Flow. Currently, there are no triggers or actions for Google Workspace, which limits our ability to integrate and

        • How do i remove the Powered by Zoho logo from my careers page

          Can I remove this? ​

        • Totals on Pivot Table

          Is there a way to change the way the subtotal calculates? In this example I have a formula to give me the average monthly attendance ....but I want the grand total of the month to be the sum of all the average attendances...any ideas? Thank you!@

        • Team Inbox is not working AGAIN

          I like Team Inbox in general. It makes using a collaborative inbox easy - when it works. The problem is that it doesn't work at times - and it seems to not work, a lot. It's not catastrophic failure, it's little things. Unable to send messages Unable

        • HOW TO USE ZOHO

          IDK

        • Zoho account sign in with passkey

          Hello, I am trying to sign in using passkey, but the option doesn't show up in the web and is disabled in Oneauth on mobile, saying the admin has restricted the use. On the Admin page in Security MFA I can find no option for passkey. Help would be greatly

        • Pivot table with Text values - "Matrix Report"

          User Story - As a user, I would like the ability to display textual data in a two-way table, matrix format (text datatypes, not numerical datatypes displayed as a dimension) One major feature missing from the Pivot tables in Zoho Analytics is the ability

        • Next Page