Kaizen #81 - COQL API - Part II

Kaizen #81 - COQL API - Part II

Hello everyone! 
Welcome to another week of Kaizen! 
In continuation to last week's post on COQL API, we will discuss the rest of the field types and their supported operators with detailed examples in this post. 

Supported field types and Operators

Please refer to the COQL API - Part I Kaizen post for the first three field types and their supported operators.

4) Boolean

The supported operator for Boolean field is =. You should provide the value as either true or false.

Sample Query

{
 "select_query" : "select Product_Name, Product_Code from Products where Product_Active = 'false' limit 2"
}

This sample query selects the Product_Name and Product_Code fields from the Products module where Product_Active is false, and limits the result to only 2 records.
Sample Response:
{
    "data": [
        {
            "Product_Code": "SR-001",
            "Product_Name": "Sigma",
            "id": "4876876000001036109"
        },
        {
            "Product_Code": "SR-003",
            "Product_Name": "Alpha",
            "id": "4876876000001039006"
        }
    ],
    "info": {
        "count": 2,
        "more_records": false
    }
}

what_Id support in COQL

=, !=, in, not in, is null, is not null are the supported operators. what_Id support is extended only to Tasks, Calls and Events. The what_Id field could be a lead or a contact.

Sample Query

{
    "select_query": "select 'What_Id->Leads.Last_Name' from Tasks where (('What_Id->Leads.id' in ('4876876000000900134','4876678000000900987')) AND ('What_Id->Leads.Industry' != 'ERP'))"
}

This sample query queries for the last name of leads associated with tasks where the lead ID is either '4876876000000900134' or '4876678000000900987', and the lead's industry is not 'ERP'.
Sample Response:
{
    "data": [
        {
            "What_Id->Leads.Last_Name": "Perry",
            "id": "4876876000001103044"
        }
    ],
    "info": {
        "count": 1,
        "more_records": false
    }
}

Roles and Profiles support in COQL

COQL supports roles and profiles lookup fields, allowing users to retrieve the owner's profile details such as ID, name etc, and the owner's role details using COQL queries. The supported comparators are =, !=, in, not in, is null, is not null.

Sample Query

{
    "select_query": "select Owner.role.id,Owner.role.name,Owner.role.reporting_to, Owner.role.share_data_with_peers, Owner.role.description, Owner.profile.id from Leads where Last_Name is not null LIMIT 1"
}

This query fetches the Profile details and Role details of the owner of one record from the Leads module. 

Sample Response:
{
    "data": [
        {
            "Owner.role.reporting_to": null,
            "Owner.profile.id": "4876876000000026011",
            "Owner.role.name": "CEO",
            "Owner.role.description": "crm.security.role.admin.desc",
            "Owner.role.share_data_with_peers": true,
            "Owner.role.id": "4876876000000026005",
            "id": "4876876000000333403"
        }
    ],
    "info": {
        "count": 1,
        "more_records": true
    }
}

Alias support

Supported operators : =, !=, in, not in, is null, is not null
Alias support in COQL allows users to simplify complex SELECT field names by giving them custom names or aliases. Using aliases in the SELECT column and ORDER BY class can make column names easier to understand and remember. For instance,  What_Id->Leads.Last_Name from our previous example can be given an alias in the SELECT query as follows.

Sample Query

{
    "select_query": "select 'What_Id->Leads.Last_Name' AS 'Lead Name' from Tasks where (('What_Id->Leads.id' in ('4876876000000900134','4876678000000900987')) AND ('What_Id->Leads.Industry' != 'ERP'))"
}

Sample Response:
{
    "data": [
        {
            "id": "4876876000001103044",
            "Lead Name": "Perry"
        }
    ],
    "info": {
        "count": 1,
        "more_records": false
    }
}

If you compare this response with the sample response in the what_Id section above, you can see that the key 'What_Id->Leads.Last_Name'  is replaced with the Alias 'Lead Name'  in here. 

Aggregate Function Support in COQL

Aggregate functions in COQL allow you to perform calculations on groups of records in a module. The available aggregate functions include SUM, MIN, MAX, AVG, and COUNT.

