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 Type | Comparators |
| 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:
Access your files securely from anywhere
New to Zoho Recruit?
Zoho Developer Community
New to Zoho LandingPage?
Zoho LandingPage Resources
New to Zoho Survey?
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Bigin?
Topic Participants
Anu Abraham
DataWarehouse
dkvagency
Jeganprabhu S
Anamika Prasanth
Sticky Posts
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! CommandsKaizen #165 : How to call Zoho CRM APIs using Client Script
Welcome back to another exciting Client Script post! In this post, we will discuss one of the most frequently asked questions: How do you call Zoho CRM APIs from Client Scripts? In this kaizen post, 1. Ways to make calls to Zoho CRM APIs using ClientKaizen#162 : Add Tags and Open Pre-filled Email Drafts via Client Scripts
Hello everyone! Welcome to another informative Kaizen post. In this post, let us see how to accomplish the following using Client Script. 1. How to auto-tag a record based on field update? 2. How to Open a pre-filled email draft This post will provideExtension Pointers - JS SDK series #4: Managing data from a widget using ZOHO.CRM.HTTP
Handling and managing the data between two applications is a major factor for an efficient business. There are a variety of ways to handle data between Zoho CRM and other third-party applications. You can use deluge CRM tasks, create connectors, use APIKaizen #152 - Client Script Support for the new Canvas Record Forms
Hello everyone! Have you ever wanted to trigger actions on click of a canvas button, icon, or text mandatory forms in Create/Edit and Clone Pages? Have you ever wanted to control how elements behave on the new Canvas Record Forms? This can be achieved
New to Zoho TeamInbox?
Zoho TeamInbox Resources
Zoho CRM Plus Resources
Zoho Books Resources
Zoho Subscriptions Resources
Zoho Projects Resources
Zoho Sprints Resources
Qntrl Resources
Zoho Creator Resources
Zoho CRM Resources
Design. Discuss. Deliver.
Zoho Show Resources
Get Started. Write Away!
Writer is a powerful online word processor, designed for collaborative work.
Zoho CRM コンテンツ
-
オンラインヘルプ
-
Webセミナー
-
機能活用動画
-
よくある質問
-
Ebook
-
-
Zoho Campaigns
- Zoho サービスのWebセミナー
その他のサービス コンテンツ
Nederlandse Hulpbronnen
ご検討中の方
Recent Topics
Workdrive Android - how can I open a file in its native app?
Hi, I'm testing Workdrive as a replacement for Onedrive. On a laptop/Windows this works fine. It syncs between devices and when I open a file, it opens the file in its native app. However, the Workdrive Android app seems to work differently. On a AndroidZoho SDK versioning and repositories need permanent naming/versioning changes moving forward
I spent all day on this, a simple problem. Add a note to a lead, retrieve the notes from the lead. Finding samples and documentation is VERY difficult BECAUSE the versioning is totally non-standard. The php SDK doesn't follow SemVer. I suppose it mayCall transcrition working for ringcentral?
I don't see anything about what telephony providers can be used. The Zoho support person A said that RingCentral isn't supported. Zoho support person B said that it works, just make sure the call recording link works. Excellent instructions here: CallFilter by technical IDs that should not be displayed
Hello Zoho and Cumminity. I know I have already found similar requests elsewhere, but have not yet received a solution from Zoho. Therefore I would like to refresh the topic and hope that a solution can be found. I have reports in the Creator, which Iis it possible to add more than one Whatsapp Phone Number to be integrated to Zoho CRM?
so I have successfully added one Whatsapp number like this from this User Interface it seems I can't add a new Whatsapp Number. I need to add a new Whatsapp Number so I can control the lead assignment if a chat sent to Whatsapp Phone Number 1 then assignis Zoho desk down ?
We have not received any tickets via email on the Zoho desk for the past hour. I also tried sending several test emails, but as of right now, no new ticket has been created on the desk.How to get the call recording external ID via desk API
I have enabled phonbridge integration with Zoom Call. I am trying to access the call recording in Zoom by calling Zoom API. I have built a Desk workflow to trigger on a new call, to call a custom function. when calling the API, the response doesn't containA few Issues when using "Pay Bill via Check"
We have quite a bit of issues with how paying for Bills via Check works. Would love some feedback from the Zoho team in case we are doing something incorrectly. 1. When we go from a vendor and select "Pay Bill via Check" option, we see ALL the outstandingBooks Not Matching Transactions from Feed - "The total amount does not match with that of the transaction"
Recently, transactions that are transfers from a foreign currency (FCY) account to a base currency (BCY) account are not allowing for matches with transactions from the bank feed. Please see the screenshots below: As one can see, the amount is in theAbility to Initiate WhatsApp Template Messages in Zoho SalesIQ Without Preexisting Chat
Hi Zoho SalesIQ Team, I hope you're doing well. I understand that with the WhatsApp integration in SalesIQ, clients can contact us via WhatsApp, and we can use WhatsApp templates to send messages outside of the 24-hour window by reopening an existingHow to make one code flow run for multiple people in the org
Hi Folks, I have built a flow to transfer a Zoho calender event as Zoho CRM module record. The code works perfectly as needed. But the issue is I want the same functionality for others in our org. It is showing only my calendar in the selection trigger.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. "Subject: Urgent: Unrelated Email Automation Issue – Request for Immediate Resolution
Dear Zoho One Support Team, We are currently facing a critical issue with email automation in our Zoho One account. Despite deleting the associated templates and workflows, the same email templates are still being sent to our customers. This has becomeZoho Sites
Does anyone have experience building Zoho sites or know how I can find someone who does?Tip of the week 63- Know your contacts well with polls in Zoho Campaigns
Communication in its true form is characterised by feedback. In email campaigns there are a few avenues via which you can achieve this. Using polls in Zoho Campaigns is one such way. With polls enabled in campaigns, your contacts can interact with your emails and help you understand them well. There are three types of polls available to be enabled in your email campaigns: Basic poll Rating based poll Reaction based poll Basic poll- Provide a question and allow the email recipients toCRM->INVENTORY, sync products as composite items
We have a product team working in the CRM, as it’s more convenient than using Books or Inventory—especially with features like Blueprints being available. Once a product reaches a certain stage, it needs to become visible in Inventory. To achieve this,Relocating from Zoho Connect External to Zoho Connect Internal or Zoho CommunitySpaces... or move off of Connect completely.
This conversation is aimed at Zoho clients that currently use Zoho Connect's External network platform. As I discovered in the comment section of another post, Zoho Connect is halting development of Zoho Connect EXTERNAL networks in favor of the new ZohoLimited review (/questions) for Bookings 2.0
Hi all, I'm writing this review of Bookings 2.0 for two reasons: 1) it may be of interest to others, and 2) I'd like to be corrected if I'm wrong on any points. It's a very limited review, i.e. the things that have stood out as relevant, and particularlyRecurring Events Not Disappearing from Zoho Calendar When Canceled by Organizer
I receive a recurring meeting invitation to my Gmail address, The event correctly appears in my Zoho Calendar, since I have Gmail calendar integrated/viewable via Zoho Mail. When the organizer cancels one occurrence, the canceled meeting does not disappearCreate Funnel to Track Email Outreach Conversion
Hello, We would like to create a funnel that measures: N° of emails sent -> N° of emails opened -> N° of emails responded We would like to measure this email response conversion rate for each of our SDRs. We use the analytics tool of Zoho CRM and notRelay Mail Failed
Hello, We are using SMTP Relay Settings in ZOHO CRM for sending emails using office 365 server. But every time we are receiving below errors. The Relay Configuration for the domain Domain.com and feature WorkFlow has failed 3 times today . Mails willCan't lock timezone in new Zoho Bookings
Hi, since the new Zoho Bookings has been changed, I cannot seem to lock the timezone in for the meetings. I have set the working hours and location, but when I got on the link, it automatically gives me slots in my timezone. I want to lock it for an in-personLeverage layout rules to customize workflow
Layout rules in Zoho Sprints primarily aim to customize the field layout of your creation forms to meet complex requirements. But it doesn't stop there. Its customization can push the boundaries of how your fields behave, how data is gathered, how processesAuto-Generate & Update Asset Serial Numbers using a custom function (Assets Module)
Hello Team, I’ve been working on a script to automate one of our processes in Zoho FSM, and the core functionality has been successfully implemented. However, I’m encountering an issue related to serial number allocation, which is not working as expected.CRM Hack #3: How to update formula functions for already created records.
Hello everyone! It’s Wednesday and we are back with yet another hack.. I'm sure you've used formula fields to meet some requirements specific to your business. Let's consider an example each for external (customer-facing) and internal facing scenariosLeave request for 0 hours is registed as 24 hours
I have configured workschedules for all my employees, so the leave request for them whould be a lot easier. In that spirit I have configured a shift from 9 to 9 with a total duration of 0 hours. When a employee fills in a leave request for lets say aHow can I see sent mail ?
Hi, When you send a sale order or invoice to the customer using the email function, where can bee seen the sent email ? I can't find it in the "sent" folder of my mail client, nor I can't find it in the mail Related list under the company or the contact.Add RTL (Right-to-Left) Text Direction Support Across All Zoho Learn Editing Interfaces
Hi Zoho Learn Team, Hope you're doing well. We would like to request an important enhancement to Zoho Learn regarding support for right-to-left (RTL) languages such as Hebrew and Arabic. 🔹 Current Issue While the Knowledge Base Article editor providesNimble enhancements to WhatsApp for Business integration in Zoho CRM: Enjoy context and clarity in business messaging
Dear Customers, We hope you're well! WhatsApp for business is a renowned business messaging platform that takes your business closer to your customers; it gives your business the power of personalized outreach. Using the WhatsApp for Business integrationFormula Fields inside of Blueprint Transitions
We would like to have formula fields inside of blueprint transitions. We type in currency fields and would like to see the result inside of a formula field. Our use case: Send out a price for XY 1. Filling out cost fields 2. See gross profitZoho Creator Populate radio field with values with all the created rows subfor
I have Main Form where i have a lookup field where i get brewery names and the number of tanks as a multiline text field with a list of beer names Based Brewery selected and bbt_tanks number i create rows in the subform and now i want to populate listRecording Credit Card Fees when Recording Payment for Bills
It seems I am unable to record credit card fees when paying a bill. I pay close to 100% of my bills with a company credit card via online portals. I'm happy for the CC fess to be recorded as Bank Charges but it will not allow that field to be used ifSearch function in Zoho Sheet mobil app on android - not working
Hello, Im new here and registered for Zoho Sheet. Than i installed the android mobile app on my tablet (redmi note pro 5g). But I cant use the search function for my added excel table. When I use the web version for zoho sheet with the same imported tableError 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.Zoho Books | Product Updates | April 2025
Hello partners, We’ve rolled out new features and enhancements to elevate your accounting experience. From FEC report to BillPay Add-on, these updates are designed to help you stay on top of your finances with ease. Export Transactions in Factur-X FormatHow to control the # of version of files to keep?
Currently most of the WorkDrive Storage comprise of the many versions of files saved. How do I save some space and reduce the number of version of date or files saved in WorkDrive? ThanksCreating Layout Rule With Formula Field
By The Grace Of G-D. Hi, I see that i cannot use Layout Rules to show/hide Formula Fields. Is that something you plan on adding sometime soon?Rich-text fields in Zoho CRM
Hello everyone, We're thrilled to announce an important enhancement that will significantly enhance the readability and formatting capabilities of your information: rich text options for multi-line fields. With this update, you can now enjoy a more versatileMeet Zoho Sign's AI answer bot!
Hi partners, Our goal has always been to ensure all our resources are readily available for our users. With this in mind, we're excited to introduce Zoho Sign's answer bot, a solution designed to help users instantly find relevant help articles by eliminatingWidget JS SDK to Upload a photo to a record in a Module
Good day, I would really appreciate it if someone can assist me. I have written a widget, to be used in a Custom Module in CRM. My goal: I want to upload a photo from my computer and display it in die "upload image" field. I am using the JS SDK: https://help.zwidgets.com/help/v1.1/index.htmlNext Page