Kaizen 207 - Answering Your Questions | Advanced Queries using COQL API
As part of Kaizen #200 milestone, many of you shared topics you would like us to cover, and we have been addressing them one by one over the past few weeks. Today, we are picking up one of those requests - a deep dive into advanced queries using Zoho CRM’s COQL APIs.
When you start building complex applications on top of Zoho CRM, you may feel that basic record fetch APIs like GET records are not enough. Business logic often demands far more, like combining data from multiple modules, applying conditional filters, grouping results, or even running aggregate calculations.
This is exactly where COQL (CRM Object Query Language) shines. If you have already used COQL for straightforward queries, this post will help you go further.
COQL Recap - Why Use It?
COQL is your go-to when you need more than what the standard GET Records API can provide. It gives you:
- SQL-like flexibility for querying CRM data.
- Access to related records across multiple modules using lookups and joins.
- Powerful filtering, aggregation, and sorting well beyond simple searches.
In short: use COQL when you want fine-grained control over results, complex reporting logic, or performance improvements in large data environments.
Understanding COQL Queries
The Query API (COQL) is best when you need flexible record retrieval without chaining multiple API calls or creating custom views.
Instead of building and maintaining complex filters in the UI, you can describe your data needs in one SQL-like query.
For example, with COQL you can:
- Fetch all products within a certain price range that also have a 5-star rating, sorted by price.
- Filter deals based on details from a related module, like the Vendor’s status.
- Pull a precise slice of data on-demand without altering CRM views.
What kind of queries are supported?
COQL currently supports only the SELECT statement, which lets you pick fields, apply conditions, sort results, and control pagination.
A typical query looks like this:
SELECT {field_api_names} FROM {module_api_name} WHERE {field_api_name} {comparator} {value} GROUP BY {field_api_name} ORDER BY {field_api_name} ASC/DESC LIMIT {limit} OFFSET {offset}
|
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 offset 10" } |
The above query retrieves five Leads who have not been contacted, ordered by Final_Score, skipping the first 10(OFFSET). You can also use the shorthand LIMIT offset, limit:
{ "select_query" : "select Last_Name, First_Name, Mobile, Final_Score from Leads where Lead_Status = 'Not Contacted' order by Final_Score desc limit 10, 5" } |
Supported Data Types & Operators
COQL supports multiple field types, each with dedicated operators:
Field Type | Supported Operators |
Text, Picklist, Email, Phone, Website, Autonumber | =, !=, 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 | = |
Formula | If the return type is:
|
Queries can be further refined with sorting (ORDER BY) and pagination using LIMIT and OFFSET.
Aggregate functions
COQL supports aggregate functions to summarize data:
- SUM(field) – total of numeric values
- MAX(field) – largest value
- MIN(field) – smallest value
- AVG(field) – average value
- COUNT(*) – number of records matching criteria
Please note that aggregate functions are supported only for numeric data types such as number, decimal, currency, etc.
Wildcards
The % character is supported with the LIKE operator for flexible text matching:
- '%tech' → values ending with “tech”
- 'C%' → values starting with “C”
- '%tech%' → values containing “tech”
With these building blocks, you can already express a wide range of queries. Let’s now move into advanced scenarios where COQL really shines.
Beyond basics: COQL Patterns for real-world scenarios
Once you are comfortable with the basics of COQL, you can start combining them into more powerful query patterns. Some of these go beyond simple filtering and field selection, helping you minimize API calls, handle relationships, and emulate unsupported features.
1. Advanced Filtering & Conditions
Beyond equality, COQL supports operators like LIKE, IN, BETWEEN, and date comparisons.
Example: Fetch Leads from the IT or Finance industry created in the year 2025.
{ "select_query": "select Full_Name, Industry from Leads where Industry in ('IT', 'Finance') and Created_Time between '2025-01-01T00:00:00+05:30' and '2025-12-31T23:59:59+05:30'" } |
Use case: Run targeted campaigns or segment leads for analysis without multiple API calls.
2. Combining Multiple Conditions
You can query diverse conditions, combining exact, partial matches, and set memberships.
Example: Pre-qualified leads in target industries with company names containing “zylker”:
{ "select_query": "select First_Name, Last_Name from Leads where (((Lead_Status = 'Pre-Qualified') and (Company like '%zylker%')) and Industry in ('Technology', 'Government/Military'))" } |
Use case: Sophisticated audience segmentation or analytics for campaigns.
3. Fetching related records and their fields using Joins (Dot notation)
COQL allows you to retrieve related records efficiently by navigating lookup relationships using dot notation. This makes it possible to pull in contextual information across modules without chaining multiple API calls.
Single-level join: Fetch contacts and their account names, excluding a specific account:
{ "select_query": "select Last_Name, First_Name, Account_Name.Account_Name, Owner from Contacts where (Account_Name.Account_Name != 'Zylker') limit 2" } |
Sample use case: Retrieve all contacts along with their associated account names while excluding certain accounts (e.g., competitors or internal test accounts). This avoids multiple queries across modules and helps in cleaner campaign targeting.
Hierarchical / nested join: Fetch contacts whose accounts have a parent account named “Kings”:
{ "select_query": "select Account_Name, Account_Name.Parent_Account.Account_Name from Contacts where Account_Name.Parent_Account.Account_Name = 'Kings' limit 5" } |
Sample use case: Easily retrieve multi-level relationships such as parent-child accounts for reporting, territory alignment, or hierarchical sales analysis.
Multi-Level Join with Extended Lookup : Fetch contacts, their accounts, the parent accounts of those accounts, and the owner of the parent account:
{ "select_query": "select Last_Name, First_Name, Account_Name.Account_Name, Account_Name.Parent_Account, Account_Name.Parent_Account.Owner AS 'Parent Account Owner', Owner from Contacts where (Account_Name.Account_Name != 'Zylker') limit 2" } |
Sample use case: Useful in complex account management and escalation scenarios where responsibility spans multiple levels. For instance, sales managers may want to see not just the contact and their account, but also which parent account owner is responsible for the overall relationship. These types of queries are helpful in large enterprises with layered ownership structures.
4. Using Subqueries to detect missing relationships
COQL supports subqueries to filter based on related module data or detect missing relationships.
Example: Find contacts whose accounts have no closed deals:
{ "select_query": "select Full_Name, Email from Contacts where Account_Name not in (select Account_Name from Deals where Stage = 'Closed Won')" } |
Use case: Identify potential follow-ups, audit compliance, or uncover opportunities.
These types of queries are handy for:
- Sales follow-ups – identify contacts from accounts that haven’t yet converted.
- Compliance checks – ensure certain accounts meet deal requirements.
- Pipeline building – target untouched accounts for new opportunities.
By combining subqueries with conditions like NOT IN, COQL makes it easy to surface hidden opportunities that would otherwise require multiple API calls and custom logic.
Note: Subqueries in COQL can return a maximum of 100 records. If the inner query has more than 100 matches, any extra records are ignored. This means you may get incomplete results in larger datasets. In such cases, it is better to redesign the query using joins or multiple API calls, which can handle broader datasets without this limit.
Advanced COQL Querying: Real-World Patterns
Once you’ve mastered filters, joins, and subqueries, you can combine them for advanced business logic.
1. Filtering Deals Based on Account Attributes
Generic Use Case:
You want to prioritize deals connected to high-value accounts that meet specific business criteria, such as strong credit ratings or key industries.
Retrieve all deals for accounts that:
- Have a high credit rating (>750)
- Belong to a specific industry, e.g., Communications
COQL Query:
{ "select_query": "SELECT Deal_Name, Amount, Account_Name, Contact_Name.Email FROM Deals WHERE Account_Name in (SELECT id FROM Accounts WHERE Credit_Rating > 750 AND Industry = 'Communications') AND Stage != 'Closed Won'" } |
Dynamically filter deals by account attributes while fetching related contact details in a single query.
2. Emulating MIN/MAX for Date fields
When working with date fields in COQL, a common analytical need is to compare records against the latest date from a related subset. For example, identifying deals that closed before the most recent high-value deal.
Intuitively, one might try to use aggregate functions like MAX() on a date field in a subquery, such as:
{ "select_query": "SELECT Deal_Name FROM Deals WHERE Closing_Date < (SELECT MAX(Closing_Date) FROM Deals WHERE Amount > 500000)" } |
Workaround: Using Subquery with ORDER BY and LIMIT
Instead of MAX(), the recommended COQL approach leverages sorting and limiting the result set to a single latest date within a subquery:
{ "select_query": "select Deal_Name from Deals where Closing_Date < (select Closing_Date from Deals where Amount > 500000 order by Closing_Date desc limit 1)" } |
How this works:
The inner subquery fetches the single most recent Closing_Date where deals exceed $500,000, ordering by date descending and limiting to one record. The outer query then retrieves all deals closed before that date.
This pattern mimics the MAX() date comparison in a manner supported by COQL’s current capabilities. You can apply the same approach with ascending sort order to emulate MIN() as well.
3. Combining Multiple Subqueries for Complex Business Logic
Real-world CRM scenarios often require filtering records based on multiple interconnected conditions across different modules. Consider this sales intelligence use case: you want to identify Contacts who are:
- Associated with Accounts that have annual revenue greater than $1000000000000
- Connected to Deals that were created in the previous quarter
{ "select_query": "SELECT First_Name, Last_Name, Email, Account_Name.Account_Name FROM Contacts WHERE Account_Name in (SELECT Account_Name FROM Deals WHERE Created_Time >= '2025-06-01' AND Account_Name in (SELECT id FROM Accounts WHERE Annual_Revenue > 1000000000000)) " } |
Query Breakdown:
- Innermost subquery: (SELECT id FROM Accounts WHERE Annual_Revenue > 1000000000000) identifies high-revenue accounts
- Middle subquery: (SELECT Account_Name FROM Deals WHERE Created_Time >= '2022-07-02T15:18:31+05:30' AND Account_Name in (...)) filters for accounts with deals created after the specified date that are also high-revenue accounts
- Main query: Retrieves contact details for all contacts associated with these filtered accounts while fetching the related account names via JOIN
This pattern finds contacts from high-value accounts that have had recent deal activity, combining temporal filtering with revenue-based account qualification in a single efficient query.
Dynamic Account and Deal Performance Analysis
Imagine you need to find all Leads from industries where accounts have historically closed high-value deals (over $100K) and those leads have "Hot" ratings.
This requires filtering leads based on:
- Industry performance from accounts with successful deals
- Lead rating criteria
- Retrieving lead details with industry information
{ "select_query": "SELECT First_Name, Last_Name, Lead_Source, Company, Industry FROM Leads WHERE Industry in (SELECT Industry FROM Accounts WHERE id in (SELECT Account_Name FROM Deals WHERE Amount > 100000 AND Stage = 'Closed Won') GROUP BY Industry) AND Rating = 'Hot'" } |
What makes this powerful:
- Inner subquery (SELECT Account_Name FROM Deals WHERE Amount > 100000 AND Stage = 'Closed Won') identifies accounts with successful high-value deals
- Outer subquery (SELECT Industry FROM Accounts WHERE id in (...) GROUP BY Industry) gets the industries of those successful accounts
- Groups by Industry to get unique industry values and avoid duplicates
- Main query finds leads in those proven successful industries with "Hot" ratings.
Note: Both subqueries in this query are limited to 100 records each. If either the Deals or Accounts module returns more than 100 matches, the additional records are silently ignored. This can lead to incomplete results when working with larger datasets. For scenarios where the inner queries are expected to return more than 100 records, redesign the query using joins or break it down into multiple API calls for complete coverage.
Conclusion
By going beyond simple record fetches, COQL gives you the power to do true analytics and querying. By mastering patterns that range from straightforward joins to complex multi-module subqueries, you can consolidate multiple API calls into a single query, reduce complexity, and streamline performance. At the same time, dynamic filtering across modules facilitates richer business logic, while relationship-aware queries let you build automations that can handle real-world exceptions with precision.
As you implement these patterns, remember that the most powerful COQL queries often combine multiple techniques: JOINs for data enrichment, subqueries for dynamic filtering, and careful aggregation for performance optimization. However, it is equally important to understand COQL's limitations too. Being aware of these limitations will help you design effective workarounds and choose the right approach for your specific use cases. For a comprehensive list of limitations, please refer to our COQL Limitations documentation.
Start with simpler patterns and gradually build complexity as your use cases demand. The investment in mastering COQL will pay you with cleaner code base, better performance, lesser credit consumption, and more sophisticated CRM functionality.
We hope that you found this post on COQL useful. If you have any queries or need further assistance, please feel free to comment below or email us at support@zohocrm.com. We are here to help!
Topic Participants
Anu Abraham
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
Recent Topics
New in Zoho Chat : Search for contacts, files, links & conversations with the all new powerful 'Smart Search' bar.
With the newly revamped 'Smart Search' bar in Zoho Chat, we have made your search for contacts, chats, files and links super quick and easy using Search Quantifiers. Search for a contact or specific conversations using quantifiers, such as, from: @user_name - to find chats or channel conversations received from a specific user. to: @user_name - to find chats or channel conversations sent to a specific user. in: #channel_name - to find a particular instance in a channel. in: #chat_name - to findAggregating the First Value in the Group By of a dataset
Hi I am trying to get the following Aggregate Formula to work in my chart, but cannot seem to get the right format. I have a series of data that I am running an include_groupby and want to SUM only a column in the first row of each group. So for example.New in Cadences: Option to Resume or Restart follow-ups when re-enrolling records into a Cadence, and specify custom un-enrollment criteria
Managing follow-ups effectively involves understanding the appropriate timing for reaching out, as well as knowing when to take a break and resume later, or deciding if it's necessary to start the follow-up process anew. With two significant enhancementsIs it possible for contacts to "Re-enter" a workflow in Zoho Campaign?
We are currently working on a way to automatically add users to from one list to other lists based on specific criteria, but can't seem to find a native way of doing this so we are trying to use Workflows to do this. So, for example, if a user's status is set to "Active," then they should be added to the list "Active Users." If the same user's status is then set to "Paused," they should be added to the list "Paused Users" and removed from the list "Active Users." This works fine for the first goAdmin Control Over Profile Picture Visibility in Zoho One
Hello Zoho Team, We hope you are doing well. Currently, as per Zoho’s design, each user can manage the visibility of their profile picture from their own Zoho Accounts page: accounts.zoho.com → Personal Information → Profile Picture → Profile PictureHow can I track which zoho users are actively using Zoho CRM
I have several licenses of Zoho CRM. We now need to add a new user. I could purchase a new license, but before I do, I would like to see if any of our existing users are not actively using the license assigned to them. How can I determine the activitySeriously - Create multiple contacts for leads, (With Company as lead) Zoho CRM
In Zoho CRM, considering a comapny as a lead, you need us to allow addition of more than one contact. Currently the Lead Section is missing "Add contact" feature which is available in "Accounts". When you know that a particular lead can have multipleTrack Zoho Campaign and Workflow sales impact
I am attempting to measure the performance of our marketing workflows and campaigns by comparing the date each campaign was sent to a contact with the purchase date of the contact. For example, if Contact A was sent Email A on 9/1 and made a purchaseZoho / Outlook Calendar sync
The current Marketplace -> Microsoft -> Meetings integration needs 2 changes. 1. The current language for the Two-Way sync option should be changed. It currently states, "Sync both your Zoho CRM Calendar and Office 365 Calendar meetings with each other."Tables for Europe Datacenter customers?
It's been over a year now for the launch of Zoho Tables - and still not available für EU DC customers. When will it be available?Field Dependency Not Working on Detail Page in Zoho Desk
Hi Support Team, I’ve created field dependencies between two fields in Zoho Desk, and they are working correctly on the Create and Edit layouts. However, on the Detail page, the fields are not displaying according to the dependencies I’ve set — they appearWhat is a line break code for zoho?
Hi, I am archiving data by adding values from a single line field from one form to a multi-line field in another form. So I need a code/function that starts a new line on that multi-line field so it does not just keep adding it on the same line. Example, doing something like this means that it will be on a same line. archive.field1 = archive.field1 + input.Field1 I need a code so the input.Field1 can just start on the next line. Instead of "value 1, 2,3,4,5" It will be: "1 2 3 4 etc.". somethingAutomatic Project Owner change
Is there a way to change Project Owner automatically once a specific Milestone in a project is marked as completed. Different Teams are working on projects in our Org, they have their own Milestones to complete and so we transfer the project from teamButton to add product to cart
Is there a way to have a button on a page, that when clicked, will add Qty 1 of a product to the cart?Problem with Submit Button Design
I have made a template to apply to my forms and under the button controls, I have it set to "standard" and yet it's still filling the container. This is super frustrating and looks weird. Why do we not have full control over button size? How can I fixZoho CRM- Authorize your Microsoft Teams account issue
Hi, I tried to link Zoho CRM with Teams and I got the following message: Clicking "Authorize now" sent me to the following page, Microsoft tried to start a session but, after 3 seconds the page closed and nothing happened. I get the same message eachPassing the CRM
Hi, I am hoping someone can help. I have a zoho form that has a CRM lookup field. I was hoping to send this to my publicly to clients via a text message and the form then attaches the signed form back to the custom module. This work absolutely fine whenIs there a way to associate an email in ZOHO Main to a Vendor record in ZOHO CRM
My situation is as below, I have a vendor in ZOHO CRM lets say "Vend A" and an associated contact, "Cont A" If Cont A sends me an email using the email I've registered in the contact record the standard OOTB email sync will work. But the vendor has someAdding a developer for editing the client application with a single user license
Hi, I want to know that I as a developer I developed one application and handed over to the customer who is using the application on a single user license. Now after6 months customer came back to me and needs some changes in the application. Can a customerKaizen #207 - Answering your Questions | Advanced Queries using COQL API
Hi everyone, and welcome to another Kaizen week! As part of Kaizen #200 milestone, many of you shared topics you would like us to cover, and we have been addressing them one by one over the past few weeks. Today, we are picking up one of those requestsPrésentation de SecureForms dans Zoho Vault
Soyons francs : demander à quelqu’un de transmettre un mot de passe ou des informations sensibles n’est jamais une tâche facile. On attend, on relance, parfois de nombreuses fois. Et quand l’information arrive, elle se retrouve souvent dispersée dansIntroducing Connected Records to bring business context to every aspect of your work in Zoho CRM for Everyone
Hello Everyone, We are excited to unveil phase one of a powerful enhancement to CRM for Everyone - Connected Records, available only in CRM's Nextgen UI. With CRM for Everyone, businesses can onboard all customer-facing teams onto the CRM platform toZoho CRM in Microsoft Power Automate Custom Connector
Hi everyone, I’m building a Power Automate flow that integrates Microsoft Bookings with Zoho CRM. The goal is to automatically create a meeting (event) in Zoho CRM whenever a new appointment is booked via Microsoft Bookings. To achieve this, I createdGranular Email Forwarding Controls in Zoho Mail (Admin Console and Zoho One)
Hello Zoho Mail Team, How are you? At present, the Zoho Mail Admin Console allows administrators to configure email forwarding for an entire mailbox, forwarding all incoming emails to another address. This is helpful for delegation or backup purposes,Sales order & purchase order item links for item details
This is fantastic for checking lots of things, I use it a lot. It would be great to see it extended to invoices & bills On another note, may as well throw in my favourite whinge ..... Wish you guys would get the PO receive differences sorted urgently,Custom Fonts in Zoho CRM Template Builder
Hi, I am currently creating a new template for our quotes using the Zoho CRM template builder. However, I noticed that there is no option to add custom fonts to the template builder. It would greatly enhance the flexibility and branding capabilities ifProblem with Email an invoice with multiple attachments using API
I have an invoice with 3 attachments. When I send an email manually using the UI, everything works correctly. I receive an email with three attachments. The problem occurs when I try to initiate sending an email using the API. The email comes with onlyZoho Workdrive - Communication / Chat Bar
Hi Team, Please consider adding an option to allow admins to turn on or off the Zoho Communication Bar. Example of what I mean by Communication Bar: It's such a pain sometimes when I'm in WorkDrive and I want to share a link to a file with a colleagueIntroducing Profile Summary: Faster Candidate Insights with Zia
We’re excited to launch Profile Summary, a powerful new feature in Zoho Recruit that transforms how you review candidate profiles. What used to take minutes of resume scanning can now be assessed in seconds—thanks to Zia. A Quick Example Say you’re hiringKaizen #190 - Queries in Custom Related Lists
Hello everyone! Welcome back to another week of Kaizen! This week, we will discuss yet another interesting enhancement to Queries. As you all know, Queries allow you to dynamically retrieve data from CRM as well as third-party services directly withinNeed the ability to have read only fields on a form.
There needs to be functionality in Creator that allows a field on a form to be read only. Most screen building software applications have this capability. I know you can hide certain fields from specific users and that you can also make the whole form read only but that's not the functionality I need. I want to be able to create a form where certain fields are editable and other are for display purposes only (read only). For example if the form was displaying information on an item that the userReverse payment on accidentally closed invoice.
An invoice was closed accidentally with the full payment added. However, only partial payment was paid. How can I reopen the invoice and reverse this to update it to show partial payment?New integration: Track booking page appointments in Google Analytics 4
Hello all, Greetings from the Zoho Bookings team! We’re excited to introduce our new Google Analytics 4 (GA4) integration designed to help you track booking activity, understand customer behavior, and measure marketing performance, all in one place. WhatZoho Books Sandbox environment
Hello. Is there a free sandbox environment for the developers using Zoho Books API? I am working on the Zoho Books add-on and currently not ready to buy a premium service - maybe later when my add-on will start to bring money. Right now I just need aHow to list emails in a folder, e.g. Inbox, on multiple pages when using Zoho mail webpage?
Something as shown in the figure. There are totally 50 emails in Sent folder. If "Mail per page" equals 20, then the Sent folder is split into 3 pages. When I wander through Sent folder, I can just select a specific page to jump to. BTW, it seems thatZoho Calendar soft bounce on @hotmail.com and @yahoo.com email addresses
Hello, our Zoho calendar recently does not send the calendar invites to emails with hotmail and yahoo domains and comes back with a "soft bounce". other domains like Gmail works fine. Also sending "email" to the same emails to the above domains work wellIMAP Server not responding.
Trying to connect a phone via IMAP and getting "imap.zoho.com not responding." Is the server down, for maintenance or otherwise? I've tried this on two different devices and got the same error on both.Elevate your CX delivery using CommandCenter 2.0: Simplified builder; seamless orchestration
Most businesses want to create memorable customer experiences—but they often find it hard to keep them smooth, especially as they grow. To achieve a state of flow across their processes, teams often stitch together a series of automations using WorkflowERROR CODE :512 - 5.4.4 DNS error:NXDOMAIN.
Suddenly we cant send mail, we are getting this error for all outbound mail to multiple domains.Option to Disable Knowledge Base Section in Feedback Widget Popup Hello Zoho Desk Team
Hello Zoho Desk Team, How are you? We are actively using Zoho Desk and would like to make more use of the Feedback Widget. One of the ways we implement it is through the popup option. At the moment, the popup always displays the Knowledge Base section,Next Page