1. SUM

Use this function to sum up the values of an aggregate field in a module. 

Sample Query 1

{
    "select_query":"select SUM(Grand_Total) from Quotes WHERE Quote_Stage = 'Draft' "
}

This Query uses the SUM function to calculate the total value of the Grand_Total field for all quotes in the Draft stage in the Quotes module.
Sample Response:
{
    "data": [
        {
            "SUM(Grand_Total)": 1073660
        }
    ],
    "info": {
        "count": 1,
        "more_records": false
    }
}

Sample Query 2

{
    "select_query": "select SUM(Grand_Total), Account_Name.Account_Name AS 'Account Name', Billing_Country from Invoices where ((Status != 'Paid') and ((Billing_Country ='USA') OR (Billing_Country = 'Canada'))) GROUP BY Account_Name.Account_Name, Billing_Country ORDER BY 'Account Name' ASC"
}

This query retrieves the total SUM of the Grand_Total field, along with the Account_Name and Billing_Country from the Invoices module for those records whose Status field is not equal to 'Paid' and the Billing_Country field is either 'USA' or 'Canada'. The results are grouped by the Account_Name and Billing_Country fields and ordered in ascending order based on the 'Account Name' field.

Note that the 'Account Name' field is specified using the AS keyword to provide an alias for the Account_Name.Account_Name field. We have used the alias for the ORDER BY clause.
Sample Response:
{
    "data": [
        {
            "Account Name": "SR Systems",
            "Billing_Country": "USA",
            "SUM(Grand_Total)": 2430000
        },
        {
            "Account Name": "SR Systems",
            "Billing_Country": "Canada",
            "SUM(Grand_Total)": 60000
        },
        {
            "Account Name": "Zylker systems",
            "Billing_Country": "Canada",
            "SUM(Grand_Total)": 378000
        }
    ],
    "info": {
        "count": 3,
        "more_records": false
    }
}

The response JSON contains the SUM of the Grand_Total fields, Account Name and Billing_Country for each unique combination of Account Name and Billing Country fields that satisfies the filter criteria specified in the query. 

2. MAX

Use this aggregate function to find the largest value of a field in a module. 

Sample Query

{
    "select_query": "select MAX(Amount) AS 'Maximum Deal Amount', Account_Name.Account_Name from Deals where ((Stage = 'Closed Won') AND  (Type = 'Existing Business')) Group by Account_Name.Account_Name"
}

This Query selects the maximum Deal Amount for each account from the Deals module where the stage is Closed Won and the type is Existing Business.
Sample Response:
{
    "data": [
        {
            "Maximum Deal Amount": 6750000,
            "Account_Name.Account_Name": "Benton"
        },
        {
            "Maximum Deal Amount": null,
            "Account_Name.Account_Name": "King"
        },
        {
            "Maximum Deal Amount": 945000,
            "Account_Name.Account_Name": "Morlong Associates"
        }
    ],
    "info": {
        "count": 3,
        "more_records": false
    }
}

3. MIN

Use this aggregate function to find the smallest value of a field in a module. For example, you can use this function to find the Lowest Annual Revenue in the Leads module.

Sample Query: 1:

{
        "select_query": "select MIN(Annual_Revenue) from Leads where Annual_Revenue is not null"
}

Sample Response:
{
    "data": [
        {
            "MIN(Annual_Revenue)": 348.3
        }
    ],
    "info": {
        "count": 1,
        "more_records": false
    }
}

4. AVG

Use this aggregate function to find the average value of a field in a module. 

Sample Query

{
    "select_query": "SELECT AVG(Amount) AS 'Average Deal Amount' FROM Deals WHERE Type = 'New Business'"
}

This query calculates the average deal amount for all new business deals in the "Deals" module. 
Sample Response:
{
    "data": [
        {
            "Average Deal Amount": 877366.8359999999
        }
    ],
    "info": {
        "count": 1,
        "more_records": false
    }
}

5. COUNT

Use this aggregate function to count the number of records in a module satisfying a certain criteria. You can use this aggregate function only for number, lookup, and picklist fields.

