Kaizen #80 - COQL API - Part I

Kaizen #80 - COQL API - 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:


    • Sticky Posts

    • Kaizen #197: Frequently Asked Questions on GraphQL APIs

      🎊 Nearing 200th Kaizen Post – We want to hear from you! Do you have any questions, suggestions, or topics you would like us to cover in future posts? Your insights and suggestions help us shape future content and make this series better for everyone.

    • Kaizen #198: Using Client Script for Custom Validation in Blueprint

      Nearing 200th Kaizen Post – 1 More to the Big Two-Oh-Oh! Do you have any questions, suggestions, or topics you would like us to cover in future posts? Your insights and suggestions help us shape future content and make this series better for everyone.

    • Celebrating 200 posts of Kaizen! Share your ideas for the milestone post

      Hello Developers, We launched the Kaizen series in 2019 to share helpful content to support your Zoho CRM development journey. Staying true to its spirit—Kaizen Series: Continuous Improvement for Developer Experience—we've shared everything from FAQs

    • Kaizen #193: Creating different fields in Zoho CRM through API

      🎊 Nearing 200th Kaizen Post – We want to hear from you! Do you have any questions, suggestions, or topics you would like us to cover in future posts? Your insights and suggestions help us shape future content and make this series better for everyone.

    • Client Script | Update - Introducing Commands in Client Script!

      Have you ever wished you could trigger Client Script from contexts other than just the supported pages and events? Have you ever wanted to leverage the advantage of Client Script at your finger tip? Discover the power of Client Script - Commands! Commands

    • Recent Topics

    • UK MTD reports concerning turnover and cerash accounting

      Hi I am a sole trader, and I have just started with Zoho Books in order to comply with the new HMRC requirements. I use 'cash basis' - which I understand to mean that income is when the cash comes in (not the invoice date) and expenses are when they are

    • Retainer Invoice.

      Why ZOHO not have facilities to deduct partially advance payment from an invoice.

    • IMAP Server not responding.

      Trying to connect a phone via IMAP and getting "imap.zoho.com not responding." Is the server down, for maintenance or otherwise? I've tried this on two different devices and got the same error on both.

    • Share saved filters between others

      Hi, I am in charge to setup all zoho system in our company. I am preparing saved filters for everybody, but the only one can see its me. How can others see it? Thanks

    • GST Slabs Redefined: Stay Compliant Using Zoho Books!

      Hello Everyone! The Government of India is rolling out new GST rates, a major reform aimed at simplifying the current tax structure starting 22 September 2025. GST will move from four slabs (5%, 12%, 18%, 28%) to two main slabs (5% and 18%), plus a special

    • Kanban view on Zoho CRM mobile app!

      What is Kanban? The name doesn't sound English, right? Yes, Kanban is a Japanese word which means 'Card you can see'. As per the meaning, Kanban in CRM is a type of list view in which the records will be displayed in cards and categorized under the given

    • Presenting ABM for Zoho CRM: Expand and retain your customers with precision

      Picture this scenario: You're a growing SaaS company ready to launch a powerful business suite, and are looking to gain traction and momentum. But as a business with a tight budget, you know acquiring new customers is slow, expensive, and often delivers

    • No practical examples of how survey data is analyzed

      There are no examples of analysis with analytics of zoho survey data. Only survey meta data is analyzed, such as number of completes, not actual analysis of responses, such as the % in each gender, cross-tabulations of survey responses. One strange characteristic

    • Zoho Creator as LMS and Membership Solution

      My client is interested in using Zoho One apps to deploy their membership academy offer. Zoho Creator was an option that came up in my research: Here are the components of the program/offer: 1. Membership portal - individual login credentials for each

    • Adding Chargebee as a Data Connector

      Is it possible to get Chargebee added as a Zoho Analytics data connector?

    • Webform & spam

      Hi, We set up 2 webform on our website, fowarding the content to Zoho CRM. Since it has been opened up, we are getting lot of spam message (for now about 20 a day). To lower the  amount of false new leads we added the captcha field and new enquieries are send to the Approval Leads list. However we still get some spam. Is there any "anti spam" mechanism built in Zoho CRM, or how is the best way to avoid these kind of spam ? Thanks

    • Dropbox to Workdrive

      Namaste, Trust you all are doing well. Wanted to check how this can be done with Zoho flow. I typically receive dropbox links from my clients. Is there a way where I can provide the link to Zoho flow and it downloads the files from dropbox link to a work

    • Deals by Stages Funnel not showing in correct order

      Using the Stage-Probability Mapping for the Deals module we have created a steps our deals will pass through, RFQ, Closed/Lost, Declined/No-Go, Pricing, Submitted, Negotiations, Won. However when I view the Deal By Stages Funnel it does not show in the

    • Turning off the new UI

      Tried the new 'enhanced' UI and actively dislike it. Anyone know how to revert back?

    • Confirmation prompt before a custom button action is triggered

      Have you ever created a custom button and just hoped that you/your users are prompted first to confirm the action? Well, Zoho knows this concept. For example, in blueprint, whenever we want to advance to the next state by clicking the transition, it is

    • How to create auto populate field based on custom module in Zoho CRM?

      Hello, i'm still new to Zoho CRM and work as administrator in my company. Currently, I'm configuring layout for Quotes Module. So, the idea is, I've created a read-only field in Quotes called "Spec". I want this field automatically filled with Specification

    • Rich Text For Notes in Zoho CRM

      Hello everyone, As you know, notes are essential for recording information and ensuring smooth communication across your records. With our latest update, you can now use Rich Text formatting to organize and structure your notes more efficiently. By using

    • Change Last Name to not required in Leads

      I would like to upload 500 target companies as leads but I don't yet have contact people for them. Can you enable the option for me to turn this requirement off to need a Second Name? Moderation update (10-Jun-23): As we explore potential solutions for

    • Office 365 and CRM mail integration: permission required

      Has anyone run into this weird problem? My email server is Office 365. When I try to configure Zoho CRM to use this server, a Microsoft popup window opens requesting user and password. After entering that, I get a message in the Microsoft window saying

    • Tables improvement ideas / features

      Heya, I've been using Zoho Tables for a few months now and wanted to post some features that I think will be greatly beneficial for the tool: 1. Ability to delete a record in automation or move a record in automation. - Usecase: I move a record from one

    • Deluge - Can't get phone number SalesIQ

      Hey folks, I’m building a custom plug for SalesIQ that’s supposed to register leads into Zoho CRM. The SalesIQ chat is being implemented on WhatsApp, and in my plug I’m using this line: mobile_clean = session.get("phone").get("value"); From what I understand,

    • Zoho Desk - Community

      As a regular user of Zoho Cares Community I would really love to see the publish date of articles. For example, when I look at Announcements, it would be very beneficial to see which ones were posted recently, over those which have just and a recent comment.

    • access to quartz for my customers

      Hi how can I have access to the application quartz you use for us to send you screen rocording, this feature would be immensely useful for our customers support https://quartz.zoho.com/

    • Issue with Inline Images in Email Reply via Zoho Desk API

      Hi, I am attempting to send inline images in an email reply using the Zoho Desk API, but the images are not being displayed inline for the recipient. I have followed this documentation: https://desk.zoho.com/DeskAPIDocument#Uploads https://desk.zoho.com/DeskAPIDocument#Threads#Threads_SendEmailReply

    • How to search a value stored in a subform?

      Hello, We store serial numbers in subforms but now we would like to be able to search the values to be able to easily find the record with the serial number. I saw that it's not possible to search such values through global search but is it possible to do it an other way? Thank you,

    • Field Dependency Not Working on Detail Page in Zoho Desk

      Hi Support Team, I’ve created field dependencies between two fields in Zoho Desk, and they are working correctly on the Create and Edit layouts. However, on the Detail page, the fields are not displaying according to the dependencies I’ve set — they appear

    • How do the keyword critera work?

      Hi, I'm working on automated assignment of tickets based on keywords. How does this feature work? Where does this criteria look for keywords - email address, subject, email body? Can you please clarify this as I want to avoid overlapping with criteria

    • Error: Unsupported content type: text/html;charset=UTF-8 after tryeing to get the token for n8n automation

      I am working on ZOHO Desk automation and need to get the ZOHO auth token for n8n I have created the app in ZOHO Desk API, got client id and client secret. Added all data required to get a token in n8n. After I sign in with my ZOHO credentials in ZOHO

    • Improving Collaboration Features in Zoho Portal

      Hello Zoho Community, I’ve recently started exploring Zoho services and I’m really impressed with the wide range of features. However, I feel there is still room for improvement in the collaboration area. For example, it would be really helpful if we

    • Automated Shopify Emails Not Being Delivered

      I have an ecommerce store with Shopify. I recently set up my email to be served through Zoho. Since doing this, customers are not receiving some of our automated emails from Shopify itself. Our initial email that confirms their purchase goes through but our Shipping Notification that is automatically sent out upon fulfillment is not going through. Sometimes we get a notice that it's been classified as spam, sometimes nothing. I can send/receive email via Outlook on my desktop and I can send/receive

    • I can't log in to my account on Thunderbird

      I've just had to rebuild my PC (calamitous mess from Microsoft with Win10/Win 11 'upgrade' - they confirmed I had to start with a new build). I have used Zoho mail for years via Mozilla Thunderbird, but now I've had to download the latest version of TBird,

    • Send a campaign to one recipient.

      Very often I speak to a customer and they say they didn't see my email (maybe it went in Junk, maybe they deleted it). Anyway, I just want to go into the Campaign and send it to one person. You already have a feature very close to this - when sending a Test. While developing a campaign, I can send tests to anyone. Why can't we have this AFTER  the campaign has been sent? I know, there's a caveat, and that's in the use of merge tags. Most of the time I only use FNAME, but maybe you could check if

    • Try FSM again for our business

      We already have our customers individual equipment in CRM with serial numbers, install dates, warranty length and importantly next service which is generally 2 years. a month before the service date is due we get get a report and send out service reminders.

    • Use Zoho Books to bill for work done in Zoho Desk??

      I'm trying to see if something is possible (and if yes, how). We use Zoho One to manage our business. We have a lot of clients that will put in a ticket (via portal) to have work done. Out techs will pick up the ticket, do the work, and then log the time

    • Get Cliq Meetings in my O365 calendar

      Hi, we are currently evaluating to replace the Teams Messaging and Meetings with Cliq. We currently still have all our email and calendars in O365. What i want to achieve is, to create a (ZOHO) meeting from Cliq and have this meeting added to my Outlook/O365

    • Issue with Zoho Help Portal – Tickets Missing or Not Answered

      Hi, How are you? I think there may be an issue with the Zoho Help Portal. I opened a few tickets directly in the help portal a some time ago but never received any response I also opened ticket 148356451 by email. I did receive a reply to it, but the

    • DUNS & Bradstreet and Credit risk monitoring integration with Zoho books

      Small businesses not being paid by bigger clients and clients of all sizes is a huge problem. It will be nice if Zoho develops integration with DUNS & Bradstreet(D&B) and Credit risk monitoring integration with Zoho books. That have small businesses can

    • Zoho Forms - Form Rules based on attachment fields

      Many businesses use forms to collect documents and images from customers. In many cases, you may want to trigger a notification or other automation based on whether or not an attachment was added. I've noticed that attachment fields do not appear in Rules

    • Add multiple users to a task

      When I´m assigning a task it is almost always related to more than one person. Practical situation: When a client request some improvement the related department opens the task with the situation and people related to it as the client itself, the salesman

    • Can't open draft email for editing

      Last night I started composing an email and I let it save in drafts. This morning I want to continue working on the email. It is in my Drafts folder but it will not open. The only option there is to delete it. This is not the first time it has happened. On previous occasions I have just deleted the draft and started afresh, but I really want this one back. Windows 10 with Pale Moon 28.10.0 browser.

    • Next Page