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 #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.Kaizen #226: Using ZRC in Client Script
Hello everyone! Welcome to another week of Kaizen. In today's post, lets see what is ZRC (Zoho Request Client) and how we can use ZRC methods in Client Script to get inputs from a Salesperson and update the Lead status with a single button click. In thisKaizen #222 - Client Script Support for Notes Related List
Hello everyone! Welcome to another week of Kaizen. The final Kaizen post of the year 2025 is here! With the new Client Script support for the Notes Related List, you can validate, enrich, and manage notes across modules. In this post, we’ll explore howKaizen #217 - Actions APIs : Tasks
Welcome to another week of Kaizen! In last week's post we discussed Email Notifications APIs which act as the link between your Workflow automations and you. We have discussed how Zylker Cloud Services uses Email Notifications API in their custom dashboard.Kaizen #216 - Actions APIs : Email Notifications
Welcome to another week of Kaizen! For the last three weeks, we have been discussing Zylker's workflows. We successfully updated a dormant workflow, built a new one from the ground up and more. But our work is not finished—these automated processes are
Recent Topics
Debug and Test not receiving updates
Hello, I've been trying to debug two of my simple flows between Zoho Projects and Clockify but it seems like the Debug and Test feature is not working for me. When I click the Play button on any of the flows and then perform the trigger actions nothingAttendance, Reimagined: Smarter Views for Teams and Individuals
Tracking attendance shouldn’t feel like a chore. With the revamped Attendance module UI, Zoho FSM makes it easier than ever to monitor work hours, check-in activity, and team availability—whether you’re managing an entire workforce or just keeping anLogistics Industry: Learn to create amazing appointment scheduling sites
In the logistics industry, the quality of service is something that cannot be shown to others unless they experience it. Sure, there are metrics that show how you fare when compared to your competition, but then, it only communicates little with respect to quality. The usual route which firms in logistics took are advertisements, to which the internet has opened alternatives in today’s digital age like websites and appointment scheduling sites. A great online presence can help you communicate withCustomer Management: # 1 Pick a Convenient Way to Onboard Customer
When Meera opened her bookstore, onboarding customers was effortless. A customer walked in, picked a book and left with a handwritten bill. As the store gained popularity, things changed. " Can I place an order over the phone?" a customer asked one day.Fetch ALL items from Inventory and send to Google Sheet using Flow
The title says it all -- I want to fetch the Item name, SKU, Purchase rate, and Sales rate for ALL items in Zoho Inventory and have them populate a tab in an existing Google Sheet. I have already successfully made this work for one item, so the flow isWhy Document Integrity Matters at Every Stage of the Contract Lifecycle
Contracts are legal records that must stay authentic and traceable from creation to expiry or termination. In Zoho Contracts, several design decisions focus on protecting the integrity of every document. These measures ensure that no important detailsaffichage pièces jointes impossible
Bonjour, aujourd'hui je n'arrive pas à afficher ni à télécharger les pièces jointes dans ZOHO CRM. J'utilise Chrome, j'ai vidé les caches et les cookies. Mais cela ne change rien. Merci de votre aideReminder to renew subscription.
Whenever I try to add to one of my notes a " renew your subscription" window pops out and won't Close disallowing me to edit. Why should I renew my subscription on an application with at best such bugs and at worse such underhanded ways to force renFields not coming through, not pulling data
Forgive the noviceness of my vocabulary. I am in Zoho flow connecting CRM and Projects. When an opportunity hits a certain stage, it will create the project in Projects, and bring in data from the opportunity. Everything is working, except for two fieldsSmartsheet Integration
Has any in the community been able to integrate Zoho Desk with Smartsheets using Zoho Flow? I am trying to get the Smartsheet Row ID based on ticket data and cannot find documentation on the Get Row function for Smartsheet. It is asking for a Query StringCRM Campaign - how to mass remove contacts?
Does anyone have a solution for removing all contacts from a CRM campaign. Seems that one by one is the only option.Send out follow-up email in the same thread (threaded conversations in individual emails) from Zoho CRM
Hi, I'm new to Zoho. I'm trying to send individual emails to my leads one by one. And I'll send out follow up email if I don't hear back from them later. However, instead of sending a new email, I want to reply in the same email thread so that the recipientsCadence Support in Zoho Vertical Studio
We would like to know whether it is possible to create and configure Cadences in Zoho Vertical Studio, similar to the Cadence feature available in Zoho CRM. Please confirm if Vertical Studio supports creating cadences for modules and users, and whetherRemoving "Products" as mandatory field from Sales Order creation page
Hello, friends According to our workflow, we should: 1) create Sales Order (Stage "New") 2) call and discuss the Products with a customer (Stage "Communication") 3) add the Products to the Sales Order during the call However, "Products" is mandatory toZoho Books says "The customer "Hakimuddin P A Ali" already exists. Please specify a different name.""
One of my transaction is not getting posted from Stripe to Zoho books. I already checked in Zoho books that there is no customer already present in Zoho books with the same name (as the error mentions) I already raised this complaint on Zoho flow portal,Is it possible to automate a file to be directly uploaded to a specific subfolder in Zoho Workdrive?
I am establishing a workflow so that when a new task is marked "complete", the document submitted in the task (under the "Documents" tab) is automatically uploaded to a specific subfolder in the associated WorkDrive for the project. However, the dilemmaSteuerberater der Zoho benutzt in Deutschland
I write in English because the issue is related to German regulations. Wir sind ein Unternehmen, welches aktuell keine Pflicht zur doppelten Buchführung hat. Aktuell bucht unser Steuerberater jeden Beleg, auch unsere Auslagen. Wir würden dies gerne selbstWhy are some folders not showing up as an action trigger in Zoho Flow?
I am currently working on a flow that automates the process of creating a folder in WorkDrive once a Task List is created. When I am selecting a destination for the folder, some projects are not showing up. I intend to create a folder within a specificGetting Date from PostgreSQL error
I am pulling data from PostgreSQL table into a CRM record via FLOW but the field is coming out empty. Need some suggestions: Field Type in CRM: Date/Time Field value in PostgreSQL: 2024-12-05 21:06:32.479 Field value in FLOW "Fetch Row": created : "2024-12-05T21:06:32.479382000",Zoho Flow to SendOwl API error
SendOwl is listed in the apps list of Zoho Flow. When trying to connect through the API with valid key and secret, the error given is: SendOwl says "HTTP Basic: Access denied" It seems Zoho doesn't connect via HTTPS. I've already tried using webhooks,decisions based on content of a ticket
Hi, I need to create a flow that sends automated email based on the content of a ticket in zoho desk. I have certain tickets that are formatted in a particular way, and contain certain keywords. How can I filter (based on decisions) the tickets basedNewline Character "\n" parsing problem in webhooks
Hi there, I am facing an issue in Zoho Flow (maybe a general issue or maybe only in Zoho Flow): I have this function: void sendListWebhook(string url, list array) { for each arrayElement in array { result = invokeurl [ url :url type :POST parameters:arrayElement.toString()One Company, multi branches, multi warehouses and I need to manage this in one organization
Dears, I'm a new user to zoho solutions, however I found a lot of persons giving a nice feedback about it also a lot of good reviews. I though why we try to move to it in our company. I'm not sure if Zoho could give me all what I need or not that's why I'm going to post in this topic most of the things I know about managing our business to let you ,as zoho experts, tell me if Zoho will meet my business requirements or not. First of all we are a flowers and landscaping company we have like 6 toMigration Evernote
Bonjour Apres plus d'une dizaine d'année passer avec evernote j'ai décidé de franchir le pas . J'ai débuté par migrer mes notes vers zoho et depuis la migration est bloquée. Impossible d'avoir un retour du support malgré plusieurs mails et tentative d'appelPushing GCLID info from Gravity Forms to ZohoCRM
We are switching to Gravity Forms from Zoho Forms and I cannot find any good info on how to make sure my GCLID tracking info is pushed through to the CRM through my new forms. There was an article in the documentation about placing something within theOn Zoho Flow, Record deleted trigger from Zoho Creator
On creating flow in Zoho Flow, when we select app Zoho created, it provides 3 trigger options - Record created, Record updated and Record created or updated We need trigger on "Record Delete" as well, We have a use case to fire our system api if recordZoho Flow Switch On Not Working
I have created a flow which is when the mailbox address received the email that meet the condition will auto fetch the contact, account and finally create the ticket by using Zoho Desk. When I do a test and debug on my flow it work well as what I expectMS Teams Flow Integration Question
Where can I find the chat ID for the "Post message to chat" target?Zoho Flow - Access denied to recover recording from call module - OpenAI
Hi, I am trying to build a Flow to recover recoding when a new call is saved in my CRM. I have integrated my CRM with ringcentral and the recordings are saved in the event. Trigger: When new module entry Step 2: Fetch module entry (Zoho CRM says "permissionUnable to send price in big commerce from zoho flow.
I am creating a flow from zoho flow to create a product in zoho inventory and sending this to big commerce. But the problem is like when ever i send the price its getting error. it require decimal format value if i am sending ${trigger.sales_rate} theNot sending mail to new lead in zoho flow
I have created a flow in zoho flow and triggered an action to send email notification when the new lead is created but even after user entering the mail id the email is not going to themAccess query string from incoming webhook URL in Zoho Flow
I have a fairly straightforward use case, but cannot find anything conclusive on this subject. How can I simply access the query string of the incoming webhook request in Zoho Flow? I am attaching the Deal ID to the end of the request like: https://flow.zoho.com/762439902/flow/webhook/incoming?zapikey=1001.0dec2d7dd15080c464a13925275f0129.448c23ee219c545c89f5a178456cfc8b&isdebug=false&dealID=${Deals.DealUnable to access my Zoho forms account
For some days now, I haven't had access to my Zoho Forms account. I keep getting an error that says, "You are an inactive user in your organization" via the mobile app and "You don't have permission to access this organization" via the web. I was removedIdentify Unused Deluge Functions in Zoho CRM
Hello Zoho Support Team, I would like to request a feature that helps administrators identify unused or rarely used Deluge functions in Zoho CRM. Currently, Deluge functions are distributed across different areas, and there is no centralized overviewUser Automation: User based workflow rules & webhooks
User management is an undeniable part of project management and requires adequate monitoring. As teams grow and projects multiply, manual coordination for updating users & permissions becomes difficult and can give way to errors. User automation in ZohoUpdate on V2 API End-of-Life Timeline
Dear Users, Earlier this year, we shared the launch of the V3 APIs and requested users to migrate from the older V2 APIs by December 2025. We have received valuable feedback from our users and partners regarding their migration timelines. We are happyRun a workflow after record merge
Hello, We are wondering if there is a way to trigger a workflow after two records are merged? We have a sms service that use to text back and forth with Prospects and Contacts. To make it easy for our users, we automatically create them in this otherLive webinar | The Evolution of Zoho Show: 2025 Feature Recap
2025 has been a year of exciting updates and features for Zoho Show. From advanced design and formatting tools to smarter presentation delivery, enhanced collaboration, and AI-powered features—along with the launch of our desktop apps, Show has continuedBatch Tracking custom function
Hi fellow zoho users, We have our ecommerce site setup to shopify. I have some products I want to add batch tracking too, my only issue is that it breaks our shopify workflow (create sales order, invoice, payment, package) because it requires us to manuallyInclude Suggested Articles in New Ticket Email
When someone creates a new ticket, is there any way to include recommended knowledge base articles (like the ones Zia Suggests) in the email notification to the user? When they're waiting for a reply, it would be nice to have sent them some recommendedNext Page