Hello everybody!
Query API lets you query for records based on queries using the CRM Object Query Language(COQL). COQL is based on the SQL query syntax, and supports the SELECT query to fetch records. Using this API, you can query for data across different modules that are linked using lookup fields.
1. Enhanced Field Limits in the SELECT Clause
You can now retrieve up to 500 fields in a single query. Previously, the limit was 50. This allows you to fetch more data in a single request, reducing the number of API calls and improving performance.
Sample query:
{ "select_query": "select Last_Name, Email, Phone, Industry, Revenue, ... (500 fields) from Leads where id is not null" } |
2. Enhanced Value Limits for IN and NOT IN Operators
The IN and NOT IN operators now support up to 100 values each, doubling the previous limit of 50. This enhancement allows for more comprehensive filtering in your queries. Users can now include a larger set of criteria in a single query, making it easier to retrieve your data without needing multiple queries.
Sample query for IN:
{ "select_query": "select Account_Name from Accounts where Industry in ('IT', 'Finance', 'Healthcare', ... (100 values))" }
|
Sample query for NOT IN:
{ "select_query": "select Account_Name from Accounts where Industry not in ('IT', 'Finance', 'Healthcare', ... (100 values))" } |
3. Custom View support in COQL API
No more manual filtering! Simply use a custom view ID directly in the
FROM clause. Retrieve custom view IDs using the
Custom View API and include them in your queries to retrieve specific-custom view data.
Unsupported Custom Views
- Custom View in Other Modules : The following custom views are not supported in any module.
- Shared By Me
- Co-owner
- Shared To Me
In the
Custom View API response for a module (e.g., Leads), search for the key
"system_name". If the value matches any of the unsupported custom views (Shared By Me, Co-owner, Shared To Me), that view is not supported.
Example : Shared By Me
Key and Value Structure | "system_name": "{module_name}SHAREDBYME" |
Example | "system_name": "LEADSSHAREDBYME" |
COQL supports CVID in the following format: FROM {module_API_name}#{CVID}.
See the sample query below for reference.
Sample query:
{ "select_query": "select Lead_Status from Leads#5725767000000087501 WHERE id is not null" } //CVID: 5725767000000087501 - This is the Custom View ID in the Leads module.
|
4. Retrieve Corresponding Field Metadata via COQL
Need to retrieve field details? You can
fetch both the required data and field metadata in a single query. Just add
"include_meta" key to your query to retrieve field metadata. This key fetches metadata for the fields specified only in the
SELECT column. Also, it eliminates making an additional
GET - Fields Metadata API call to know the field metadata and save API credits.
Sample query:
{ "select_query": "select Lead_Status, Phone from Leads where id is not null", "include_meta": [ "fields" ] } |
Ensure you have the ZohoCRM.settings.fields.READ scope to avoid an OAUTH_SCOPE_MISMATCH error.
Sample Response:
{ "data": [ { "Lead_Status": "Attempted to Contact", "Phone": "555-555-5555", "id": "5725767000000420480" }, { "Lead_Status": "Contact in Future", "Phone": "555-555-5555", "id": "5725767000000420481" }, . . . ], "fields": { "Lead_Status": { "webhook": true, "operation_type": { "web_update": true, "api_create": true, "web_create": true, "api_update": true }, "colour_code_enabled_by_system": false, "field_label": "Lead Status", "tooltip": null, "type": "used", "table_name": "CrmLeadDetails", "field_read_only": false, "required": false, "display_label": "Lead Status", "read_only": false, "association_details": null, "multi_module_lookup": {}, "id": "5725767000000002611", "created_time": null, "filterable": true, "visible": true, "refer_from_field": null "auto_number": {} } }, "info": { "count": 200, "more_records": true } }
|
5. Field Limit Restriction in the ORDER BY Clause
The fields limit has been restricted in the ORDER BY clause. You can specify up to 10 fields in the ORDER BY clause for more precise sorting. Previously, there was no limit. This restriction helps improve query performance.
sample query:
{ "select_query" : "select Last_Name from Leads where id is not null order by Lead_Source, Lead_Status, Email, ... (up to 10 fields)" } |
6. Duplicate fields restriction
Duplicate fields are restricted in ORDER BY, GROUP BY, and AGGREGATE clauses. That is, if the same field is specified multiple times within the same clause, leading to unnecessary repetition and potential errors. This ensures cleaner and more accurate query results. Previously, this restriction applied only to the SELECT column.
Bad query:
{ "select_query":"select id from Contacts where id is not null order by id,id" }
|
Duplicate aggregate fields in a query will result in a "DUPLICATE_DATA" error in the response.
7. Retrieve territory information
You can now retrieve territory information from a module by including the territory field API name in the SELECT column. This is useful to understand where your data has been distributed in various geographical locations.
Sample query 1 :
{ "select_query": "select Account_Name, Territories from Accounts where id is not null" } |
Sample query 2 :
{ "select_query" : "select Territories, Account_Name.Territories from Contacts where id is not null" } |
Sample response:
{
"data": [ { "Account_Name.Territories": [ { "name": "USA", "id": "5725767000000454003" } ], "id": "5725767000000420261", "Territories": [ { "name": "China", "id": "5725767000000454981" } ] }, . . . ], "info": { "count": 174, "more_records": false } }
|
Supported modules to retrieve territory details: Contacts, Accounts, Leads, and Deals.
Note:
The territory field API names differ by module:
- Accounts, Contacts, and Leads : Territories
- Deals : Territory
8. "Full_Name" retrieval based on user preferences
The
full name can be retrieved based on the current user's display name pattern, as set in their
preferences. This ensures that the full name format aligns with the user's settings.
The "Full_Name" field contains the concatenated values of the First_Name and Last_Name fields. This is a read-only field available only in the Leads and Contacts modules.
Sample query:
{ "select_query": "select Full_Name from Contacts where id is not null" } |
9. Support for Multiple Modules in "what_id"
A Multi-Module Lookup (MML) field is a special type of lookup field that allows you to establish a relationship between a record in one module and records from multiple other modules. Unlike a standard lookup field that references a single module, an MML field can point to multiple module types.
Currently, an MML field is available in the Appointments module. The Appointment_For field is an MML field that can reference multiple modules, such as Leads and Contacts.
Sample query:
{ "select_query": "select 'What_Id->Leads.Company','What_Id->Accounts.Account_Type' from Calls where id is not null" }
|
9.1 Support to query Multi-module Lookup (MML)'s inner field
You can now retrieve inner fields of linked modules within the MML field. This provides deeper insights into related fields data in a module.
For example, you can now query specific fields from a related module that is linked through a Multi-Module Lookup, making your data retrieval more precise and informative.
{ "select_query": "select 'Appointment_For->Contacts.Lead_Source' from Appointments__s where id is not null" } |
This query retrieves the
Lead Source field from the
Contacts module within the
Appointment_For MML field.
9.2 Support for Querying Multi-Module Lookup Module Name
Retrieve the module name associated with each record in a Multi-Module Lookup (MML) field. This helps in identifying the source module of the linked records, providing a better context for your data.
Sample query:
{ "select_query": "select Appointment_For.module.api_name, Appointment_For from Appointments__s where id is not null" } |
The
Appointment_For.module.api_name returns the module name (e.g., Contacts or Accounts) for each record linked in the
Appointment_For MML field.
10. Retrieving Consent-Related Information
You can now retrieve
consent-related information using the
Consents Lookup fields in a module. This is particularly useful for
GDPR compliance, ensuring you have consent records.
For more details, refer to the
Consent Management documentation. This is particularly useful for GDPR compliance, ensuring you have all necessary consent information.
Note:
- Once GDPR is enabled and configured for a module, a new Data Processing Basis Details field will be created in the respective module with the data type consent_lookup ("data_type": "consent_lookup").
- You can retrieve the Data Processing Basis Details field created in the module using the GET Fields Metadata API.
- To know all available fields in the Consents module, make another GET Fields Metadata API call.
Sample query:
{ "select_query": "select Data_Processing_Basis_Details.Contact_Through_Survey, Data_Processing_Basis_Details.Data_Processing_Basis from Leads where Data_Processing_Basis_Details is not null" }
|
11. Enhanced Lookup Field Response
Previously, when a lookup field was included in the SELECT column JOIN, only the lookup field’s ID was displayed. From V7, the corresponding display field value is also provided. Now, when you specify a lookup field in the SELECT column, the response includes the id of the Lookup record ID and the display field value of the lookup record.
This eliminates the need for an additional SELECT column JOIN to fetch the display value (e.g., Owner.last_name).
Sample query:
{ "select_query" : "select Owner from Accounts where id is not null" } |
Sample response
V6 | V7 |
{ "data": [ { "Owner": { "id": "5725767000000411001" }, "id": "5725767000000420169" },
{ "Owner": { "id": "5725767000000411001" }, "id": "5725767000000420170" }, . . . ], "info": { "count": 23, "more_records": false } } | { "data": [ { "Owner": { "name": "Boyle", "id": "5725767000000411001" }, "id": "5725767000000420169" },
{ "Owner": { "name": "Kate", "id": "5725767000000411482" }, "id": "5725767000000420170" }, . . . ], "info": { "count": 23, "more_records": false } } |
Note:
- If you query the Users lookup field in the SELECT column, the corresponding record’s last name (display field) will be displayed in the name key of the corresponding lookup JSON (e.g. Owner) response, from V7.
- Display field value will not be available for Consent Lookup and Multi-Module Lookup (MML) fields.
12. Increasing "Profile Image" field comparators
The Profile Image field now supports additional comparators.
V6 | V7 |
is null, is not null | is null, is not null, =, !=, in, not in, like, not like |
You can use a record image's ID to fetch related data. For example, if you only have a record image's ID and want to retrieve the respective record's last name, you can include the record image ID in your query.
Sample query:
{ "select_query" : "select Record_Image from Leads where Record_Image = 'siwz79472749456ff433f8b88d0a795ae9855' " } |
13. Enhanced "Rollup Summary" field comparators
The Rollup Summary field now supports additional comparators, making it easier to filter and analyze your data with more precision.
| V6 | V7 |
is null, is not null | =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null |
Sample query:
{ "select_query" : "select Last_Name from Leads where Total_Calls between 5 and 10" }
|
13.1 Enhanced response for the Date and Datetime return types of the Rollup Summary
The Date and Datetime return types in the Rollup Summary field are now provided in ISO date and ISO datetime formats based on the user's time zone.
Sample query:
{ "select_query": "select Last_Note_Added from Leads where id is not null" } |
Response| V6 | V7 |
{ "data": [ { "Last_Note_Added": "1721302770000", "id": "5725767000003160016" }, . . . ], "info": { "count": 200, "more_records": true } } | { "data": [
{ "Last_Note_Added": "2024-07-04T03:49:48-07:00", "id": "5725767000003160016" }, . . . ], "info": { "count": 200, "more_records": true } } |
14. Enhanced LIMIT and OFFSET Value Limit
You can now retrieve up to 100,000 records using pagination with the LIMIT and OFFSET clauses. This significantly increases the amount of data you can handle in a single query, making it easier to work with large datasets. Previously, the maximum limit was 10,000. For the next set of records, add another criteria id > {1,00,000th record ID} with the "AND" operator, and go on to fetch the next 1,00,000 records.
Sample query:
{ "select_query" : "select Last_Name, Created_Time from Leads where (Last_Name is not null and id > 5725767000003160016)" } |
15. Enhanced JOIN Support Limit
Base Column JOIN
If a query contains any lookup_field.{any_field} in any clause other than the SELECT column such as ORDER BY, GROUP BY, or AGGREGATE columns, then it will be treated as Base JOIN. The Base JOIN support limit has been increased from two to five. This enhancement enables more complex queries across multiple modules, improving data retrieval flexibility.
Sample query:
{ "select_query" : "select id from Contacts where id is not null order by Owner.role, Created_By.profile" } |
Refer to the
Base JOIN section in the COQL API document for more details.
15.1 Introducing the SELECT Column JOIN
A new SELECT column JOIN has been introduced, with a maximum limit of 15.
Advantages of SELECT Column JOIN
- If a lookup field is included in the SELECT column and another lookup field is used in the Base column JOIN, they will be treated as separate JOINs. This ensures that the SELECT column JOIN does not affect the Base JOIN.
- Previously, when two lookup fields in the SELECT pointed to the same module (e.g., Users), they were treated as separate JOINs. From V7, they are now considered a single JOIN, optimizing query performance and reducing redundancy.
Sample query:
{ "select_query" : "select Owner.last_name, Created_By.role from Contacts where id is not null" } |
16. Subquery in COQL
A subquery support has been given in COQL. A subquery is a child query that is nested or embedded within a parent query i.e., outer query. Subqueries can be used as an alternative to JOINs in a query. Use subqueries when a JOIN field is only needed in the criteria, i.e., in the WHERE clause. The primary reason for using subqueries is to improve performance by saving time and memory.
Sample query:
{ "select_query" : "select Lead_Source,Email from Leads where Contacts in (select id from Contacts where Email=' patricia@mail.com')" } |
You can retrieve up to 100 records per subquery in a single query. A maximum of 5 subqueries can be queried in a parent query.
Refer to the
Subquery documentation for more details.
A detailed post on Subquery will be covered in an upcoming Kaizen post.
We trust that this post meets your needs and is helpful. Let us know your thoughts in the comment section or reach out to us at
support@zohocrm.com.
Stay tuned for more insights in our upcoming Kaizen posts!
Recent Topics
136.143.188.51 blocked by spamcop
Zoho mailserver seems to be blocked by spamcop. Cant send mail to my customer. Not sure what to do.
🚀 WorkDrive 5.0: Evolving from a file sharing app to an intelligent content management platform: Phase 2
Hello everyone, WorkDrive's primary focus has always been to provide an intelligent and secure content management platform, simplify collaboration, and be the central repository of files for all Zoho apps. In our previous announcement, we unveiled the
i keep see there is a connetion issue connecting 3rd party api on zoho when using zia
hi there , i have set up open ai api to zoho zia (copied and pasted to zoho zia) but I keep getting notificaiton "there is a connetion issue connecting 3rd party api on zoho" when using zia on top when click zia and try to type in word there
Deleting Fields in CRM Deletes Views in Analytics
Hey friends! I'm having some issues when we modify some fields within ZohoCRM. There are times where we need to sunset a field and eventually completely remove it. In these instances, it seems like a lot of views are removed in Analytics. This ranges
Booking outside of scheduled availability
Is there a way for staff (such as the secretary) to book appointments outside of the scheduled availability? Right now to do this special hours must be set each time. There should be a quicker way. Am I missing something?
Zoho Desk Android and iOS app update: Access multilingual templates on the IM module
Hello everyone! We have introduced multilingual templates on the IM module of the Zoho Desk mobile app allowing you to send IM template messages in translated versions. iOS: Android: Please update the app to the latest version directly from the App Store/
Does Zoho offer a full WhatsApp Marketing Platform (like WATI / GallaBox)?
lm exploring WhatsApp marketing and I’d like to understand what options are available within the Zoho ecosystem. Currently, I see: Zoho SalesIQ has a broadcast option, but it seems very limited — for example, it doesn’t support uploading contacts via
Custom buttons
Getting Error "You can only create 10 custom buttons per module" ! I already have more than 10 for 1 module before trying to create another. Is this a new restriction brought about by the all knowing what's best for businesses Zoho guru ? It would be
The Social Wall: October 2025
Hello everyone, As we head toward the end of the year, we’re bringing you a few updates to help give your social media efforts a strong finish. This month, we’re rolling out new enhancements across both the web and mobile app. Post Preview Have you ever
Client Script | Update - Support for List Page (Canvas) and Notes
Hello everyone! We are glad to announce two exciting updates in Client Script. Client Script support for List Page (Canvas) Client Script support for Notes These open the door to more dynamic and interactive customizations! 1. Client Script support for
Pincode Based Pricing
We want to give extra discount to customers within city limits because our shipping and packing costs are less. My proposal is assign list of pincodes to price list and assign the price list to customers whenever they sign up with pincodes. Also show
feature question: lookup field in Projects task layout
Hello, Below we have interface to configure a custom task layout, however, we need a lookup field to let pickup data from other apps, such as Zoho CRM Products module, is that possible or already in your roadmap? or a workaround to achieve it? Thanks in advance.
Plan change from Zoho One to Zoho Workplace
Hello Zoho, Following the recent pricing update for Zoho One, we are interested in transitioning to Zoho Workplace products. Please inform us about the necessary steps for this process so we can proceed promptly. Kind regards,
Can we do Image swatches for color variants?
We want to do something like the attached screenshot on our new zoho store. We need image swatches instead of normal text selection. We want to user to select an image as color option. Is this doable? I don't see any option on zoho backend. Please h
What's New in Zoho Analytics - October 2025
Hello Users! We're are back with a fresh set of updates and enhancements to make data analysis faster and more insightful. Take a quick look at what’s new and see how these updates can power up your reports and dashboards. Explore What's New! Extreme
Zoho Analytics & SQL Server - Live connect
Below are the steps I did Created a fresh database and table in my own virtual server Created a new data source connection with live connect I was able to select the tables and created the data source successfully I am getting the error when I try to
Explore the Redesigned Quotes Module in Zoho Billing
Dear users, We’re excited to introduce a refreshed look for the Quotes details page in Zoho Billing! This update brings you a more efficient user interface experience without changing your existing workflow. We've enhanced layouts with organized tabs,
Latest updates in Zoho Meeting | Create departments, Share PDF files
Hello all, You can now create departments to group team members within your organization. This will make it easier for you to organize department-level meetings and invite members. In webinars, use the Share material feature to share PDF files directly
Unify All Zoho Video Meeting Experiences into One Standardized Platform
Hi Zoho Team, We would like to share an important user experience concern regarding the current state of video meeting functionality across the Zoho ecosystem. The Problem Within Zoho, there are multiple ways to initiate or schedule a video meeting: Zoho
[Free Webinar] Product Updates: Quick Catch-Up Session - Part I - Creator Tech Connect
Hello Everyone! We welcome you all to the upcoming free webinar on the Creator Tech Connect Series. The Creator Tech Connect series is a free monthly webinar featuring deep-dive technical sessions designed for developers, administrators, and app builders.
Project expenses in Zoho Books
Just the way timesheet is used to calculate labour cost for a particular project, how do i record other expenses against a project such as materials and consumables used for the project? So that under report, I can view the total amount expended on each
Blueprint status change
I want to change the Blueprint status of a workflow. The status is completed automatically when I added the record through API call. But I want to make it active, I don't see any options for this. Even if I draw transition from any stage , it is not visible
What is going on with GMail extension.
Suddenly, it is popping up and saying "Please wait while we work our magic... 10%" and it sits there. Yesterday I waited for an entire hour call and it never got further. Today, it's not resolved. Is there a fix I can do? I've tried all the typical
Marketing Tip #6: Cross-sell with related products
Don’t let customers stop at just one product. Suggesting related or complementary items improves the shopping experience and increases order value. Try this today: Use the Item Recommendations feature in Zoho Commerce and add two to three suggested items
i cannot use <b></b> to bold the message in Creator C6!!!!???What?
Dear experts and friends, Now Creator 6 blocking us from using <b></b> Anyone facing this issue? I faced it on Creator C6 Previously, it used to work. Now failed to work. Faint~ Seek guidance from everyone on how to bold the message. The super simple
Zoho Writer - Option to Export as .zdoc format
I've noticed that it's not possible to export a Zoho Writer Document in the .zdoc format. Isn't zdoc, Zoho Writer's own format? My use case is that I sometimes need to create quite complex documents with floating elements, which sometimes need to become
[Announcement] Enhanced Merge APIs of Zoho Writer for automating document generation using Zoho Apps data
Hi users, With the record_id parameter in Zoho Writer's Merge APIs, fields related to the given record id is automatically fetched from the Zoho app. You don't have to spend time manually mapping the records and fields in the Deluge code. Here is a sample
Pin multiple columns and adjust column widths in CRM subforms
Hello all, Subforms act as secondary forms or tables in which you can associate multiple line items to a primary record and thereby ensure more structured and comprehensive data organization. We've made some recent enhancements to subforms. Here's what's
[Webinar] Automate sales and presales workflows with Writer
Sales involves sharing a wide range of documents with customers across the presales, sales, and post-sales stages: NDAs, quotes, invoices, sales orders, and delivery paperwork. Generating and managing these documents manually slows down the overall sales
Automate insurance document workflows with Zoho Writer
Insurance companies have to deal with creating and managing complex documents and forms, such as policy applications, explanation of benefits documents, brochures, renewals, and claim forms. Handling all of this manually is hugely time and effort intensive,
Latest updates in Zoho Meeting | New End of session notification to remind everyone about the session end time
Hello everyone, We’re excited to share a new feature for Zoho Meeting ; End of session notification. With this new setting, you can choose to remind all participants or only the host about the scheduled end time of a meeting. You can also choose when
[Webinar] Zoho Writer for SCM and logistics
Creating, sharing, and distributing complex documents are constant challenges in the SCM and logistics industry. That's why our next webinar is focused on how to simplify these document workflows. Join us on Jul 10, 2025 for a Zoho Writer webinar designed
[Webinar] Zoho Writer for the manufacturing industry
Creating, sharing, and distributing complex documents are constant challenges in the manufacturing services industry. That's why our next webinar is focused on how to simplify these document workflows. Join us on May 8, 2025, for a Zoho Writer webinar
Add Comprehensive Accessibility Features to Zoho Writer
Hello Zoho Writer Team, We hope you are doing well. We would like to submit a feature request to enhance Zoho Writer with a full set of accessibility tools, similar to the accessibility options already available in the Zoho Desk agent interface. 🚧 Current
[Webinar] Simplifying document workflows in financial services with Zoho Writer
Creating, sharing, and distributing complex documents are constant challenges in the financial services industry. That's why our next webinar is focused on how to simplify these document workflows. Join us on April 10, 2025, for a Zoho Writer webinar
[Zoho Writer Webinar] Learn how to simplify your HR operations: Part 2
Hi Zoho Writer users, We're excited to announce the Zoho Writer webinar for July 2024: Learn how Zoho Writer can simplify your HR operations: Part 2. This webinar will focus on how Zoho Writer can help HR teams streamline and automate their entire hiring
[Zoho Writer Webinar] Learn how to simplify your day-to-day HR operations
Hi Zoho Writer users, We're excited to announce the Zoho Writer webinar for June 2024: Learn how Zoho Writer can simplify your day-to-day HR operations. This webinar will focus on how to automate your entire hiring process and generate various types of
Branding of native system emails
Make system emails adjustable in terms of branding. We want our system to be completely white label, because it is not a CRM anymore, it's way more than that. We are following the strategy of "CRM for everyone" to use the CRM in all departments, not only
Support “Never End” Option for Recurring Meetings in Zoho Meeting
Hello Zoho Meeting Team, Hope you are doing well. We would like to request support for creating recurring meetings with no end date in Zoho Meeting. Currently, when scheduling a recurring meeting, Zoho Meeting requires us to select a specific end date.
[Important announcement] Removal of Zoho Writer's DocuFiller Add-on
Hi users, Effective 21st Feb. 2025, we will be removing the DocuFiller add-on for Zoho Writer's Fillable Templates. From that point on, the add-on's functionalities, such as sharing or publishing fillable forms for data collection and receiving submissions
Next Page