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
I have already created some Bots, Commands, and Widgets in Cliq, but I am still not seeing the “Create Extension” option in my account. Could you please help me enable or access this option?
Zoho Books Sandbox environment
Hello. Is there a free sandbox environment for the developers using Zoho Books API? I am working on the Zoho Books add-on and currently not ready to buy a premium service - maybe later when my add-on will start to bring money. Right now I just need a
How to add Product Add-Ons, Mandatory Forms, and Auto-Save Address in Zoho Commerce
Hi all, I need help setting up several behaviors in Zoho Commerce. I can’t find the correct configuration options, so I want to confirm whether these are supported or if there is a workaround. 1. Product-Specific Add-Ons (Example: GWB Subscription) When
Zoho Workdrive - Communication / Chat Bar
Hi Team, Please consider adding an option to allow admins to turn on or off the Zoho Communication Bar. Example of what I mean by Communication Bar: It's such a pain sometimes when I'm in WorkDrive and I want to share a link to a file with a colleague
Zoho CRM - Writing Assistant Tone
Hi Zoho CRM Team, Text in my emails often gets underlined in yellow because I tend to use a more informal tone with my client's, like using "I'm" instead of "I am". Is there some way for me to tell the system that this is my preferred writing tone, so
Work Order Assignment for Engineers Handling Their Own Requests
I’m setting up FSM for a business where there are multiple engineers, but each engineer handles their own process end-to-end receiving the service request, creating the work order, and completing the field service job. I noticed that I must create an
Proposal for Creating a Unique "Address" Entity in Zoho FSM
The "Address" entity is one of the most critical components for a service-oriented company. While homeowners may change and servicing companies may vary, the address itself remains constant. This constancy is essential for subsequent services, as it provides
Request for Auto PO - Min–Max based Automated Purchase Feature
Dear Zoho POS Team, I’m writing to request a feature enhancement that would significantly streamline inventory management for businesses using Zoho POS — particularly supermarkets, FMCG retail, and multi-store operations like ours. Feature Requested:
Zoho CRM mobile app update: Reports module for Android app
Hello everyone You can now access the Reports module in your Zoho CRM Android app. With this update, you can view any report created in CRM web app in your mobile app. <br> Once you open the report, you can tap on any record to view its details.
Zohomail
Im trying to setup email address zoho
PROBLEMA
Salve, non riesco a inviare email, e mi esce una tabela errore temporaneo. come posso risolvere il problema ?
Forever FREE Business Email with Zoho Mail
Forever FREE Business Email with Zoho Mail: is it available?
CRM: hosting a single html file in Zoho and displaying it as a widget
I have seen that CRM offers the option of uploading a web project to Zoho itself and displaying it as a widget in CRM. The instructions then talk about setting a development environment with Node and developing an application to upload to Zoho. But I
Weekly Tips : Make collaboration effortless with Whiteboard in Zoho Mail
Working with your team often means switching between emails, notes, and other applications just to explain an idea. Maybe you are trying to sketch a layout, plan a workflow, or quickly brainstorm ideas—with text alone, things can get confusing. So how
Formula field number of days between 2 dates
Hi, I want to have a formula field which calculates the following: IF EndDate < TODAY and Oproep is true (this is a checkbox field) than EndDate - StartDate, otherwise TODAY - StartDate It should calculate the number of days How can I write this for
Show Custom Button in Portal Listview Canvas
I have created a custom button that shows in a list view of deals (internally I can see it). I have permissions to allow this button on the portal. But it is not displaying in the canvas list? Before I do too much leg work, is this function allowed?
Improved RingCentral Integration
We’d like to request an enhancement to the current RingCentral integration with Zoho. RingCentral now automatically generates call transcripts and AI-based call summaries (AI Notes) for each call, which are extremely helpful for support and sales teams.
Zoho CRM for Everyone's NextGen UI Gets an Upgrade
Hello Everyone We've made improvements to Zoho CRM for Everyone's Nextgen UI. These changes are the result of valuable feedback from you where we’ve focused on improving usability, providing wider screen space, and making navigation smoother so everything
search and Smart Bar both missing in Mail
One of the users on my account does not have the search bar at the top right or the Smart Bar at the bottom left of the desktop Mail app. Any ideas how to get those back?
What's New in Zoho Analytics - November 2025
We're thrilled to announce a significant update focused on expanding your data connectivity, enhancing visualization capabilities, and delivering a more powerful, intuitive, and performant analytics experience. Here’s a look at what’s new. Explore What's
ZOHO reporting DKIM entries are not configured, when they have been configured and verified by 3rd parties
Why is ZOHO reporting to my organisation users the following: "The DKIM entries in your domain's DNS records are not configured. Please contact your administrator for configuring DKIM to ensure optimal RSVP invite delivery." When I have configured the
Manage Bookings directly from Zoho Mail
Greetings from the Zoho Bookings team! We’re introducing the new Zoho Bookings extension for Zoho Mail, designed to help you view appointments, copy time slots and share booking links without leaving your inbox. This integration brings scheduling right
My notes from the past 2 months have disappeared
Hola, necesito ayuda urgente. Hoy, al iniciar sesión en mi Zoho Notebook como todos los días, me llevé una gran sorpresa al descubrir que todas mis notas de los últimos dos meses habían desaparecido. Estas notas son muy importantes para mí, ya que uso
How can I load a network into the cliq desktop app?
I have both the standard cliq log in for my org and I am part of a cliq network. In the browser I can choose which I log in to. However, in teh desktop app if I log in it will alwasy load my org's cliq. Can I switch this to the network I have create
Ability to modify what displays in calendar invite?
I am a long time calendly user and want to make the switch to bookings. I understand that there is not currently a meets/hangouts integration, is one on the roadmap? Is there anyway I can modify the calendar invite to include the meet link? I can add it to the emails no problem, but I would also like it to display on their calendar. Is there some work around I can do to get it on the calendar? Also am I able to modify the calendar event title?
Issue with Booking Confirmation Page Not Displaying, Leading to Customer Anxiety and Unnecessary Support Calls
I am writing to express my growing concern regarding the confirmation process in Zoho Bookings, particularly the inconsistent display of the confirmation page after a successful payment. As a mobile service provider, I rely on Zoho Bookings platform for
Is it possible to turn off all capabilities for a customer to schedule, reschedule or cancel an appointment?
Is it possible to turn off all capabilities for a customer to schedule, reschedule or cancel an appointment? I would like to set it up so only staff can schedule appointments. Is this possible?
Is there a way to generate a virtual meeting for a group service in Zoho Bookings?
Are virtual meetings not supported for group services/meetings? I have integrated Zoom with one-on-one services, but I need a way to create an online group meeting. Thanks
Tracking Snippet not working in Zoho Marketing Automation!
First off, the fact that you have to wait about 12-24 hours for every response is terrible. How are we supposed to conduct business? Second, we have been trying for several days to get the Tracking Code Snippet in marketing automation to work, to no avail.
Introducing VeriFactu Support in Zoho Books
Hello users, Spain has introduced the VeriFactu system under Real Decreto 1007/2023 to ensure integrity, traceability, and anti-fraud compliance in e-invoicing. Starting January 1, 2026, all B2B invoices must be reported to Agencia Estatal de Administración
Where we can change the icon in social preview
Hi, we changed our logo, and the image that appear in preview (ex : when we post a appointment link somewhere) is still our old logo. I did change our logo in the org setting. https://bookings.zoho.com/app/#/home/dashboard/settings/basic-info?clview=false
Zoho Bookings changes Lead Source
Hi. i would like to know if there's a way for Zoho Bookings to not change the lead source when booking a lead for an appointment as the lead source will be used in a report. Scenario: Lead source: Website after booking an appointment Lead source: Zoho Bookings Thanks. Dan
Need to set workflow or journey wait time (time delay) in minutes, not hours
Minimum wait time for both Campaigns workflows and Marketing Automation journeys is one hour. I need one or the other to be set to several minutes (fraction of the hour). I tried to solve this by entering a fraction but the wait time data type is an integer
Suggestion: Associating Assets with Company in Zoho FSM
Hello Team, I would like to share an idea based on practical experience. Currently, all assets in the Zoho FSM Asset module are linked to a specific contact person. I would like to know if it is possible to associate assets with a company instead. This
Zoho Inventory / Finance Suite - Extend Visibility of File Names on Attachment Fields
Hi Inventory / Finance Suite team, I noticed recently that when you add an attachment field to a module in Inventory, only the first 8 characters of the file name are visible on the details view. 8 characters is not a useful amount and there is plenty
AI Interview Insights: Turn Recorded Interviews into Quick Transcripts & Summaries
Evaluating interviews shouldn’t require replaying long recordings or taking manual notes. With AI Interview Insights, you can now review complete transcripts and AI-generated summaries of your One-way (Recorded) interviews right inside Zoho Recruit. This
Edit Pinned Comments in Zoho Desk
It's great that private comments can now be pinned to the top of the ticket but what would be extremely helpful would be to allow for the pinned comment to be edited vs. having to find the comment in the ticket to edit it.
Many Notes Becoming Unusable
Hello. The Notebook app is becoming unusable. I'm getting odd-looking results from my searches. Some of the notes, after clicking on them, show an update button that does absolutely nothing. Not sure what has happened, but it would be nice to get this
Kaizen #218: Actions APIs - Field Updates
Hello all!! Welcome back to a fresh Kaizen week. In the previous weeks, we covered Workflow Rules APIs, Actions APIs - Email Notification APIs, and Tasks Update APIs. This week, we will continue with another Actions API - the Field Update API in Zoho
How Contract Types and Templates Form the Backbone of Zoho Contracts
Every contract in Zoho Contracts starts with two essential elements: Contract Type and Template. These are not just administrative steps. They define how every contract in your organization is created, governed, and managed over time. Let us look at the
Next Page