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 #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 FAQsKaizen #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
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
get file api is returning Junk data
I am working on extension development where at one point I need to retrieve attachments on records I found out I can use only invokeconnection and not invokeurl in extension development The invoke connection returns the image in raw binary format. WhenNeed help with message box
End user updates many records at once. Each have unique serial number. They want a confirmation box that says starting number, ending number and qty. Is there any way to do this? I been searching and asking support but seems like no great solution.[Webinar] The Transformative Power of Gen BI
Traditional decision-making tools are no longer enough. The integration of generative AI into business intelligence (BI) is proving to be a true game changer—enabling businesses to make faster, smarter, and better informed decisions. Early adopters ofBackorder process review - Automating Removal of Sales Order from "On Hold" When PO is Received
Hello Zoho Inventory Team, Currently, sales orders in On Hold status are released only when the bill for the purchase order is created. In our workflow, it would be much more efficient if the sales order could automatically move out of On Hold as soonHow to access the saved Query using API?
I have created a query in setting. Is it possible to access it from a API? What's the endpoint? I tried /v8/queries/important_deals but didnt' work.création d'une base de données
base de donnee d'un lycéeUser Session variables to recall after form submit & redirect to menu
Hey, Forgive me if this is a simple answer I have overlooked. I have built a big onboarding process for my SAAS platform which is built and managed by an external Dev team. I am hoping to embed my new creator app on the SAAS platform. I have a menu pageZoho Writer Merge Template with data from Zoho Analytics Views
Hello, Is there any way to pull information from Zoho Analytics and put it into a Merge Template? I am trying to create a bulk export of one report that filters on a field to create a single document for each of the filter fields.Having to enter bill before stock shows as available.
Hi, Am I right in thinking you must create a bill from the purchase order receipt before the goods are available for shipping?Agent Availability Report
From data to decisions: A deep dive into ticketing system reports Businesses need to track when their support agents check in and check out from work. This report, titled Agent Availability, is one of the static reports that helps managers track the numberWriting by Hand in "Write" Notes
Hi there! I just downloaded this app a few moments ago, and I was wondering if there was a way to write things by hand in "Write" mode instead of just typing in the keyboard. It would make things a bit more efficient for me in this moment. Thanks!AI-driven construction analytics using Zoho Projects Plus
Construction projects thrive on timelines; if a project is completed on or before the expected time, it makes headlines, but if it gets delayed, the company’s credibility will be at risk. Such projects require precise planning and constant monitoring,Canadian payroll
Hello ZOHO, is any updates on when payroll for Canada will be available?Unified customer view: Bringing product intelligence into your CRM data
For businesses today, applications and digital interfaces are the main touch points in the customer journey. While Zoho CRM data already tells who your customers are and what they buy, Zoho Apptics tells you how they interact and engage with your product.Zoho Creator SaaS app with multiple client AND multiple users
I am close to finalizing a Zoho Creator app that will be used by different companies to manage sale info, and each company can have multiple users using the app. Things were going perfectly until I tried a search on a report. I use a database field in each table to separate the data for each company, which works great. BUT when I do a search, I get to see ALL the data. This means that Company A could easily see the data from Companies B and C via the built-in search. Is there a way to set up theProject Change Orders and Additions
We are in the process of migrating from QuickBooks Online to Zoho Books. We have Zoho One and like the ability to sync all of our data across everything. And I like that projects work in a way that's less dumb than QuickBooks. I'm trying to figure outTrack online, in-office, and client location meetings separately with the new meeting venue option
Hello everyone! We’re excited to announce meeting enhancements in Zoho CRM that bring more clarity and structure to how meetings are categorized. You can now specify the meeting venue to clearly indicate whether a meeting is being held online, at theCreate 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 werePossible to send Zoom AI Companion transcripts and summaries to contacts in CRM?
Title says it all. Is it possible to send Zoom AI Companion transcripts and summaries to contacts in Zoho CRM?Introducing segments for targeted user engagement
Hello everyone, We’re excited to announce Segments in Zoho Apptics, a new way to group users based on their behavior, preferences, and app activity. With Segments, you can deliver more meaningful, timely, and relevant experiences to your users. When combinedHow do I edit the Calendar Invite notifications for Interviews in Recruit?
I'm setting up the Zoho Recruit Interview Calendar system but there's some notifications I don't have any control over. I've turned off all Workflows and Automations related to the Calendar Scheduling and it seems that it's the notification that is sentPosibility to add Emoticons on the Email Subject of Templates
Hi I´ve tried to add Emoticons on the Subject line of Email templates, the emoticon image does show up before saving the template or if I add the Emoticon while sending an Individual email and placing it manually on the subject line. Emoticons also showClarity on extended contract status
Clarity on “extended” status- How does Zoho “extend” a contract? E.g. if client extends by 1 month, can the “end date” be adjusted accordingly in Zoho and tagged as “Extended” with a clear audit trail that also captures the client’s email? Note- EmailCustomizing contract status
Can we customize/add/remove status on Zoho contracts ourselves or does Zoho needs to do this? Context- There is a long list of status on Zoho but not all of them are relevant for us . There are few status which are missing and needs to be added. E.g-Set Custom Business Calendars and Holidays for Global Teams
Managing a project across diverse teams means accounting for more than just tasks and deadlines; it means acknowledging how and when each team actually works. Users might follow different working days or observe region-specific holidays that cannot be[Integration edition] Deluge learning series - Integrations between Zoho apps and Third-party services with Deluge | 28 August 2025
We’re excited to welcome you to the Deluge learning series: Integration edition! The Integration edition will run for three months: Session 1 – Integrating Zoho Apps with Deluge using inbuilt integration tasks Session 2 – Integrating Zoho Apps with DelugeCritical Vulnerability in all major password manager plugins - is Zoho Vault affected?
Hi Zoho, a security researcher found a critical clickjacking vulnerability in all major browser password managers: DOM-based Extension Clickjacking: Your Password Manager Data at Risk | Marek Tóth Is Zoho Vault affected as well? Apparently the Vault extensionIntroducing SecureForms in Zoho Vault
Hey everyone, Let’s face it—asking someone to send over a password or other sensitive data is rarely straightforward. You wait. You nudge. You follow up once, twice—maybe more. And when the information finally arrives, it shows up in the worst possibleTables from ZohoSheets remove images when updated from source
I have a few tables from a ZohoSheet in a ZohoWriter document that will remove the images in the cells when I refresh from the source. The source still has the images in the table when I go to refresh. After updating from the source, as you can see theChange eMail Template for Event-Invitations
Hello ZOHO-CRM Team How I can change the eMail Template for Event-Invitations? I work with the German Version of the Free Version. I know how I can modify eMail alerts or Signature Templates, but where I can other eMails modify you send out? Thank you for your answer. Regards, JuergInventory Adjustments
Hi, How to transfer the material from one head to another ? Like materials purchased for manufacturing the laptop need to transfer from consumption inventory (Quantity of raw materials reduced) to destination inventory ( Quantity of Laptop increased)Need Your Insights
Hi Zoho, I'm confused why the flow only sends to one output. setVariable15 is from a list. It doesn't consider the 2nd entry. Any thoughts?Map Dependency Fields - How can can I limit picklist values from a multi-select Parent field?
I know the "Map Dependency Fields" feature doesn't allow a multi-select parent field. However, I need the functionality. Can I do it through a deluge function, and would it still support color coding picklists? We need the color coding for quick scanningDYK 1 - Color Palette for Enhanced Visual Identification of Status
Introducing the Did You Know series of posts. The goal of this series is to familiarize users with certain features or enhancements in Zoho Projects that may not be evident at first glance. The first post in this series deals with color palettes for indicatingInvalid field in the COQL query
Dear Zoho Support! I believe that you already helped me with a similar problem a few years ago. One of my clients has a custom field named "LOB" in the "Deals" Module (see the field's metadata below). The COQL query using this field: : "select id, Deal_Name,Automating Employee Birthday Notifications in Zoho Cliq
Have you ever missed a birthday and felt like the office Grinch? Fear not, the Cliq Developer Platform has got your back! With Zoho Cliq's Schedulers, you can be the office party-cipant who never forgets a single cake, balloon, or awkward rendition ofTransferring domain registration to new registrar and switching email hosting at the same time?
I need to transfer an existing domain uv cure adhesive that's currently with SiteGround to Porkbun. I also need to move the existing custom email addresses from SiteGround to Zoho Mail. I'm not sure if I should transfer the domain first and then tackleSplit deposits
Can Zoho do split deposits. One deposit, two checks for two separate invoices from different customers. This is one of the most common tasks I can imaging. When I mark the two invoices paid, there are two deposits in bank register. When I try to match,Feedback: Streamlining Note Management in Zoho Notebook
Dear Team/Support, I would like to share some feedback regarding the note management system that could help improve usability and accessibility for users like myself. Notebook 1 (screenshot attached): Currently, the system does not allow selecting andDeactivate Desk Contact without Deleting Contat
We have a client who has multiple tenants for regulatory purposes, and as such, has a few users that have email addresses in both tenants. They've then emailed into the ticketing system, so we have multiple contacts (no big deal, we want to keep theirNext Page