Sample Query:

{
    "select_query": "SELECT COUNT(Product_Category) FROM Products WHERE Product_Active = 'false'"
}
This sample query queries for the total number of inactive products in the Product module. Please be aware that if there are any products with no value for the product category field, they will not be included in the count.

Important points to remember

  • COQL keywords are not case-sensitive.
  • To prevent ambiguity, the criteria listed in the WHERE clause must be properly enclosed in brackets. If WHERE has more than two criteria such as A, B, and C, use them as either of the following:
    • (A and (B and C))
    • ((A and B) and C)
    • A and (B and C)
    • (A and B) and C.
  • COQL supports only SELECT queries with WHERE, FROM, ORDER BY, LIMIT, and OFFSET clauses and aggregate functions SUM(), MAX(), MIN(), AVG(), and COUNT().
  • The default sort order is ascending based on record ID if ORDER BY is not specified.
  • Use the following format for :
    • Date : YYYY-MM-DD
    • DateTime : YYYY-MM-DDTHH:mm:ss+/-HH:mm
  • The default value for LIMIT is 200 and OFFSET is 0.
  • A maximum of 10,000 records can be fetched using the API.
  • Multi-line fields cannot be used in criteria.
  • COQL doesn't support consent_lookup, subform, profileimage, multiselectlookup, fileupload datatypes.
  • A maximum of 25 criteria can be included in the WHERE clause.
  • A maximum of 50 field API names can be used in the SELECT column of the query.

     Data Type
     Supported Operators
     text
     =, !=, like, not like, in, not in, is null, is not null
     picklist
     =, !=, like, not like, in, not in, is null, is not null
     ownerlookup
     =, !=, in, not in, is null, is not null
     lookup
     =, !=, in, not in, is null, is not null
     date
     =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null
     datetime
     =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null
     currency
     =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null
     boolean
     =
     email
     =, !=, like, not like, in, not in, is null, is not null
     phone
     =, !=, like, not like, in, not in, is null, is not null
     website
     =, !=, like, not like, in, not in, is null, is not null
     double
     =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null
     bigint
     =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null
     integer
     =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null
     formula
     If the return type is:
          Decimal/Currency/Date/Datetime : =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null
          String : =, !=, like, not like, in, not in, is null, is not null
          Boolean : =
     autonumber
     =, !=, like, not like, in, not in, is null, is not null
We hope you found this post useful. We will meet you next week with another interesting topic.

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! 


