Kaizen #80 - COQL API - Part I
Hi everyone! Welcome back to another week of Kaizen!
This week, we will discuss the COQL Queries in detail.
COQL (CRM Object Query Language) is a powerful query language based on SQL syntax that allows users to write their own queries and fetch records using module API names and field API names. In this post, we will discuss the operators supported by COQL in detail and also provide examples to help you understand better. Please note that the information in the article holds true for version 4 of Zoho CRM APIs.
When should you use the Query API?
Use Query API when you want to query for a module's data and/or it's look up related data using various comparators, or for records that fall into a custom view without actually creating one. For example, if you want to query for all products in a specific price range, with a 5 star rating and sort the results by price, use the Query API. Similarly you can use Query API to query for records from cross-modules (linked via lookup field), such as filtering products based on the vendor's details.
What types of queries are supported by COQL?
COQL supports only the SELECT query, which is used to select data from the CRM, based on the conditions specified by the clauses.
Here is a sample query:
SELECT {field_api_names} FROM {module_api_name} WHERE {field_api_name} {comparator} {logical_operator} {value} ORDER BY {field_api_name} ASC/DESC LIMIT {limit} OFFSET {offset}
What are the clauses & aggregate functions supported by COQL?
WHERE - used to select records based on specific conditions.
FROM - specifies the module from which to fetch the records.
ORDER BY - used to sort the results in ascending or descending order.
LIMIT - used to limit the number of records returned by the query.
OFFSET - used to skip a specified number of records while retrieving the records.
{ "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" } |
For example, the above query retrieves the Last_Name, First_Name, Mobile and Final_score of all Leads whose status equals 'Not Contacted'. The results will be sorted in descending order based on the Final_Score field, and only the 5 records after skipping the first 10 will be returned.
NOTE : You can also use the syntax "LIMIT offset, limit" to achieve the same result. For 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, 10" } |
Aggregate functions can perform calculations on a set of values and return a single value.
SUM() - to get the sum of the values of an aggregate field in a module.
MAX() - to get the maximum value of an aggregate field.
MIN() - to get the minimum value of an aggregate field.
AVG() - to get the average value of the values of a field.
COUNT() - to get the number of records that satisfy the criteria.
Wildcard Character Support in COQL
In COQL queries, the only supported wildcard character is %. This % wildcard can be used with the like operator to achieve functionality similar to the contains, starts_with and ends_with operators. For instance, '%tech" queries for field values ending with tech, 'C%' queries for the values starting with C, and '%tech%' translates to contains 'tech'.
Supported field types and Comparators
The following table gives a gist of the field types whose data you can query for, and the comparators for each field type.
| Field Type | Comparators |
| Text and Picklist | =, !=, 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 | = |
Supported field types and Comparators
1) Text and Picklist Fields
Supported comparators : =, !=, like, not like, in, not in, is null, is not null. Please note that the like operator is used for starts_with, ends_with, contains, and not like operator is used for not contains.
Sample Query 1: (=, like, in)
{ "select_query": "SELECT First_Name, Last_Name FROM Leads WHERE (((Lead_Status = 'Pre-Qualified') and (Company like '%zylker%')) and Industry in ('Technology', 'ERP'))" } |
This SELECT query retrieves the First_Name and Last_Name of all Leads whose Lead_Status is Pre-Qualified, Company contains zylker, and Industry is either Technology or ERP.
Sample Query 2:(not in, like, not null)
{ "select_query": "SELECT First_Name, Last_Name FROM Leads WHERE (((Lead_Status not in ('Closed-Won', 'Closed-Lost')) and (Lead_Source like '%Web%')) and (Skype_ID is not null)) LIMIT 2" } |
This query will return the first name and last name of all leads whose lead status is not Closed-Won or Closed-Lost, whose Lead Source fields contains web, and whose Skype ID field is not empty.
Sample Query 3:( !=, not like, null)
{ "select_query": "SELECT First_Name, Last_Name, Mobile FROM Leads WHERE (((Lead_Source != 'Webinar') and (City not like '%New York%')) and (Skype_ID is null))" } |
This query retrieves the first name, last name and mobile of leads where the lead source is not a webinar, the city is not New York, and the Skype ID field is empty or null.
2. Lookup Fields
=, !=, in, not in, is null, is not null are the supported comparators for lookup fields. If you want to get the name of the lookup field in the response, you must include the field API name in the query. Otherwise, the system will return only the ID of the field.
Sample Query 1: (=, not in, !=)
{ "select_query": "select Last_Name, First_Name, Full_Name, Account_Name, Owner from Contacts where (((Account_Name.Account_Name != 'Zylker') and (Owner = 4876876000000327001)) and Vendor_Name.Vendor_Name not in ('Skytech','SR')) LIMIT 2" } |
This query retrieves the last name, first name, full name, account name, and owner of two contacts whose account name is not Zylker, whose owner ID is 4876876000000327001, and whose vendor name does not contain Skytech or SR.
Sample Response:
{ "data": [ { "First_Name": "John", "Full_Name": "John Wilson ", "Owner": { "id": "4876876000000327001" }, "Last_Name": "Wilson ", "Account_Name": { "id": "4876876000000333089" }, "id": "4876876000000333182" }, { "First_Name": "Josephine", "Full_Name": "Josephine Darakjy", "Owner": { "id": "4876876000000327001" }, "Last_Name": "Darakjy", "Account_Name": { "id": "4876876000000333090" }, "id": "4876876000000333183" } ], "info": { "count": 2, "more_records": true } } |
In this query, the id of the account is returned but not the name since we have not specified the field API name in the query. To fetch the field name, specify the field API name in the query. Refer to the following sample query to know how.
Sample Query 2: (in, is null, is not null)
{ "select_query": "SELECT Deal_Name, Account_Name.Account_Name, Created_Time FROM Deals WHERE (((Account_Name.Account_Name in ('Grayson','Zylker')) and (Owner is not null)) and (Contact_Name is null)) ORDER BY Created_Time DESC LIMIT 2" } |
This query retrieves the Deal Name, Account Name, and Created Time of the 2 most recently created Deals whose Account Name contains Zylker or Grayson, and do not have a Contact Name associated with them but have a Deal Owner associated with them. The results will be ordered in descending order by the Created Time.
Sample Response:
{ "data": [ { "Deal_Name": "Westborne Deal", "Created_Time": "2023-04-06T10:04:02+05:30", "Account_Name.Account_Name": "Grayson", "id": "4876876000003548001" }, { "Deal_Name": "Eastwing Deal", "Created_Time": "2023-04-05T19:10:55+05:30", "Account_Name.Account_Name": "Grayson", "id": "4876876000003526011" } ], "info": { "count": 2, "more_records": true } } |
In the same query, if you want to use the Account ID instead of the name, replace ((Account_Name.Account_Name in ('Grayson','Zylker')) with ((Account_Name in (4876876000001236083, 4876876000002799001)) to include the IDs instead of the field API names and the account names.
{ "select_query": "SELECT Deal_Name, Account_Name.Account_Name, Created_Time FROM Deals WHERE (((Account_Name in (4876876000001236083, 4876876000002799001)) and (Owner is not null)) and (Contact_Name is null)) ORDER BY Created_Time DESC LIMIT 2" } |
3. Date, DateTime, Number, Currency Fields
=, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null are the supported comparators for these fields.
Sample Query 1: (between, <, >)
{ "select_query": "SELECT Deal_Name, Amount, Stage, Probability FROM Deals WHERE (((Closing_Date between '2023-01-01' and '2023-03-31') and (Probability < 99)) and (Amount > 10000))"} |
This query retrieves the deal name, amount, stage, and probability of all deals whose closing date is between January 1, 2023 and March 31, 2023, and whose probability is less than 99 and amount is greater than 10000.
Sample Response:
{ "data": [ { "Deal_Name": "Chapman Deal", "Amount": 2500000, "Probability": 97, "Stage": "Closed Won", "id": "4876876000003550011" } ], "info": { "count": 1, "more_records": false } } |
Sample Query 2: (<=, !=, >=)
{ "select_query": "SELECT Product_Name, Qty_in_Stock, Vendor_Name, Cost_Price FROM Products WHERE (((Sales_End_Date <= '2023-04-30') and (Qty_in_Stock != 0)) and (Cost_Price >= 500))" } |
This query retrieves the product name, quantity in stock, vendor name, and cost price for all products whose sales end date is on or before April 30, 2023, whose quantity in stock is not zero, and whose cost price is greater than or equal to 500. Since Vendor_Name is a lookup field, only the ID will be returned in the response.
Sample Response
{ "data": [ { "Cost_Price": 2000, "Vendor_Name": { "id": "4876876000001039017" }, "Product_Name": "Sigma", "Qty_in_Stock": 30, "id": "4876876000001036109" } ], "info": { "count": 1, "more_records": false } } |
Sample Query 3: (not between, is not, >)
{ "select_query": "SELECT Last_Name, First_Name, Referred_By.Full_Name FROM Leads WHERE (((Final_Score not between 10 and 20) and (Annual_Revenue is not null)) and (No_of_Employees > 200)) LIMIT 1" } |
This COQL query selects the last name, first name, and the full name of the lead's referred by field, for one lead whose final score is not between 10 and 20, annual revenue is not null and whose number of employees is greater than 200.
Sample Response
{ "data": [ { "First_Name": "Chau", "Last_Name": "Kitzman", "id": "4876876000000333403", "Referred_By.Full_Name": "Simmons Truhlar" } ], "info": { "count": 1, "more_records": true } } |
Sample Query 4: (>, not in, =, is null)
{ "select_query":"SELECT PO_Number, PO_Date, Status, Discount FROM Purchase_Orders WHERE (((PO_Date = '2023-04-06') and (Due_Date not in ('2023-04-10','2023-04-11', '2023-04-12'))) or ((Discount > 10) and (Requisition_No is null)))" } |
This query selects the PO_Number, PO_Date, Status, and Discount from the Purchase_Orders module where the PO_Date is equal to '2023-04-06' and Due_Date is not any of '2023-04-10', '2023-04-11', or '2023-04-12'; OR Discount is greater than 10 and Requisition_No is null. In this query, we have used both AND and OR operators to combine the clauses.
Sample Response
{ "data": [ { "Status": "Created", "PO_Date": "2022-05-10", "Discount": 9990, "PO_Number": "PO-JL-3876", "id": "4876876000001125176" }, { "Status": "Created", "PO_Date": "2023-04-06", "Discount": 370.37, "PO_Number": "PO-DA-1932", "id": "4876876000003561019" } ], "info": { "count": 2, "more_records": false } } |
We hope you found this post useful and that it has given you a better understanding of COQL queries. In our next post, we will discuss the rest of the field types, aggregate functions with more examples, and provide more queries to help you get started.
If you have any questions or feedback, please let us know in the comments below, or write to us at support@zohocrm.com. We would love to hear from you!
Additionally, if you have any questions about the COQL API or how to construct a query, kindly let us know in the comments.
Additional Reading:
Access your files securely from anywhere
Centralize Knowledge. Transform Learning.
All-in-one knowledge management and training platform for your employees and customers.
New to Zoho Recruit?
Zoho Developer Community
New to Zoho LandingPage?
Zoho LandingPage Resources
New to Zoho Survey?
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho CRM Plus?
Deliver unforgettable customer experiences
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Zoho Marketing Plus?
Everything you need to run your marketing
New to Bigin?
Topic Participants
Anu Abraham
DataWarehouse
dkvagency
Jeganprabhu S
Anamika Prasanth
Sticky Posts
Kaizen #198: Using Client Script for Custom Validation in Blueprint
Nearing 200th Kaizen Post – 1 More to the Big Two-Oh-Oh! Do you have any questions, suggestions, or topics you would like us to cover in future posts? Your insights and suggestions help us shape future content and make this series better for everyone.Kaizen #226: Using ZRC in Client Script
Hello everyone! Welcome to another week of Kaizen. In today's post, lets see what is ZRC (Zoho Request Client) and how we can use ZRC methods in Client Script to get inputs from a Salesperson and update the Lead status with a single button click. In thisKaizen #222 - Client Script Support for Notes Related List
Hello everyone! Welcome to another week of Kaizen. The final Kaizen post of the year 2025 is here! With the new Client Script support for the Notes Related List, you can validate, enrich, and manage notes across modules. In this post, we’ll explore howKaizen #217 - Actions APIs : Tasks
Welcome to another week of Kaizen! In last week's post we discussed Email Notifications APIs which act as the link between your Workflow automations and you. We have discussed how Zylker Cloud Services uses Email Notifications API in their custom dashboard.Kaizen #216 - Actions APIs : Email Notifications
Welcome to another week of Kaizen! For the last three weeks, we have been discussing Zylker's workflows. We successfully updated a dormant workflow, built a new one from the ground up and more. But our work is not finished—these automated processes are
New to Zoho TeamInbox?
Zoho TeamInbox Resources
Zoho CRM Plus Resources
Zoho Books Resources
Zoho Subscriptions Resources
Zoho Projects Resources
Zoho Sprints Resources
Qntrl Resources
Zoho Creator Resources
Zoho CRM Resources
Design. Discuss. Deliver.
Zoho Show Resources
Get Started. Write Away!
Writer is a powerful online word processor, designed for collaborative work.
Zoho CRM コンテンツ
-
オンラインヘルプ
-
Webセミナー
-
機能活用動画
-
よくある質問
-
Ebook
-
-
Zoho Campaigns
- Zoho サービスのWebセミナー
その他のサービス コンテンツ
Nederlandse Hulpbronnen
ご検討中の方
Recent Topics
Need Customer Item Inward Module along with QC
Need Customer Item Inward Module along with QC 1. Using Transfer Orders hit the item balance sheet 2. Items without inventory it becomes difficult for tracking purpose. 3. Custom Modules become tedious to capture multiple items, item subforms are notPossible to Turn Off Automatic Notifications for Approvals?
Hello, This is another question regarding the approval process. First a bit of background: Each of our accounts is assigned a rank based on potential sales. In Zoho, the account rank field is a drop-down with the 5 rank levels and is located on the accountWill be possible to create a merge mail template for products?
Hi, we would need to create a mail merge template for products (native) module. Will be possibile? or do you have a smart solutions to merge products data with a mail merge? thanks ChrisMaximum incoming email (attachment size) limit
I see that the maximum size of received emails can now be set to 20MB, however I'd still like to see it be larger. It's embarrassing when a client sends me an email with a large attachment and I can't receive it. 50, even 100 MB seems reasonable these[Free Webinar] Learning Table Series - Multi-currency management in Zoho Creator
Hello everyone, We’re excited to invite you to another edition of the Learning Table Series webinar. As you may already know, we've moved to a purpose-based approach in the Learning Table Series this year. Each session now focuses on how a Zoho CreatorLESS_THAN_MIN_OCCURANCE - code 2945
Hi I'm trying to post a customer record to creator API and getting this error message. So cryptic. Can someone please help? Thanks VarunRanking by group in report
Dears, I am new to Zoho Analytics and I would like to ask you guys help to creating a ranking column. Report type: Pivot Matter: I want to create a ranking column on the right of Percentage. This ranking is group by column Type, and ranking by Final Score/Percent.How do I add a project template to a pre-existing project?
Help....How do I add a project template to a pre-existing project? Right now I have to delete the old project, make a new one and add the project template at the time of creating the new project.Passing the image/file uploaded in form to openai api
I'm trying to use the OpenAI's new vision feature where we can send image through Api. What I want is the user to upload an image in the form and send this image to OpenAI. But I can't access this image properly in deluge script. There are also some constraintsUsers may not pick the fields to be shown as columns in the Choose Account window when creating a new Deal record
Hi there, by talking with other users I found out that I, as an Admin, am the only one who can pick fields to be shown as columns in the Choose Account window when creating a new Deal record. In fact, if other users click on the "Add Column" symbol onWhy is the ability Customize Calls module so limited?
Why can't I add additional sections? why can't I add other field types than the very limited subset that zoho allows? Why can I only add fields to the outbound/inbound call sections and not to the Call Information section?Report Template - How to remove page break after each record?
Hi, We have report template for a list report. It looks good at screen. But when printing, it creates a page break after each record. How to remove the it? So we can print multiple records in same page. Please look at the attached screenshots. Report Template Report Print PreviewCalendar report with order options and more quick view templates
I think many of us regularly work with calendar-style reports. It would be great to be able to customize the quick view with new templates and have options to sort the entries for each day of the calendar by different criteria. I think this is an interestingDeprecation Notice: OpenAI Assistants API will be shut down on August 26, 2026
I recieved this email from openAI what does it means for us that are using the integration and what should we do? Earlier this year, we shared our plan to deprecate the Assistants API once the Responses API reached feature parity. With the launch of Conversations,Shall we play a game?
Presenting the very first game created using ZOHO Creator: Tic-Tac-Toe (or noughts and crosses) I made this to challenge myself and employ some of the new features of ZOHO Creator. I must admit that the code is very literal and not too elegant. There are plans to improve on the machine AI and streamline the code over time. Currently the code makes extensive use of functions for the machine "AI" - there are 12 of these. The machine AI can be tricked, so to counteract that I made it exceedingly arrogantCRM notes
I want to be able to add notes to a task that do not necessarily get rolled up into an account or contact. For example, I tasks to work on a Court Order for John Doe divorce account. There might be lots of updates (in the form of notes) that employeesConfluence export to zoho learn
Hello Is there any known way to export Confluence spaces to zoho learn ?Limitation in chart of accounts
There is a limitation of 4000 accounts in chart of accounts Zoho needs to remove this limitZoho People API, Inactive users
Hi, I would like to export using the Zoho People API using Fetch Leave Records API V2 | Zoho People API. However it only pulls active user's leaves. I want all users, active and former employees records.Empty folders are now appearing in the sidebar...
...and the folder list is now auto-collapsed by default with no way to change. Neither of these recent updates are useful or user-friendly. ==================== Powered by Haiku https://www.haiku.co.uk ====================Client Script | Update - Client Script Support For Custom Buttons
Hello everyone! We are excited to announce one of the most requested features - Client Script support for Custom Buttons. This enhancement lets you run custom logic on button actions, giving you greater flexibility and control over your user interactions.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 everythingCreate custom rollup summary fields in Zoho CRM
Hello everyone, In Zoho CRM, rollup summary fields have been essential tools for summarizing data across related records and enabling users to gain quick insights without having to jump across modules. Previously, only predefined summary functions wereMonthly Webinar - Getting Started with Zoho LandingPage
Are you building your first landing page and want a little guidance? Join our monthly Getting Started with Zoho LandingPage webinar and learn how landing pages fit into your marketing strategy, drive lead generation, and improve conversions. Here’s whatView all email threads directly from record's History and Interactions
Greetings all, We've introduced the ability to view complete email thread conversations directly from records' History and Interactions sections, along with email delivery status and sentiment insights on the Interactions page—which makes it easier forMarketing Tip #22: Check website and navigation for broken links
Do you inspect your online store frequently? If not, here's your reminder to do it now. Broken links frustrate customers and make your store feel unreliable. Whether it’s a missing product page, an outdated menu item, social media accounts, or a brokenOnline Payment Fees
We don't take many online credit card payments so the merchant service provider (PayPal) charges us the 2.9% fee for processing the amount. I would like the ability for the fee to be automatically added to the total amount for "ease of payment". We'dpush notification to Cliq when user is @mentioned in CRM notes
push notification to Cliq when user is @mentioned in CRM notes. Currently users that is @mentioned gets an email to be notified. User/s that is @mentioned should get a Cliq notification.How to make entries for restaurant sales in Zoho Books?
Hello, I'm new to Zoho Books. I need to understand how to record our restaurant sales in Zoho Books. We can't raise invoices for each bill from the daily sales report generated by our POS (duplicated effort). We need a means of entering cumulative salesIntegrate with WooCommerce using Wordpress Plugin
We’re thrilled to announce a powerful update to the Zoho Marketing Automation WordPress plugin with WooCommerce integration! This enhancement enables new possibilities for businesses running online stores using WooCommerce, empowering them to merge seamlessHow do I sync multiple Google calendars?
I'm brand new to Zoho and I figured out how to sync my business Google calendar but I would also like to sync my personal Google calendar. How can I do this so that, at the very least, when I have personal engagements like doctor's appointments, I canDomain already exists
Hi, I tried to add my domain creativecolumnist.com but its showing as Domain already exist. Please release if it has already been associated with Zoho, I am the real owner of that domain.Select forwarding
For Zoho mail online on PC in web browser: I had forwarding enabled in section "Mail accounts / Forwards", and I also have some filters that send emails from some unwanted senders to Archive. But it doesn't work as intended, it forwards everything (obviouslyPay Pal Paylater button
I am testing the paypal setup to have my customers pay invoices with paypal and credit cards. But it seems to have two options. Paypal and Pay later. I don't want my customers access to pay later feature. How can I turn that part off.. My other integrationsystem not picking my default custom service report template
Can you tell me why when we create a service report always pick the (standard old) template? Even when I have a custom service report selected as Default.Restrict Appointment Booking to Approved Clients
Dear Zoho Bookings Support Team, We'd like to propose a feature enhancement for managing appointments within Zoho Bookings. This feature would ensure only pre-approved clients can schedule meetings. Desired Functionality: We propose the introduction ofAutomate Credit Card Surcharge
Is there a way to create an automation that will add a 3.0% credit card surcharge to a subscription whenever a customer pays via credit card?I have the item field and Quantity field in the sub form , on the submit of the form if the quantity is grater than inventory means show alert on submit validation only for item type goods ,
I have the item field and Quantity field in the sub form , on the submit of the form if the quantity is grater than inventory means show alert on submit validation . Stock Check Validation only for item type goods , not for item type service . For theDisappearance of all articles in the knowledge base
Hi there! It seems like all of our articles have disappeared from the knowledge base: However, the names still appear in the "sort articles" section:Logic for sending to a non-primary email address
Hi, I have a scenario where contacts are able to sign up for emails with 2 different email addresses (example: work, personal). I've mapped both to Campaigns from Zoho CRM, but when I go to target an email only the primary email addresses are pulling in. How can I update this to look at both of the email addresses - or specifically the secondary email address in Campaigns? Thanks, JennyNext Page