Kaizen #207 - Answering your Questions | Advanced Queries using COQL API

Kaizen #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 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_namesFROM {module_api_nameWHERE {field_api_name} {comparator} {valueGROUP BY {field_api_name} ORDER BY {field_api_nameASC/DESC LIMIT {limitOFFSET {offset}

  • FROM - specifies the module to query
  • WHERE - filters records based on conditions

  • GROUP BY - groups records by one or more fields for aggregation
  • ORDER BY - sorts results ascending or descending
  • LIMIT - restricts the number of records returned
  • OFFSET - skips a certain number of records before fetching results

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:

  1. Decimal/Currency/Date/Datetime: =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null
  2. String: =, !=, like, not like, in, not in, is null, is not null
  3.  Boolean: =


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 LIKEINBETWEEN, 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.

NoteSubqueries 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)"

}

Warning
However, COQL currently does not support aggregate functions like MAX() or MIN() on Date or DateTime fields. Attempting this will result in errors or unexpected behavior, as COQL aggregates are primarily designed for numeric fields.

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!



    • Recent Topics

    • Consumers are talking about your business. Are you listening?👂

      A loyal customer might be praising your product in a forum. A frustrated user could be posting a harsh review on a public site. An excited partner may have left a comment on your campaign. A domain expert might be deconstructing your product. A prospect
    • What counts as a Temp for Billing Purposes in Workerly

      I'm considering trying this product but am not sure how the temp count is used for billing purposes. For example, if we keep a large data base of 500 potential workers.....are we billed for that or only if they are assigned to a client at a given point
    • Unable to Receive Emails on Zoho Mail After Office 365 Coexistence Setup – Error: 553 Relaying Disallowed

      Hello, My domain name is bigniter.com, and I’ve been using Zoho Mail as my email service provider without any issues. Recently, I followed the steps outlined in the Zoho documentation to enable Coexistence with Office 365: 🔗 https://www.zoho.com/mail/help/adminconsole/coexistence-with-office365.html#multi-server
    • Zoho Form URL displays incorrect name

      Hi, I have a form I created called "Design Request form". It displays this way everywhere I look. However, in the URL, it shows up as "DesignJobRequestFormFINAL011325PROOFV1B" and I'm not sure why. I can't find where to fix this. Does anyone have any
    • Form name incorrectly displayed in URL

      Hi, I have a form I created called "Design Request form". It displays this way everywhere I look. However, in the URL, it shows up as "DesignJobRequestFormFINAL011325PROOFV1B" and I'm not sure why. I can't find where to fix this. Does anyone have any
    • I can't receive mail

      Hello, I can't receive e-mail. I no longer receive e-mails to the e-mail I received for ​my site. I also edited the DNS settings, but it doesn't work at all.
    • 1‑to‑1 invite missing post-setup (needs re-invite) vs channel invite auto-joins without business prompt

      1. Zoho Cliq 1‑to‑1 external invite The inviter sent a 1‑to‑1 invite to an invitee who didn’t have a Cliq account. After the invitee completed account setup and created a business/organization, the website redirected them to Cliq, where they opened Cliq
    • 【開催報告】東京 ユーザー交流会 Vol.3 2025/10/17 Zoho サービスの活用促進を外部ツールとの連携で実現!

      ユーザーの皆さま、こんにちは。コミュニティチームの藤澤です。 10月17日(金)に新橋で「東京 ユーザー交流会 Vol.3」を開催しました。ご参加くださったユーザーの皆さま、ありがとうございました! この投稿では、当日のセッションの様子や使用した資料を紹介しています。残念ながら当日お越しいただけなかった方も、ぜひチェックしてみてください😊 ユーザー活用事例セッション:Zoho Flowと決済システムの連携 あみろくの岡島さんに、Zoho サービスの活用事例として、Zoho Flow を活用した外部サービスとの連携事例をご共有いただきました。
    • received email opens in a new tab every time I log in

      as per the title: since about when I first made my email account, every single time Ive logged in to view my inbox, a new tab opens for an email I viewed once as if restoring a closed session. I thought I just didnt understand the "starting up" settings
    • Mapping a new Ticket in Zoho Desk to an Account or Deal in Zoho CRM manually

      Is there any way for me to map an existing ticket in Zoho desk to an account or Deal within Zoho CRM? Sometimes people use different email to put in a ticket than the one that we have in the CRM, but it's still the same person. We would like to be able
    • Engage with your customers at scale using WhatsApp Marketing Template messages

      Hi everyone, To make it easier for organizations to communicate with customers, Desk now allows you to send individual, mass, and bulk WhatsApp template messages from both the Ticket and Contact modules. How is this going to benefit your business? WhatsApp
    • Zoho Desk iOS and Android app update: AI powered: Reply Assistance and Refine Messages on IM module.

      Hello everyone! We are excited to introduce new AI powered features on the IM module of the Zoho Desk app. Reply Assistance: Reply Assistance generates suggested responses for incoming chat messages, which you can directly insert into the conversation
    • Empowered Custom Views: Cross-Module Criteria Now Supported in Zoho CRM

      Hello everyone, We’re excited to introduce cross-module criteria support in custom views! Custom views provide personalized perspectives on your data and that you can save for future use. You can share these views with all users or specific individuals
    • Importation Tickets error

      Hi, I'm newbie here 🤓 So, i'm importing data from csv, but when I try advance to mapping fields the importer tool show this message: Previously I try import, other data, and not show errors in this step. Some ideas? Best Regards,
    • Missing information data Zoho inventory

      there some missing data in Zoho inventory connection. pick list stock counts bin location we have requested it via mail and the support team doesn’t gove feedback. has anyone achieve to get these info or to ask other ya les
    • Global 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 has
    • Showing description in timesheet and timelogs.

      I am wondering if it’s possible in version 5 of Zoho People to have the description show by default or with a manipulation on the user’s part. Let me show you what I mean. As you can see this is the view for the users. Now if they want to see the full
    • Direct “Add to Google Calendar” Option in Zoho Meeting

      Hello Zoho Meeting Team, Hope you are doing well. We would like to request an enhancement related to the “Add to Calendar” functionality in Zoho Meeting. Currently, when we open Zoho Meeting and view our meetings under My Calendar, there is an Add to
    • Add Flexible Recurrence Options for Meeting Scheduling in Zoho Cliq (e.g., Every 2 Weeks)

      Hello Zoho Cliq Team, We hope you are doing well. Currently, when scheduling a meeting inside Zoho Cliq, the recurrence options are limited to Daily, Weekly, Monthly, and Yearly. There is no ability to set a meeting to occur every X weeks — for example,
    • Zoho CRM Analytics - Allow To Reorder Dashboards

      I would like to suggest that you add the ability to reorder dashboards in the Analytics Module. I can see that this has been requested some time ago, the latest 9 years ago. I am not sure if this is a big or small endeavor, but such a small fix can go
    • Sales Order, Invoice and Payment numbers

      Hi zoho friends, it is me again, the slow learner. I'm wondering if there is a way to have it so the Sales order, invoice and payment numbers are all the same? It would be easier for me if they were the same number so there is not so many reference numbers
    • Enhanced duplicate check for Leads in CRM

      Hello Everyone, We are excited to announce that you can now check for duplicate entries in leads by comparing them with similar records in the Contacts Module. Previously, when you added a lead, only the converted leads were checked for duplicates. This
    • Ability to Add YouTube Video to Playlist During Publishing

      Hi Zoho Social Team, How are you? While publishing YouTube videos through Zoho Social, we noticed that the platform currently does not allow selecting a playlist at the time of publishing. Instead, we can only add the video to a playlist after it has
    • Ability to Change Visibility of Published YouTube Videos

      Hi Zoho Social Team, How are you? We would like to request an enhancement in Zoho Social regarding the management of already published youtube videos. Currently, after publishing a youtube video through Zoho Social, there is no option to change its visibility
    • All new Address Field in Zoho CRM: maintain structured and accurate address inputs

      The address field will be available exclusively for IN DC users. We'll keep you updated on the DC-specific rollout soon. It's currently available for all new sign-ups and for existing Zoho CRM orgs which are in the Professional edition. Managing addresses
    • Ask the Experts 24: Analytics, data administration, and mobile experience with Zoho Desk

      Hello Everyone! Welcome back to the Ask the Experts(ATE) series! We were so focused on our Autumn 2025 release that we didn't host an ATE session last month. In this month's ATE, we'd like to expand our areas for discussion: we'd like to listen to your
    • Update on the client portal URL for Guest users

      We’re updating the way Guest users access their Connect network. As part of this change, all client organization portals used by Guest users will now be accessible through a dedicated domain specific to each data center. The access URLs mentioned here
    • Workaround: openURL in Blueprints - An alternate approach

      There is a roundabout way to open a URL in blueprints after a save event. By using the 'onBeforeMandatoryFormSave' in Client Script, you can open an external URL. Now, the problem is, this is designed to be run BEFORE the blueprint is saved, not after,
    • MTD SA in the UK

      Hello ID 20106048857 The Inland Revenue have confirmed that this tax account is registered as Cash Basis In Settings>Profile I have set ‘Report Basis’ as “Cash" However, I see on Zoho on Settings>Taxes>Income Tax that the ‘Tax Basis’ is marked ‘Accrual'
    • Migrate file from Single File Upload to Multi File Upload

      Dears, I have created a new field Multi File Upload to replace the old Single File Upload field. I'd like to ask you guys what is the best way to migrate the files to the new field?
    • Integration Request: Elementor

      Integrating Zoho CRM forms with Elementor, the most popular page builder on Wordpress, would be great. I use it for our site, goenergylink.com, and I have had to use Zapier webhooks to be able to connect it with Elementor. The one issue I have run into
    • Open "Live Chat" from a hyperlink?

      Hi, I often write paragraphs and text on our company website, and usually say you can get in touch with us via live chat. Can the chat window be triggered to pop open without clicking the chat graphic in the bottom window, and use it in a hyperlink? ie:
    • Zoho Sites search box

      Is there a Search box that can be added to a Zoho site? It would be for searching within the site only.
    • What stops me from packaging and shipping an order when the inventory is negative?

      It seems if the inventory value is negative, that Zoho Inventory should not allow me to create a Package and Ship it.   But, there seems to be nothing to stop me from doing that other than when I go to physically package the item and realize that there is no stock. There also seems to be nothing on the screen that even indicates to me that I should not package and ship.  To me this is the fundamental point of an inventory system.  Am I doing something wrong?   
    • Conditional formatting based on another field

      Hi I have two fields on my form stage 1 complete and stage 1 deadline.  I am trying to setup conditional formatting so that if stage 1 complete is after stage 1 deadline the record is highlighted in red. I need both stage 1 complete and stage 1 deadline
    • Zoho 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, Apple
    • Is there API Doc for Zoho Survey?

      Hi everyone, Is there API doc for Zoho Survey? Currently evaluating a solution - use case to automate survey administration especially for internal use. But after a brief search, I couldn't find API doc for this. So I thought I should ask here. Than
    • Using IMAP configuration for shared email inboxes

      Our customer service team utilizes shared email boxes to allow multiple people to view and handle incoming customer requests. For example, the customer sends an email to info@xxxx.com and multiple people can view it and handle the request. How can I configure
    • Task Due Date greater than 10 years.

      We use recurring tasks in Projects where every week, month, year etc Some of our projects are greater than 10 years and we are unable to set a new due date because the difference between start date and due date is greater than 10 years. As an example
    • The sending IP (136.143.188.15) is listed on spamrl.com as a source of spam.

      Hi, it just two day when i am using zoho mail for my business domain, today i was sending email and found that message "The sending IP (136.143.188.15) is listed on https://spamrl.com as a source of spam" I hope to know how this will affect the delivery
    • Next Page