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

    • How to Replace an Assessment in a Job Opening on Zoho Recruit

      Hi everyone, I’m currently using Zoho Recruit and would like to replace the assessment linked to a specific job opening. I want to remove the existing assessment and add a new one. What is the best way to do this without losing any important data or affecting

    • Fixed asset management

      I want to know if there is any individual module for fixed assets management

    • Input GST Reversal for damaged goods

      In our line of business, some items are damaged and we are doing inventory adjustments to remove them from stock. However, as per GST guidelines, there is a specific rule that we have to reverse Input GST availed for such items and needs to be reported

    • Introducing Record Summary: smarter insights at your fingertips

      Hello everyone, Building on the recent launch of Zoho's in-house Zia Large Language Model (Zia LLM)—a major milestone in Zoho CRM’s AI capabilities—we’re excited to introduce the Record Summary feature. This powerful addition makes use of Zia LLM to simplify

    • No longer get Cliq notifications on phone if app not started

      On Android, I used to get notifications on my phone whether I was in the app, or it was started. Then about a month ago, I stopped seeing notifications on my phone UNLESS I had already started the app. So if I reboot my phone, and never start the app,

    • Error AS101 when adding new email alias

      Hi, I am trying to add apple@(mydomain).com The error AS101 is shown while I try to add the alias.

    • Deluge script to add Mail Task

      Has anyone out there created a custom function to create a Zoho Mail task? I'd be interested in hearing how you accomplished it. Sample code is appreciated!

    • Tags with Zapier

      Maybe I'm missing something....I hope so... Using tags for triggers is a key need.  This prevents us from having a ton of different lists. I am trying to find out how to add a tag using zapier when someone makes a purchase....but it doesn't seem to be

    • Send Supervisor Rule Emails Within Ticket Context in Zoho Desk

      Dear Zoho Desk Team, I hope this message finds you well. Currently, emails sent via Supervisor Rules in Zoho Desk are sent outside of the ticket context. As a result, if a client replies to such emails, their response creates a new ticket instead of appending

    • How to apply blueprint on tickets that created from IM module

      Hello, I have an issue applying blueprint on tickets that created from WhatsApp conversation, the tickets matches with the blueprint criteria but still we are not able to put these tickets into the blueprint. I've tried with deferent type of tickets and

    • Function #4: Schedule Customer Statements

      Regularly sending statements to customers is an imperative part of many business processes as it helps foster strong customer relationships and provides timely guidance on payments. While you can generate the statement of accounts and have it sent over

    • Zoho Mail POP & IMAP Server Details

      Hello all! We have been receiving a number of requests regarding the errors while configuring or using Zoho Mail account in POP/ IMAP clients. The server details vary based on your account type and the Datacenter in which your account is setup. Ensure

    • Remove 30-Day Client Reply Restriction on Supervisor Rules in Zoho Desk

      Dear Zoho Desk Team, I hope you're doing well. Currently, Supervisor Rules in Zoho Desk run once every hour but only apply to tickets that have received a customer response within the past 30 days. This restriction creates challenges for us, as we have

    • Paid Support Plans with Automated Billing

      We (like many others, I'm sure) are designing or have paid support plans. Our design involves a given number of support hours in each plan. Here are my questions: 1) Are there any plans to add time-based plans in the Zoho Desk Support Plans feature? The

    • Contacts Don't Always Populate

      I've noticed that some contacts can easily be added to an email when I type their name. Other times, a contact doesn't appear even though I KNOW it is in my contact list. It is possible the ones I loaded from a spreadsheet are not an issue and the ones

    • How to get NSE/BSE Stock Prices in Zoho sheets?

      I've been looking for a function that provides me with the NSE/BSE listed stocks price in Zoho Sheets like GOOGLEFINANCE in Google sheets, but I found none. Please help if there is any way to het stock prices?

    • Tip #5: Setting access rights at the subfolder level

      Hello everyone, We hope you're finding our WorkDrive Tips and Tricks series useful. For today's tip, we'll teach you how to assign higher subfolder permissions to Team Folder members. Team Folders helps you avoid the drawbacks of traditional file sharing.

    • Cannot edit email text in Zoho Form rules

      I have a number of rules set up on a form depending on a user's submission. For some reason, I am no longer able to edit the content of the emails sent out based on those rules. I am invited to "use the advanced editor", but the original text of the email

    • Can multiple agents be assigned to one ticket on purpose?

      Is it possible to assign one ticket to two or more agents at a time? I would like the option to have multiple people working on one ticket so that the same ticket is viewable for those agents on their list of pending tickets. Is something like this currently

    • For security reasons your account has been blocked as you have exceeded the maximum number of requests per minute that can originate from one account.

      Hello Zoho Even if we open 10-15 windows in still we are getting our accounts locked with error " For security reasons your account has been blocked as you have exceeded the maximum number of requests per minute that can originate from one account. "

    • how can I hide this Module?

      Hi everyone, newbie question. how can I hide the "Sales Order" column? when I try I get this message: https://imghostr.com/86395c_p7j

    • Domain verification is in progress... (How long do I need to wait?)

      Trying to setup my first email domain by connecting with GoDaddy. Have been here for quite some time and the screen is not changing. How long should this take?Send DataSend Data

    • How to display profile picture for distribution list?

      I am Admin of a Zoho Mail server and we have distribution lists along with user accounts. I am able to set Profile picture for the users and it shows when the email is sent to another companies. The members of the groups can also send email from those

    • Mail Search Not Working

      Hello, Mail search is not working at all. I've tried Chrome and Mozilla. I can try and search for an exact term, or even an email that is 1st in my email list. All search does is sit and spin, or it comes up with no results. I've also tried it on my android

    • Password should not contain sequential characters

      How can I avoid this? How do I even disable it. On my password policy page, it's all blank, so I don't know why I'm even getting this error now.

    • Same phone number for more than one account.

      Hi there, I am a webdeveloper specialising in providing websites, webhosting and email solutions for my customers. I have signed up a number of my customers to Zoho Mail in the past, and a couple of these have grown into a paid package for Zoho CRM. As

    • Is there a live chat for Zoho mail?

      I am having a problem in Zoho mail and would love to live chat with someone instead of email and wait for a response. Is there a function for this? I know there is in CRM but I can't seem to find it in mail... Thank you!

    • Integrating Zoho Desk Instances from two separate organizations

      Is it possible to integrate Zoho Desk with an instance from another organization? For example, creating a ticket in one organization can cause the creation of a ticket in the second organization? Or certain tickets from one organization be viewable by

    • Why does incoming mail inconsistently bounce back from Zoho mail

      On testing our user accounts, we are having problems where mail sent to zoho mail bounces back with errors message that 'relay access denied'. On testing from various accounts (including outlook, gmail and yahoo) mail seems to get through on some occasions

    • Zoho email setup in office365

      When i am trying to setup zoho mail setup using my domain in office365 and it is not working and it says that we couldn't log on to the incoming (IMAP) server and please check your email address and password and try again. I was able to login using my

    • JunkMail rejected

      Hello, we are facing problems sending mails. The IP has been blacklisted. Please, fix it as soon as possible. JunkMail rejected - sender4-op-o12.zoho.com [136.143.188.12]:17291 is in an RBL on rbl.websitewelcome.com, see Blocked - see http://www.spa

    • My emails going to spam folder for hotmail or outlook

      My emails (not spam mails) are going into the spam folder for my customers using hotmail. Gmail and Yahoo users are receiving the emails in their inbox. can you please solve this problem. I read few articles but coudnt find any solution to it. I am testing it by sending a simple text email no pictures nothing at all still it is filtering my emails as spam. Please help I am really loosing time and clients due to this. Thanks

    • Capture hotkeys inside the remote session and allow file exchange via clipboard

      Hi guys, assist is a really good app, and to become great it would be nice to have some features other vendors have in place and we take them for granted. For example, ScreenConnect, TeamViewer and others allow you to send hotkeys via the remote connection,

    • Cannot fetch url with custom extension (sigma - javascript)

      Hello i try to make my first extension with API request, i have two cases 1) this a deluge code attach to a button --> this one works very well response = invokeurl [ url :"my_api_fetch_url" type :GET headers:{"api_key":"myapikey","accept":"application/json","content-type":"application/json"}

    • how to upload the picture and document

      i want to upload the picture and document,would you please told how to upload them?could you told it for each step?

    • Forgot my email management account

      Hello, I am the administrator of ihomemix.com. I can’t remember which email address I used to register the account and then opened the email service for ihomemix. I can’t see the subscription period of my email function when I log in with this account.

    • Add customer to account based on domain name.

      I generate reports based on a the account field, i.e. companyX.  In GoToAssist, my last provider, there was an option to automatically assign new ticket creators to a company (or account) based on their domain name. So for example, if a new employee creates a ticket from @companyx.com, for them to be automatically added to the companyx account would be a huge advantage.  As it stands right now, I have to remember to add them to the account manually.  Often I forget and when generating a report for

    • Facilitate business processes by mandating Kiosks in your Blueprint's transition settings

      Hello everyone, We've made a few enhancements to Kiosk Studio. Blueprints provide a structured and systematic approach to executing business processes, and you can use Kiosks to build custom capabilities to retrieve, collect, and execute actions on CRM

    • Response Violation - Zoho Desk

      Hi Team, I just need an information regarding the zoho desk - Response Violation and how can we avoid the tickets from getting the tickets response violated.

    • I need help in setting up a script that works for my calling service

      Please i need your guidance and expertise in how to go about a particular scripting. You see, we are a call service that assists companies to receive calls for them, i need to create a system in my Zoho CRM whereby i will receive call from my already

    • Next Page