Additional Reading:








    • 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 this

    • Kaizen #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 how

    • Kaizen #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

    • Recent Topics

    • Zoho Desk Limitations

      Good day, all, I would like to know whether others share my frustration with some of Zoho's limitations. Don't get me wrong, I like Desk (and I also have a subscription for Analytics), I have been with them for close to 10 years, and unfortunately, I

    • New Mandatory One-Click Unsubscribe Link Overshadowing Custom Unsubscribe Link

      I was recently informed by Zoho CRM Support that they are now mandated by the large email service providers like Google and Yahoo to provide a one-click unsubscribe option in the header (not the body) of all mass emails. I have a custom unsubscribe link

    • how to undoreconciled transaction

      how to undo reconciled transaction

    • Zoho Sign for Zoho Projects

      Documents authorization and validation with signature is now easier in project management with Zoho Projects' Zoho Sign extension. Sign documents or send them for signatures directly from tasks and issues using Zoho Sign extension in Zoho Projects. Extension

    • Deluge Learning Series – Best Practices in Deluge | December 2025

      We’re excited to bring you the next session of the Deluge Learning Series, focused entirely on writing cleaner, faster, and more reliable Deluge code. In this edition, we’ll dive deep into the essential best practices every Deluge developer should follow

    • Associate a Contact to a Campaign using deluge?

      If I want to add one record to another related list, which zoho function do I use? zoho.updateRelatedRecord is not it I don't think.  I simply want to take a "Contact" and associate it to a "Campaign" using deluge.  Can anyone help? contactId = 5122008000000796037;

    • Add "Groups" above "Users" in the Zoho Forms Left Menu

      Hi, We have Groups but you have to go to a Form and Share or use the Directory App to manage them, please add "Groups" above "Users" in the Zoho Forms Left Menu so you can manage them in 1 place within Zoho Forms too. Thanks Dan

    • Zoho Books | Product updates | October 2025

      Hello users, We’ve rolled out new features and enhancements in Zoho Books. From iOS 26 updates to viewing reports as charts, explore the updates designed to enhance your bookkeeping experience. Zoho Books Updates for Apple Devices At WWDC 2025, Apple

    • Adding Taxes to paid consultations in Zoho Bookings

      I created a 'paid' consultation under Zoho Booking and integrated it with payment gateways for online/instant payment before a booking is done. How can I add 'taxes' to the price of consultation? I can add taxes to other Zoho apps (liks Books, Checkout,

    • Zoho CRM Reports Module on Mobil App

      I have the mobile app and the reports module doesn't appear in the sidebar for some reason. I saw a Youtube video where the user had the Reports module on mobile. Is there a setting to show it on mobile? Thanks.

    • Inquiry on Help Centre Tab Customisation

      Hi Zoho team, I’m wondering if it’s possible to further customise the Help Center tabs, specifically the descriptions under Knowledge Base, Community, and Tickets. While the current setup allows customising tab names, being able to tailor the descriptions

    • Passing a form object to a function

      Suppose I have a sort_order field in multiple tables and I want to increment it by +1 onCreate of a new record. Is there a way to pass the form object as an argument into the function to keep things DRY? The following function from Zia works, but I'd

    • Can you copy and paste a page within a form?

      I have been looking at trying to copy/duplicate a whole page within a form. I can't see how I can do this without having to add all the data in again. This is very time consuming. Any help would be great.

    • Unable to sort as Descending order

      Trying to change the sort order for a lookup field (checkboxes) from Ascending to Descending and keep getting an error in Deluge that the order must be Ascending Did anyone ran into this? Thanks Eyal

    • How do I copy an email message to one or more other folders?

      I can move a message to another folder using the "move to" option but I can't figure out how to copy a message. 

    • New feature request: Allow copy of email message to another folder

      Hello Zoho team, This is a suggested new feature to allow copy an email message to either another folder or the same folder. Within the same Zoho account. This is not a support request about "label". This is a suggested new feature to allow copies of

    • Creator Simplified #11: Create a custom button to download files

      Hey there! This time, let's see how to create custom buttons to download the files uploaded in the file upload field. Sample use-case A training app has a module that provides the materials for all the available courses. Students can click on the corresponding

    • Hide horizontal lines at bottom of form

      Hi I have a page with 3 forms embedded in html snippets. On each of the forms I can hide the submit button easily but I am left with the horizontal lines that bound that section and I cannot figure out to remove them (see below for the two sets of horizontal

    • Autosaving of Form Field

      HI can anyone suggest a way of autosaving or simulating an autosave function for a specific field in a form. The story is as follows. I have a bunch of technicians who write notes on how they do a repair. Sometimes they write for a couple of hours the troubleshooting process. If they click incorrectly, press ESC, Cancel or the browser crashes the information is lost which can be heartbreaking so my guys use notepad and then copy the information in. The only way around this is to click submit on the

    • Ask the Experts 26: Brighten every customer interaction with Zoho Desk all year long

      Hello everyone, Greetings and welcome to Ask the Experts 26. As we wrap up 2025, we are excited to invite you to the 26th episode of our Ask the Expert series. 🎄The Merry Metrics Edition = Best of Zoho Desk [Best Practices + Holiday Automation + Year-End

    • Pricing calculator

      Alright, so I'm trying to create a pricing calculator/pricing report generator to use in a cabinet shop. I did all the logic and such, and the calculator works. I have a few other things that I would need it to be able to do though, I'll do my best to

    • Bug Details

      In the Bug detail the milestoneid is missing

    • Is there a way to make a button scroll down?

      Looking to have a button on a landing page scroll down to another section on the page. Any recomendations outside of coding?

    • Stop completed task lists from disappearing?

      Is there any way to stop projects from making tasks lists disappear when all the tasks in the list are completed? That's one of those little things where we're constantly fighting the product. For instance we have some projects which are ongoing - no start and end date. We use Kanban view to show the various task lists. We don't want the kanban list to disappear every time the items on it happen to be closed out. Thanks

    • Allow customers to choose meeting venue and meeting duration on booking page

      My business primarily involves one-to-one meetings with my clients. Given the hybrid-work world we now find ourselves in, these meetings can take several forms (which I think of as the meeting "venue"): In-person Zoom Phone call I currently handle these

    • 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?

    • Operation Questions.

      Hello, I hope you are well. To explain a little, we are a company that sells services and products with a technical team responsible for installation and maintenance. Zoho FSM can be useful for the technical team, namely for the technical coordinator

    • Update date & time when a cell is edited

      Hi All, I am desiring to have a cell update with the current date and time when another cell is edited. Any ideas?  Thank you

    • Add "Groups" to "Share With" on Reports & All Entries

      Hi, On Forms we can share Publicly, with Specific Users And/Or Specific Groups or All Users. With Reports and All Entries we lack the "Groups" option, please add this as with many users this saves a lot of work. Thanks Dan

    • Add Pause / Resume Option to Zoho Quartz Recordings

      Hi Zoho Team, We would like to request an enhancement to Zoho Quartz recordings: the ability to pause and resume an ongoing recording. Current Limitation: At the moment, when recording an issue with Zoho Quartz, the recording continues even when we are

    • Custom Fields Not Showing Up in Invoice PDF Template Document Information

      I have added 2 custom fields under Sales > Invoices > Manage Custom Fields. They are set to show in all PDFs. However when I am editing my Invoice PDF template, i do not see the custom fields under Document Information.

    • ZOHO Books

      Hi there, Why after I upgrade my Zoho invoice > Books then i wanted to add plugin which i cannot do. Please advise.

    • How is Your eCommerce Experience w/Zoho Inventory?

      First off, I'm SUPER grateful for the advent of Zoho Inventory and now the Zoho Commerce Suite. Overall, Inventory is a great product, especially for customers without an eCommerce presence. For eCommerce companies (especially those shipping more than ~10 packages/day), however, there are certain drawbacks that keep my clients from moving over to Zoho Inventory: Cons: 1. Invoice + Package Creation from Shopify/Other eCommerce Integrations: Zoho Inventory makes the somewhat perplexing decision to

    • Simplify Mass Replies with Predefined Templates and Snippets

      Hello everyone, We are happy to introduce a new enhancement to Mass Reply that helps agents respond to customers quickly and consistently. With the addition of Email Templates and Snippets in the reply window, agents can use predefined messages while

    • Mapping custom fields from one module to another

      I have a custom field, "Subscription Period" that appears as a required field in every Opportunity (Potential). I want that field to appear on any Quotes derived from that Opportunity (and have created a custom field of the same name in Quotes for that

    • The Social Wall: November 2025

      We’re nearing the end of the year, and the holiday season is officially kicking in! It’s that time when sales peak and your social media game needs to be stronger than ever. We’re back with exciting new updates across AI, analytics, and the mobile app

    • How to update Multi File upload field

      Assume that i have a multi file upload field,how can i update the same field again?

    • Customer Address Not on Standard Invoice when Address is on Contact Record

      Hi,  I entered the customer billing and shipping address in Zoho CRM. I created an invoice in Zoho Books with the same customer contact. The contact is correctly in Zoho Books with the billing and shipping address. The invoice for the customer does not

    • Apply partial payments to invoices from the Banking Module

      We need this! Why is this not possible?

    • Welcome to the Zoho Show Community Forums page!

      Hello everyone, The Zoho Show community is a place for you to discuss and share anything and everything related to Zoho Show Presentations, our online presentation software. It includes users from all over the globe and all walks of life. Our community helps answer your Zoho Show questions with responses from other knowledgeable community members to give you the best experience creating and designing exciting presentations. Along the way, you'll meet recognized experts from our software team and

    • Next Page