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 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
Kaizen #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 achievedKaizen #142: How to Navigate to Another Page in Zoho CRM using Client Script
Hello everyone! Welcome back to another exciting Kaizen post. In this post, let us see how you can you navigate to different Pages using Client Script. In this Kaizen post, Need to Navigate to different Pages Client Script ZDKs related to navigation A.Kaizen #210 - Answering your Questions | Event Management System using ZDK CLI
Hello Everyone, Welcome back to yet another post in the Kaizen Series! As you already may know, for the Kaizen #200 milestone, we asked for your feedback and many of you suggested topics for us to discuss. We have been writing on these topics over theKaizen #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.
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
Ability to add VAT to Retainer Invoices
Hello, I've had a telephone conversation a month ago with Dinesh on this topic and my request to allow for the addition of VAT on Retainer Invoices. It's currently not possible to add VAT to Retainer Invoices and it was mutually agreed that there is absolutely no reason why there shouldn't be, especially as TAX LAW makes VAT mandatory on each invoice in Europe! So basically, what i'm saying is that if you don't allow us to add VAT to Retainer Invoices, than the whole Retainer Invoices becomesZOHO DESK link with Power BI
HI, I am using ZOHO Desk for last two years and now my client is asking to integrate ZOHO desk data to Power BI so that they can use Data for reporting. Kindly guide in details so that i can give access to ZOHO desk export data for more visibility.URLs being masked despite disabling tracking
Hey, We had disabled click tracking from an email update we are sharing with our users. Despite this, the URL the end user is receiving is masked, and looks like "https://qksyl-cmpzourl.maillist-manage.net/click/1d8e72714515cda6/1d8e72714515ca70" insteadZoho CRM - Calendar Cards View - Let Users Decide What Is Displayed On Calendar Entries
Imagine planning your week of face-to-face meetings across three counties. You’re trying to group appointments by location to make the best use of your time, but Zoho CRM’s calendar doesn’t show where each meeting is happening. You’re left trying to rememberSet to Review for all
We are testing the use of Writer as part of an internal review process for statement of work documents and have found that when the document is changed from Compose to Review by one person, that is not reflected for all others who view the document. IsDashboard Autorefesh
Good day, I am a dashboard that displays the number of tickets based on "Product Name". This dashboard is displayed on a big TV for the team to monitor. Can the dashboard auto-refresh every few minutes to display the new values? Currently, we have closedDeferred/ Unearned revenue
Dear Zoho Team, Just in case you have missed out my query posted few days ago: We issue invoices relating to 12-month web hosting service. When we issue the invoice, we should record the entire amount of the invoice as DEFERRED/UNEARNED REVENUE (ie. $10Report Hover Setting
Would be great if we will able to show information to the user while hovering a record in a report.Bigin Android app update: Zoho Books integration
Hello everyone! We’re excited to introduce Zoho Books integration on the latest version(v1.8.5) Bigin Android app. Once the integration is completed in the web(bigin.zoho.com), the Zoho Books tab will be visible in detail View of Contacts, Companies,Audit Log enhancements: Increased retention period, better user visibility, and more
Hello everyone, The Audit Log brings in the following enhancements which improve the overall ability to manage data. Why did we make these updates? Extended Data Retention: Audit data can now be filtered and exported for a 60-day period, doubling theQuestion Regarding Managing Sale Items in Zoho Books
Good day, I was wondering about something. Right now, Zoho Books doesn’t seem to have a way to flag certain items as being on sale. For example, if I want a list of specific items to be on sale from October 1 to October 12, the user would have to export[WEBINAR] Smooth year-end closure with Zoho Books (KENYA)
Hello there, This webinar is for all Kenyan businesses looking to wrap up their financial year smoothly! Join our free session to learn how Zoho Books can simplify your year-end process. What to expect from this webinar: - All the latest updates in ZohoSystem flaws and lack of response from Zoho
I have had to go on here as no-one is replying to my emails regarding my problem (been 10 days and no email reply) and your chat facility online through your Zoho Books software opens and closes immediately, so not functioning properly. I actually calledCustomer Grouping
Hi, how can I group multiple customers into single group. So that I can have idea of accounts receivables of all the customers in single group. Like if there are multiple subsidiaries of same company we have having a business with, and want to view theTwo currencies
More and more I am finding that internattional payments' fees are unpredictable. I would like, on my invoices that are in a foreign currency (eg. USD$ or EUR€) for there to be a GBP£ TOTAL display alongside the invoice's currency total. This would makeZoho Books | Product updates | September 2025
Hello users, We’ve rolled out new features and enhancements in Zoho Books. From PayNow payment method to applying journal credits to invoices and bills in other locations, explore the updates designed to enhance your bookkeeping experience. IntegrateGST 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 specialZoho Books | Product updates | October 2025
Hello users, We’ve rolled out new features and enhancements in Zoho Books. From iOS 26 updates to viewing reports as charts, explore the updates designed to enhance your bookkeeping experience. Zoho Books Updates for Apple Devices At WWDC 2025, AppleNeed Inactive accounts to be visible in Reports in Zoho Books
I N=need Inactive accounts to be visible in Reports in Zoho Books to do recons of the accounts but when i see the same they are not visible in the Accountant - Account Transactions reportEdit item custom fields
Getting this error : Transactions have been created with the custom field. Hence it cannot be deleted. Not trying to delete it, just trying to change which modules to show in or to not show at all in transactions !Zoho Books - How to Invoke a Custom Function in Schedulers
We have multiple schedulers that send emails to customers in batches. Currently, we are maintaining the same code across several schedulers. Is it possible to use a custom function inside a scheduler script? If yes, how can we invoke the custom functionUse 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 timeZoho Finance Suite - Customer Custom Tabs - Dynamic Link
Hi Finance Suite team, When creating a Custom Tab for a Client Portal, there is no option to add dynamic parameters. This would be very helpful for adding Zoho Analytics dashboards which can be dynamically filtered through the URL to only show informationModular Permission Levels
We need more modular Permissions per module in Books we have 2 use cases that are creating problems We need per module export permission we have a use case where users should be able to view the sales orders but not export it, but they can export otherBlueprint or Validation Rules for Invoices in Zoho Books
Can I implement Blueprint or Validation Rules for Invoices in Zoho Books? Example, use case could be, Agent confirms from client that payment is done, but bank only syncs transactions tomorrow. in this case, Agent can update invoice status to done, andFeature Enhancement Request – Text Formatting Options in Item Description (Zoho Books/Quotes Module)
Dear Zoho Development Team, Greetings from Radiant360 Integrated Technical Services LLC. We would like to bring to your attention a functional limitation we've encountered within the Item Table / Quote Description section of Zoho Books (and Zoho CRM Quotes).Add Option to Mass Dispatch by User
Hello! We are using the dispatch console to dispatch service appointments to our service ressources. Right now, the process is our dispatcher verifies each ressource's route for the day and dispatches it after validation. Sadly, there doesn't seem toBank Receipt Catagorization
Hi, how can I match a bank deposit to multiple customer's invoices ? For e.g. A single person paid to us on behalf of different five customers. I need to keep the separated invoices for each customerPer Level Approval for admins
We need Process admins like Zoho CRM in Zoho Books for per stage approval Currently in books, admins only have the option for Final Approval But for example, in cases like when an employee is on leave, we can't just approval one level we only have optionPayment on a past due balance
Scenario: Customer is past due on their account for 4 months. We suspend their billing in Zoho books. Customer finally logs into the portal and enters a new credit card. We associate that cardwith their subscription, which will permit the card to be usedHow to export all line-item descriptions for a specific item in Zoho Books?
I am trying to audit a specific item (“Item X”) that has been invoiced multiple times with different line-level descriptions. Here’s the situation: I am using Zoho Books (Professional). Each invoice may contain the same item but with different descriptionsList of hidden features
Hi Friends, I had another support chat today and low and behold the feature that I wanted just simply needed to be "enabled". I thought I'd share, and maybe see if others had some similar experiences. 1. This one is from 5 ish years ago. I asked if thereHow to change a BAS that has been filed
I have discovered that a group of expense transactions were accidentally placed in a asset account rather than an expense account. As a result I need to adjust the transaction and consequently most of my BAS to correct the error. Because the BAS haveHow to Export PDF with a custom Template
I need to export Sales Order with a Custom Template I have created How can I do it? I see an API to export the PDF but how can I choose which template to choose to generate the PDFAssociate email with a potential or project.
I have a pivotal requirement to associate emails from various suppliers (contacts) with different potentials or projects, on an email by email basis as they come in. This question appears to have been raised before but I cannot find a definitive yes "it can be done". Could anyone please tell me, yes or no. If the later I can stop wasting time and look at alternative crm systems. I would love not to have to do this. Thanks in advance.MTA - BAD IP reputation by outlook/hotmail
Messages to Microsoft email servers are bouncing back due to poor reputation. Message: 4.7.650 The mail server [136.143.188.206] has been temporarily rate limited due to IP reputation. For e-mail delivery information see https://postmaster.live.com (S775)Function with Search Records was working until a few weeks ago, around when "Connected Records" was released
I have a custom function that has been running for nearly a year now, which suddenly stopped working around the time Zoho released the "Connected Records" update. The function is no longer finding the record using the searchRecords function. I've changedIs CRM On Premise available
Hi Zoho team, Can you please let me know that CRM Zoho is available for On Premise as well? Thanks, DevashishHow to sync from Zoho Projects into an existing Sprint in Zoho Sprints?
Hi I have managed to integrate Zoho Projects with Zoho Sprints and I can see that the integration works as a project was created in Zoho Sprints. But, what I would like to do is to sync into an existing Zoho Sprints project. Is there a way to make thatOnline Assessment or any aptitude test
This video is really helpful! I have one question — if I share an assessment form link (through email or with the application form on my career page), how does Zoho Recruit evaluate it? Can a candidate use Google or external help while taking the test,Next Page