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
PO receive quantities
At last, Zoho has finally got around to allowing us to receive a larger qty than recorded in the PO :-) Saves us all from editing the PO's before receiving larger quantities ( usual for us ) ! It's still in "beta" but available upon request, I've tested
Analytics : How to share to an external client ?
We have a use case where a client wants a portal so that several of his users can view dashboards that we have created for them in Zoho Analytics. They are not part of our company or Zoho One account. The clients want the ability to have user specific,
Reference Deal Categories in Deluge
Hello, Is there a way to reference Deal Category in deluge functions? So for our Deals, we have several different WON stages in a pipeline. Rather than type each stage into our functions, we'd like to be able to reference the Deal Category. Similar
Users Name & Email in Reports
Hi, I would like to show the Users Name from their Zoho Acount in All Entries/Reports as well as the current Account Email. Thanks Dan
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
Workflow Creation with Zia gets stuck
It gets stuck here:
Unable to Add Asset to Work Order Due to Different Contact and Company
Hello Zoho Team, I’m facing an issue in our environment related to asset linking in Work Orders. Here’s the situation: When our engineers go for field activities, they initially create the Work Order using the salesperson’s contact details. After completing
I NEED MORE CUSTOM FIELDS!!!
Why can I only have 60 custom fields! It's not enough. I want another 100 extra custom fields to do what's necessary for my business! I'm sure I can't be the only one with this problem!!! How hard would it be to fix this? Just fix it for me please at
Zoho Webinar et Zapier : de la prise de rendez-vous aux relances — une automatisation performante
Organiser un webinaire réussi et gérer efficacement les participants peut être un véritable défi. Avec Zoho Webinar et Zapier, vous pouvez rendre vos processus plus simples. Vous gagnez du temps en automatisant les tâches répétitives. Résultat : une expérience
Ticket Stages Report
From data to decisions: A deep dive into ticketing system reports Ticket stages refer to the series of transitions from the moment a support ticket is created until it is closed. It moves through various stages based on the actions taken at each preceding
【参加無料】今年最後のZoho ユーザー交流会|東京・大阪・名古屋で開催! 活用事例&ユーザー同士の情報交換
ユーザーの皆さま、こんにちは。コミュニティチームの中野です。 11月に東京、大阪、名古屋の3都市でZoho ユーザー交流会を開催します! 毎回ご好評いただいている本イベントでは、実際の Zohoユーザーによるリアルな活用事例の共有や 参加者同士でノウハウを交換し合うグループワークを予定しています。 「他社の活用を参考にしたい」「Zoho をもっと使いこなしたい」方にぴったりの場です。 初参加の方もぜひお気軽にご参加ください! ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
Slow Performance on desk.zoho.com. 11/3/2025
I’m not seeing any active service alerts for desk.zoho.com, but everyone on our account is currently experiencing very slow load times when opening or navigating tickets. We’ve already tried the standard troubleshooting steps — clearing cache and cookies,
Create View to See Tickets Closed within the last 3 days
I'm trying to create a view in Zoho Desk that shows me "recently closed ticket," which I will define as tickets closed in the last 3 days. I want this view to update so that whenever I click to view it is recalculates and shows me tickets closed within
Recurring Events Not Appearing in "My Events" and therefore not syncing with Google Apps
We use the Google Sync functionality for our events, and it appears to have been working fine except: I've created a set of recurring events that I noticed were missing from my Google Apps calendar. Upon further research, it appears this is occurring
Export your notes from Notebook!
Dear users, The long awaited feature is now live. Yes, you can now export your notes from Notebook app in bulk. But the feature has just started with web app alone for now. You can try the export feature as mentioned below: Go to our web app, https://notebook.zoho.com Go to 'Settings' > 'Export' Now, select the format: You can select either ZNote or HTML Once done, you can use the same to import or can have this a local backup of your notes. Note: Export for other platforms are in development and
Dissociate account from contact
Hello, When I make the call through the API to "Dissociate account from contact" using, /api/v1/contacts/**integer**/accounts/**integer**/dissociate I get this response back, 422Unprocessable Entity (WebDAV) (RFC 4918) { "errorCode": "INVALID_DATA", "message":
Zoho Desk and Zoho People Integration - is it possible in order to alert whether an agent is available
Hi, We use Zoho People for our employees to log absence (Paid Time Off etc). I was wondering if it was possible that this information can be shared in to Zoho Desk, so that when allocating a ticket to an agent, if they are on leave there is an onscreen
Add Save button to Expense form
A save button would be very helpful on the expense form. Currently there is a Save and Close button. When we want to itemize an expense, this option would be very helpful. For example, if we have a hotel expense that also has room service, which is a
Call transcrition working for ringcentral?
I don't see anything about what telephony providers can be used. The Zoho support person A said that RingCentral isn't supported. Zoho support person B said that it works, just make sure the call recording link works. Excellent instructions here: Call
How to Send Email from within a custom module (with or without an email template)
It is possible to send an email from the Deals module. However, I can't find a way to send an email from any of our custom modules. I have tried adding an email field to the modules (even though we don't really want one or need it there). That doesn't
Zoho Inventory - Managing Items With Multiple Suppliers
Hi community, I'm working on a project at the moment for a car parts wholesale business. Each Item (part) has its own original manufacturer part number (sometimes more than one part number for the same item). When purchasing parts from 3rd party suppliers,
How do I see the total leads during a certain period?
I understand I can get the count of leads and potentials but the total number of leads in a certain period should be equal to Leads+potentials because when we convert a lead it gets moved to potentials and no longer exists there. is there a way i could
Where is the Global Search field?
I am looking for an alternative to SF.com. Zoho CRM seems to be work fine, and be customizable in terms of the fields and reports. But there's one big thing missing and it's going to prevent us from using it: there's no global search box at the top of
API 500 Error
Hello amazing ZOHO Projects Community, I get this message. How can we solve this? { "error": { "status_code": "500", "method": "GET", "instance": "/api/v3/portal/2010147XXXX/projects/2679160000003XXXX/timesheet", "title": "INTERNAL_SERVER_ERROR", "error_type":
In place field editing for candidates
Wondering about any insight/best practices for efficiently updating candidate records while reviewing them in a Job Opening pipeline. We can do in-field editing (e.g. update job title or City) only when we have the full candidate record open, however
Default tax type for mileage?
Where we are, mileage includes a government tax. Is there any way to set a default tax for the Personal Car Mileage category of expense? (Or any other way?)
Show price book list price
When using price books, once you add products to the price book in the Products related list you can display the Unit price which is the default list price; however, there is no option to show the price book list price. To see the price book list price
Cliq File Upload
While uploading large file like 500MB, it takes time, that fines. But if you resize window or move window in other screen, that uploading disappears. After upload complete & sent it will be visible
Zia Actions: AI-powered Workflow Automation for Faster and Smarter Execution
Hello everyone, Workflows got a notch better with AI-based actions. Actions such as field extraction, prediction, auto reply, and content generation facilitate quick execution with improved speed and accuracy. Zia can intercept useful details in newly
Constant color of a legend value
It would be nice if we can set a constant color/pattern to a value when creating a chart. We would often use the same value in different graph options and I always have to copy the color that we've set to a certain value from a previous graph to make
What's New in Zoho POS - October 2025
Hello everyone, Welcome to Zoho POS's monthly updates, where we share our latest feature updates, releases, changes, and more. Let’s take a look at how October went. Process returns for refunds, exchanges, or offer store credit Returns and exchanges can
Loan and purchase
My husband is lending me mobey to buy a vehicle intersst free ... I need to know how to record the cash receipt and how I pay it back... the money is for a vehicle do I just post the invoice for that as I normally would usung the loan money to pay for
Zoho Connect Module in Zoho Trident
Hi I really like where Zoho Trident is going. Having Mail and Cliq in one place is especially powerful. However, Zoho Connect really needs to be included to make this a true communication and collaboration hub. I would like to request that Zoho Connect
Zoho FSM API Delete Record
Hi FSM Team, It would be great if you could delete a record via API. Thank you,
File Field Validation
Hello all, We are tracking our customer NDA agreements in our CRM and have created 2 fields to do so, an execution date field and a file upload field. I want to create a validation rule to ensure that when the execution date field is populated that the
100 record view limitation
I have just migrated from another CRM and am starting in ZOHOcrm with over 5000 contacts. It seems that my searches and sorts are limited to 100 live records....or am I missing something. This seems to be very limiting...in a lot of scenarios (mass email,
ZUG Meet-ups are back - Across India (December 2025)
The Zoho User Group (ZUG) meet-ups are back, and this time, we’re travelling across India to reconnect with our amazing community! From Chennai to Delhi, Bengaluru to Mumbai, we can’t wait to meet you all in person and talk everything Zoho SalesIQ, automation,
Categorise Attachments
We take ID, proof of address, right to work documentation and more. I can upload a single file in to field, but we often receive multiple files for each category e.g. someone may send a separate file for the front and back of their national ID card. My team don't have time to manipulate the files in order to upload them as a single file. The options, as far as I can tell, would be to create additional fields on attachments in order to categorise what the file is, or to be able to upload single
Scheduling a meeting for just a 1:1 phone call
My business is B2C and many of my customer's don't want to engage in an online meeting for what can be handled in a regular phone call. I am trying to create a new meeting invitation, but there is no venue optoin for "phone call". How are other's handling
Need more details on API Usage Dashboard
Hi Team, We have implemented Zoho Expense for a client and has done some integrations with well known third party ERP via api. Recently we have noticed a huge spike in the API consumption. But we couldn't get the root cause for the same. I accept there
Next Page