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_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}

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: 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: =

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

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!

Previous Kaizen: Kaizen #206: Displaying Related Purchase Orders from Zoho Books in CRM Deals using Queries | Kaizen Directory

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

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

COQL Recap - Why Use It?

Understanding COQL Queries

What kind of queries are supported?

Supported Data Types & Operators

Aggregate functions

Wildcards

Beyond basics: COQL Patterns for real-world scenarios

1. Advanced Filtering & Conditions

2. Combining Multiple Conditions

3. Fetching related records and their fields using Joins (Dot notation)

4. Using Subqueries to detect missing relationships

Advanced COQL Querying: Real-World Patterns

1. Filtering Deals Based on Account Attributes

2. Emulating MIN/MAX for Date fields

3. Combining Multiple Subqueries for Complex Business Logic

Dynamic Account and Deal Performance Analysis

Conclusion

Further Reading:

Topic Participants

Anu Abraham

Sticky Posts

Kaizen #198: Using Client Script for Custom Validation in Blueprint

Kaizen #226: Using ZRC in Client Script

Kaizen #222 - Client Script Support for Notes Related List

Kaizen #217 - Actions APIs : Tasks

Kaizen #216 - Actions APIs : Email Notifications

Recent Topics

Item Bulk Edit - Allow for Reorder Level

Sharing my portal URL with clients outside the project

Zoho Flow + QuickBooks Estimates – Line items not created from CRM subform

SAP S/4 HANA Integration via ZOHO Flow

VLOOKUP FUNCTION PROBLEM

Zoho Flow Completely Broken?

Print / Export full Dashboard page

emails duplicated in my outlook client

How to check task starting time in zoho mobile app?

Creating an extension in Sigma: Zoho CRM isn't selectable as a service

List of packaged components and if they are upgradable

RouteIQ for Zoho FSM

Export Tickets from the Help Center

Is it possible to remove filtering options?

Function #18: Associate invoice templates automatically based on customer language

Changing field types

Quote PDF – Header image stopped rendering suddenly

Changing Default PDF Name

Tip 2: Recursive functions in Deluge: How to dynamically run a function for a specified number of times.

Function #48: Manage fixed installment payments using Zoho Books

Spotlight series #6: The Show app for Android TV has a new look!

Can’t create alias “Email alias already exists”

Appraisals - Order of Previous Reviews Should Be Newest to Oldest

Paging through API results. a major gap in your documentation.

Recurring invoices were generated with old template...

Tip #54- Exploring the Files Icon in Zoho Assist- 'Insider Insights'

Tip#47: Estimation planner

Time Tracking on Iphone doesn't stop

Zoho Sign - Zoho CRM extension upgrade

Group by Owner/ Owner Kanban /Group by Custom Field

Flow using way too many tasks

Regarding the integration of Apollo.io with Zoho crm.

Start Workflow from Deluge Script

Saved Sections?

Marketer's Space: Unable to select a sender address? Here's the fix

Loading Project Balances in ZOHO Books for each project

Scale up your writing style with content analysis tool in Knowledge Base

Zoho Books "Update" Trigger for Zapier?

How to prevent editing of closed tickets in Zoho Desk?

Zoho Desk Limitations

Next Page