Kaizen #81 - COQL API - Part II
Hello everyone!
Welcome to another week of Kaizen!
In continuation to last week's post on COQL API, we will discuss the rest of the field types and their supported operators with detailed examples in this post.
Supported field types and Operators
Please refer to the COQL API - Part I Kaizen post for the first three field types and their supported operators.
4) Boolean
The supported operator for Boolean field is =. You should provide the value as either true or false.
Sample Query
{ "select_query" : "select Product_Name, Product_Code from Products where Product_Active = 'false' limit 2" } |
This sample query selects the Product_Name and Product_Code fields from the Products module where Product_Active is false, and limits the result to only 2 records.
Sample Response:
{ "data": [ { "Product_Code": "SR-001", "Product_Name": "Sigma", "id": "4876876000001036109" }, { "Product_Code": "SR-003", "Product_Name": "Alpha", "id": "4876876000001039006" } ], "info": { "count": 2, "more_records": false } } |
what_Id support in COQL
=, !=, in, not in, is null, is not null are the supported operators. what_Id support is extended only to Tasks, Calls and Events. The what_Id field could be a lead or a contact.
Sample Query
{ "select_query": "select 'What_Id->Leads.Last_Name' from Tasks where (('What_Id->Leads.id' in ('4876876000000900134','4876678000000900987')) AND ('What_Id->Leads.Industry' != 'ERP'))" } |
This sample query queries for the last name of leads associated with tasks where the lead ID is either '4876876000000900134' or '4876678000000900987', and the lead's industry is not 'ERP'.
Sample Response:
{ "data": [ { "What_Id->Leads.Last_Name": "Perry", "id": "4876876000001103044" } ], "info": { "count": 1, "more_records": false } } |
Roles and Profiles support in COQL
COQL supports roles and profiles lookup fields, allowing users to retrieve the owner's profile details such as ID, name etc, and the owner's role details using COQL queries. The supported comparators are =, !=, in, not in, is null, is not null.
Sample Query
{ "select_query": "select Owner.role.id,Owner.role.name,Owner.role.reporting_to, Owner.role.share_data_with_peers, Owner.role.description, Owner.profile.id from Leads where Last_Name is not null LIMIT 1" } |
This query fetches the Profile details and Role details of the owner of one record from the Leads module.
Sample Response:
{ "data": [ { "Owner.role.reporting_to": null, "Owner.profile.id": "4876876000000026011", "Owner.role.name": "CEO", "Owner.role.description": "crm.security.role.admin.desc", "Owner.role.share_data_with_peers": true, "Owner.role.id": "4876876000000026005", "id": "4876876000000333403" } ], "info": { "count": 1, "more_records": true } } |
Alias support
Supported operators : =, !=, in, not in, is null, is not null
Alias support in COQL allows users to simplify complex SELECT field names by giving them custom names or aliases. Using aliases in the SELECT column and ORDER BY class can make column names easier to understand and remember. For instance, What_Id->Leads.Last_Name from our previous example can be given an alias in the SELECT query as follows.
Sample Query
{ "select_query": "select 'What_Id->Leads.Last_Name' AS 'Lead Name' from Tasks where (('What_Id->Leads.id' in ('4876876000000900134','4876678000000900987')) AND ('What_Id->Leads.Industry' != 'ERP'))" } |
Sample Response:
{ "data": [ { "id": "4876876000001103044", "Lead Name": "Perry" } ], "info": { "count": 1, "more_records": false } } |
If you compare this response with the sample response in the what_Id section above, you can see that the key 'What_Id->Leads.Last_Name' is replaced with the Alias 'Lead Name' in here.
Aggregate Function Support in COQL
Aggregate functions in COQL allow you to perform calculations on groups of records in a module. The available aggregate functions include SUM, MIN, MAX, AVG, and COUNT.
1. SUM
Use this function to sum up the values of an aggregate field in a module.
Sample Query 1
{ "select_query":"select SUM(Grand_Total) from Quotes WHERE Quote_Stage = 'Draft' " } |
This Query uses the SUM function to calculate the total value of the Grand_Total field for all quotes in the Draft stage in the Quotes module.
Sample Response:
{ "data": [ { "SUM(Grand_Total)": 1073660 } ], "info": { "count": 1, "more_records": false } } |
Sample Query 2
{ "select_query": "select SUM(Grand_Total), Account_Name.Account_Name AS 'Account Name', Billing_Country from Invoices where ((Status != 'Paid') and ((Billing_Country ='USA') OR (Billing_Country = 'Canada'))) GROUP BY Account_Name.Account_Name, Billing_Country ORDER BY 'Account Name' ASC" } |
This query retrieves the total SUM of the Grand_Total field, along with the Account_Name and Billing_Country from the Invoices module for those records whose Status field is not equal to 'Paid' and the Billing_Country field is either 'USA' or 'Canada'. The results are grouped by the Account_Name and Billing_Country fields and ordered in ascending order based on the 'Account Name' field.
Note that the 'Account Name' field is specified using the AS keyword to provide an alias for the Account_Name.Account_Name field. We have used the alias for the ORDER BY clause.
Sample Response:
{ "data": [ { "Account Name": "SR Systems", "Billing_Country": "USA", "SUM(Grand_Total)": 2430000 }, { "Account Name": "SR Systems", "Billing_Country": "Canada", "SUM(Grand_Total)": 60000 }, { "Account Name": "Zylker systems", "Billing_Country": "Canada", "SUM(Grand_Total)": 378000 } ], "info": { "count": 3, "more_records": false } } |
The response JSON contains the SUM of the Grand_Total fields, Account Name and Billing_Country for each unique combination of Account Name and Billing Country fields that satisfies the filter criteria specified in the query.
2. MAX
Use this aggregate function to find the largest value of a field in a module.
Sample Query
{ "select_query": "select MAX(Amount) AS 'Maximum Deal Amount', Account_Name.Account_Name from Deals where ((Stage = 'Closed Won') AND (Type = 'Existing Business')) Group by Account_Name.Account_Name" } |
This Query selects the maximum Deal Amount for each account from the Deals module where the stage is Closed Won and the type is Existing Business.
Sample Response:
{ "data": [ { "Maximum Deal Amount": 6750000, "Account_Name.Account_Name": "Benton" }, { "Maximum Deal Amount": null, "Account_Name.Account_Name": "King" }, { "Maximum Deal Amount": 945000, "Account_Name.Account_Name": "Morlong Associates" } ], "info": { "count": 3, "more_records": false } } |
3. MIN
Use this aggregate function to find the smallest value of a field in a module. For example, you can use this function to find the Lowest Annual Revenue in the Leads module.
Sample Query: 1:
{ "select_query": "select MIN(Annual_Revenue) from Leads where Annual_Revenue is not null" } |
Sample Response:
{ "data": [ { "MIN(Annual_Revenue)": 348.3 } ], "info": { "count": 1, "more_records": false } } |
4. AVG
Use this aggregate function to find the average value of a field in a module.
Sample Query
{ "select_query": "SELECT AVG(Amount) AS 'Average Deal Amount' FROM Deals WHERE Type = 'New Business'" } |
This query calculates the average deal amount for all new business deals in the "Deals" module.
Sample Response:
{ "data": [ { "Average Deal Amount": 877366.8359999999 } ], "info": { "count": 1, "more_records": false } } |
5. COUNT
Use this aggregate function to count the number of records in a module satisfying a certain criteria. You can use this aggregate function only for number, lookup, and picklist fields.
Sample Query:
{ "select_query": "SELECT COUNT(Product_Category) FROM Products WHERE Product_Active = 'false'" } |
This sample query queries for the total number of inactive products in the Product module. Please be aware that if there are any products with no value for the product category field, they will not be included in the count.
Important points to remember
- COQL keywords are not case-sensitive.
- To prevent ambiguity, the criteria listed in the WHERE clause must be properly enclosed in brackets. If WHERE has more than two criteria such as A, B, and C, use them as either of the following:
- (A and (B and C))
- ((A and B) and C)
- A and (B and C)
- (A and B) and C.
- COQL supports only SELECT queries with WHERE, FROM, ORDER BY, LIMIT, and OFFSET clauses and aggregate functions SUM(), MAX(), MIN(), AVG(), and COUNT().
- The default sort order is ascending based on record ID if ORDER BY is not specified.
- Use the following format for :
- Date : YYYY-MM-DD
- DateTime : YYYY-MM-DDTHH:mm:ss+/-HH:mm
- The default value for LIMIT is 200 and OFFSET is 0.
- A maximum of 10,000 records can be fetched using the API.
- Multi-line fields cannot be used in criteria.
- COQL doesn't support consent_lookup, subform, profileimage, multiselectlookup, fileupload datatypes.
- A maximum of 25 criteria can be included in the WHERE clause.
- A maximum of 50 field API names can be used in the SELECT column of the query.
Data Type | Supported Operators |
text | =, !=, like, not like, in, not in, is null, is not null |
picklist | =, !=, like, not like, in, not in, is null, is not null |
ownerlookup | =, !=, in, not in, is null, is not null |
lookup | =, !=, in, not in, is null, is not null |
date | =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null |
datetime | =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null |
currency | =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null |
boolean | = |
email | =, !=, like, not like, in, not in, is null, is not null |
phone | =, !=, like, not like, in, not in, is null, is not null |
website | =, !=, like, not like, in, not in, is null, is not null |
double | =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null |
bigint | =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null |
integer | =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null |
formula | If the return type is: Decimal/Currency/Date/Datetime : =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null String : =, !=, like, not like, in, not in, is null, is not null Boolean : = |
autonumber | =, !=, like, not like, in, not in, is null, is not null |
We hope you found this post useful. We will meet you next week with another interesting topic.
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!
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
Arose Unity
Haiku Technical Support
Subramanian K
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
Zoho CRM - Potentials Tab
Hi! When I create a Deal (Potentials tab) the header looks like this: After a refresh on the page it looks like this: What should I do so that it is displayed like in the second pic without refresh? Also I have a user that as of recently, cannot see thisGlobal Search placement in the new UI
Having a hard time with the global search placement in the UI redesign. Surely I can't be the only one. Previously global search placement was perfect. A bar at the top/center of the page. Exactly where you would expect it to be. Since the new UI hasZoho 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, AppleZoho Inventory - Composite Items - Assembly - Single Line Item Quantity of One
Hi Zoho Inventory Team, Please consider relaxing the system rules which prevent an assembly items from consisting of a single line item and outputting a quantity of 1. A client I'm currently working with sells cosmetics and offers testers of their productsRotate an Image in Workdrive Image Editor
I don't know if I'm just missing something, but my team needs a way to rotate images in Workdrive and save them at that new orientation. For example one of our ground crew members will take photos of job sites vertically (9:16) on his phone and uploadIntroducing Zoho Creator's 2025 Release Projection 2
Hello Creators! I'm Prakash, from the Creator product management team, and today I'm delighted to unveil our next set of features as part of Release Projection 2 for 2025. With thoughtful analysis and planning, we've curated powerful new capabilitiesCreate custom rollup summary fields in Zoho CRM
Hello everyone, In Zoho CRM, rollup summary fields have been essential tools for summarizing data across related records and enabling users to gain quick insights without having to jump across modules. Previously, only predefined summary functions wereHelp with Filtering Records, HTML Pages, and Automatic File Uploads in Zoho Creator
Hi Zoho Creator Community, I’m building a Zoho Creator application and need guidance on a few features I’ve been struggling with. I want to implement them safely and efficiently, and I’d appreciate any examples, tips, or best practices. I want users toCreator Change History: Ways to improve
Hi Everyone, Recently been working in developing this change history(an idea from Zoho Forms) - unlike forms that you can this with a click but using Creator, we can use "old" keyword. The concept I come up with is to put the result in a table however,How to Send Email from within a custom module (with or without an email template)
It is possible to send an email from the Deals module. However, I can't find a way to send an email from any of our custom modules. I have tried adding an email field to the modules (even though we don't really want one or need it there). That doesn'tConvert invoice from zoho to xml with all details
How to convert an Invoice to XML format with all detailsIn arattai received message can't be deleted
The issue has been noticed in following: arattai app (Android) arattai app (Window) arattai web While the message posted by me may be deleted, the ones received from others can't be. The item <Delete> change to <Report> when the message is a receivedFormat a "return" message
Good day, Is there a way to format the message of the "return" function in CRM deluge script?CRM x WorkDrive: File storage for new CRM signups is now powered by WorkDrive
Availability Editions: All DCs: All Release plan: Released for new signups in all DCs. It will be enabled for existing users in a phased manner in the upcoming months. Help documentation: Documents in Zoho CRM Manage folders in Documents tab Manage filesIs there a way to automatically add Secondary Contacts (CCs) when creating a new ticket for specific customers?
Some of our customers want multiple contacts to receive all notifications from our support team. Is there a way to automatically add secondary contacts to a ticket when our support team opens a new ticket and associates it with an account? This wouldDoes Zoho Learn integrate with Zoho Connect,People,Workdrive,Project,Desk?
Can we propose Zoho LEarn as a centralised Knowledge Portal tool that can get synched with the other Zoho products and serve as a central Knowledge repository?Shopify Extension no longer working in CRM
Zoho CRM and Extension are no longer working properly. We have used Shopify Basic for years. All of the sudden it stopped working, then started working again, and now no longer working again. There are several ways for Personal Information to sync fromAdd Comprehensive Accessibility Features to Zoho Writer
Hello Zoho Writer Team, We hope you are doing well. We would like to submit a feature request to enhance Zoho Writer with a full set of accessibility tools, similar to the accessibility options already available in the Zoho Desk agent interface. 🚧 CurrentIf there’s a batch at the end, the ‘mark as inactive’ function doesn’t work.
when my batch has a date to batch end and I "mark as inactive" he batch does not obey the status and reactivates the batch.Changing an agents email address
How do you change an agent's email address? I keep getting a red circle even though I am an admin. And on one of my agents he has two email addresses? How is that possible?Having Trouble Opening The Candidate Portal
Recently am having trouble opening the Candidate Portal. It keeps loading but cannot display any widgets. Tried Safari, Chrome and Edge. Non of them work. Please solve the problem ASAP.Signature issue
Problem: The signature does not appear when replying or forwarding an email. solve issue: settintgs/Signature Check option place a signature above the content with quotation marksFeature Suggestion for Zoho Websites – Inspired by Squarespace Systems
Dear Zoho Team, I’m a Zoho user and also a Squarespace Platinum Circle member, and I recently noticed the launch of Zoho Websites in India. I wanted to share some ideas for features that could enhance the platform for professional users and agencies.Customer happiness customisation
Hi, I was wondering if it's possible to somehow customise to whom and when customer happiness request is sent? Can you enable it only for selected tickets for example based on workflow or any other criteria (customer name, tag or anything else)? AlsoCustom View - Sort by Custom Field
I created a custom field for our Engineering team to know which tickets to work first by numbering them. I created a custom view to general data which includes the Engineering Priority. However, I cannot sort the Engineering Priority column ascendingAdvanced Deluge help needed with custom function and COQL limits
I have a scheduled function that takes a snapshot of specific metrics and saves them to a custom module. However I'm struggling with the limits of COQL. I attach my full code, but an example of the issues I'm having is that it's erroring with a 'LimitCliq iOS can't see shared screen
Hello, I had this morning a video call with a colleague. She is using Cliq Desktop MacOS and wanted to share her screen with me. I'm on iPad. I noticed, while she shared her screen, I could only see her video, but not the shared screen... Does Cliq iOS is able to display shared screen, or is it somewhere else to be found ? RegardsDefault Sorting on Related Lists
Is it possible to set the default sorting options on the related lists. For example on the Contact Details view I have related lists for activities, emails, products cases, notes etc... currently: Activities 'created date' newest first Emails - 'createdMicrosoft Teams now available as an online meeting provider
Hello everyone, We're pleased to announce that Zoho CRM now supports Microsoft Teams as an online meeting provider—alongside the other providers already available. Admins can enable Microsoft Teams directly from the Preferences tab under the MeetingsMass (Pull back) (Close WOs) (Close APs)
Hi develop team. Just idea because Zoho FSM is great but its too manual. we have a lot of task to do every day and the system needs to have more features to automatic our tasks. I need to close several WOs, APs and Pull Back per day. Please we need massOutdated state in mexico
Hello Zoho team, the drop down to add the state for customers, when they introduce their state in mexico has a city named “Distrito Federal” that name changed many years ago to “ciudad de mexico”. could you please update this so my clients can find theZoho Indeed Intergation not pulling candidate details
We have recently integrated zoho with indeed. Prior to this our candidates came into the candidates tab via the zoho.resumes email address from indeed and it pulled through the candidates mobile number, and majority of the time a postcode. However sinceCustom View of tickets created today
How can I create a custom view that list all my ticked created in the current date? Currently, if I select the "Created Time" criteria, the "Current Time" option does not work as today. Actually, I don't know how it works this "Current Time".Lifecycle Reports
From data to decisions: A deep dive into ticketing system reports A lifecycle report captures and visualises the sequential states that a ticket undergoes across its lifespan. For instance, when a customer submits a support ticket for a faulty product,Zoho Forms - Print Button on Forms
Hi Forms team, I'm replicating a form for a client which is currently based on JotForm. I noticed that at the end of the form there is a button to print the completed form. I thought this would be something worth sharing and a nice to have in Zoho Forms.Putting Watermark on Zoho Sheet
Can this be done?Validation function not preventing candidates under 18 or over 30 from submitting the web form
Hello everyone, I’m trying to create a validation rule for the Candidate Webform in Zoho Recruit. I added a custom field called “Date of Birth”, and I want to make sure that candidates cannot submit the form unless their age is between 18 and 30 years.Report to know the history of certain Tickets on Desk
Hi there guys, As the title implies we're wondering if there's any way to get some kind of Report that allows us to check the History of various Tickets at the same time since as of today if we want to know that we have to check them 1 by 1 which is notShared Mailboxes Disappeared
Zoho Mail users in our company haven't been able to see their Shared Mailboxes for the past few hours. I've checked with App and Web Access, but they can't access them. When I send emails to their addresses, I get no error messages. They're still visibleIMAP Migration from Gmail
I have been trying to import my email from a Gmail server and keep receiving the following error. I have reduced the security, activated imap and no improvement. The link to the Google support item has not helped. Unable to connect to your account. PleaseNext Page