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

    • Features, Feedback and Votes

      We’re launching the Feedback Forum for our customers. This is 'THE' place where you can add everything you’d like to see in Zoho Books. If you don't find the specific feature you need, simply add it so others can also vote for it. While we cannot promise

    • There Might Be A Glitch in Determining If A String Represents A URL

      I suspect there might be a glitch in determining if a string represents a URL or not. For example, I cannot embed the following URL. Perhaps the exclamation mark or parentheses are the culprit? https://en.wikipedia.org/wiki/How_the_Grinch_Stole_Chri

    • Customer Management: #4 Enhance Customer Journey

      When Neha started DefineOps, a growing IT support and consulting firm, most of her work was straightforward. A client would sign up for a free version, decide whether the service works for them, and then either continue or discontinue. Billing was simple,

    • Android app sync problem - multiple devices have same problem

      Hello, I am having a problem with synchronization in the Android app. When I create a drawing, the data does not sync correctly—only a blank note is created without the drawing. I tested this on multiple devices, including phones and tablets, and the

    • Marketing Tip #1: Optimize item titles for SEO

      Your item title is the first thing both Google and shoppers notice. Instead of a generic “Leather Bag,” go for something detailed like “Handcrafted Leather Laptop Bag – Durable & Stylish.” This helps your items rank better in search results and instantly

    • Territory Assignment Issues (Lead to Account + Contact)

      1. Lead → Account & Contact Territory Assignment on Conversion A Lead is automatically assigned one or more territories using a workflow and Lead Assignment Rules. This works as expected, and we are able to assign multiple territories to a Lead automatically.

    • Marketer's Space: Proven tips to improve open rates – Part II

      Hello Marketers! Welcome back to another post in Marketer's Space! We're continuing from where we left off a fortnight ago. We ended the previous post discussing the subject line, and we'll continue from there. Let's dive right in. Pre-header Pre-header

    • SQL Table slowed to a crawl

      Hi all - I seem to have noticed an update. Now whenever I am typing in the script field for the SQL tables there is a huge delay and it is all very slow. This has never been the case for me in over 5 years of using Analytics - I really hope it's fixed

    • Deluge Events/search API works in user environment but not in sandbox – why?

      I am creating an Extension for Zoho CRM using Zoho Sigma Platform I’m using the following Deluge code to search Events in Zoho CRM based on Start_DateTime: criteria = "(Start_DateTime:greater_than:2025-12-20T00:00:00+00:00)"; url = "crm/v8/Events/search?criteria="

    • What are Zoho Meeting capabilities included in Zoho Workplace Standard?

      I am evaluating using Zoho Meeting for my organization, but it is not clear what Zoho Meeting capabilities are already included in Zoho Workplace Standard. - Are meeting recordings included in Workplace Standard? - Can we invite external meeting participants

    • How can I get the participant list of a reoccurring meeting afterwards?

      I'm trying to use the Meeting Participant Report from the API docs but when I call it on a reoccurring meeting it returns that there are no participants because it thinks I'm talking about the meeting in the future. Is there a way to use webhooks or some

    • Function #50: Send Mass emails to your customers

      Hello everyone, and welcome back to our series! We have reached a milestone of 50 Functions, which means that we have automated 50 different tasks in Zoho Books. Every Friday, we have shared a nifty function aimed at either automating a task or streamlining

    • Full Hebrew Language Support for Client-Side Zoho Assist Interface

      Dear Zoho Assist Team, We would like to request an enhancement to Zoho Assist's client-side interface to support full Hebrew language customization, including all popups, notifications, and session-related messages. Current Limitation The Join page allows

    • Add Hebrew & RTL Support to Feedback Widget

      Hello Zoho Desk Team, How are you? We are using Zoho Desk and would like to utilize the Feedback Widget. While Zoho Desk itself supports Hebrew and RTL, the Feedback Widget unfortunately does not. We kindly request that Hebrew and full RTL support be

    • Merge Tickets Directly from Contact Page in Zoho Desk

      Dear Zoho Desk Support Team, We are writing to request a new feature that would allow users to easily merge tickets directly from the contact page in Zoho Desk. Currently, the only option to merge tickets is from the Tickets list view page, which can

    • Different languages for users

      Hello, Do you plan to enable individual users to select their languages for interface? Currently language can be changed for everyone - it looks like a settings for a whole portal, which is not good when you are working internationally. Best regards,

    • 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

    • 2025年 Zoho コミュニティ 活動の振り返り 🎉

      ユーザーの皆さん、こんにちは！コミュニティチームの中野です。 2025年も多くの学びと出会いがあったZoho コミュニティ。 本記事では今年の活動を振り返りながら、フォーラムの投稿・参加者の皆さん・イベントのハイライトをご紹介していきます。 目次 フォーラム：注目の投稿 フォーラム：多くの貢献をしてくださった方々 ユーザー交流会振り返り ワークアウト振り返り その他のトピックス 1. フォーラム：注目の投稿 本フォーラムでは様々な議論と知識の共有が行われました。 ユーザーの皆さんが日々の業務で直面する課題を投稿し、経験豊富なユーザーさん達が実践的な解決策を提供してくださいました。

    • Customer Management: #3 Giving Customers Control & Privilege

      Rio, the founder of RenoTech Solutions, a fast-growing digital service company, found itself juggling a dozen different services for its clients. They handled one-time setup fees, recurring monthly invoices, and custom milestone-based billing for projects.

    • Can I use a Standalone CRM Function as the Callback URL For Async Export Data API?

      I am creating an export job using this API https://www.zoho.com/analytics/api/v2/bulk-api/export-data-async/create-export/view-id.html There is a "callbackUrl" key in the CONFIG object. I tried copying the URL for a standalone function in CRM which can

    • Add RTL (Right-to-Left) Text Direction Support Across All Zoho Learn Editing Interfaces

      Hi Zoho Learn Team, Hope you're doing well. We would like to request an important enhancement to Zoho Learn regarding support for right-to-left (RTL) languages such as Hebrew and Arabic. 🔹 Current Issue While the Knowledge Base Article editor provides

    • Add Hebrew Support for Meeting Transcripts Provided by ZIA in Zoho Cliq

      Hi Zoho Cliq Team, Hope you're doing well. We would like to request the addition of Hebrew language support for the Meeting Transcript and Summary feature in Zoho Cliq. Currently the transcript and summary feature is available for recorded meetings and

    • Remote Control Functionality During Screen Sharing in Zoho Cliq

      Hello Zoho Cliq Team, We would like to request the addition of remote control functionality during screen sharing sessions in Zoho Cliq. Currently, while screen sharing in Cliq is very useful, it lacks the ability for another participant to take control

    • Centralized Organization Information Management in Zoho One

      Dear Zoho One Support, I'm writing to propose a feature that would significantly improve the user experience and streamline data management within Zoho One. Current Challenge: Currently, managing organization information across various Zoho One apps requires

    • Enhance Zoho One Conditional Assignment to Fully Reassign App Settings When Changing Departments

      Hi Zoho Team, We’d like to submit a feature request regarding the current behavior of Zoho One’s conditional assignment logic when moving a user between departments. 🔧 Current Limitation As it stands, Zoho One’s conditional assignment does not remove

    • Ability to Filter Alias Mailboxes in Zoho Recruit

      Dear Zoho Recruit Team, I hope you are doing well. We would like to request a feature enhancement regarding the handling of alias mailboxes in Zoho Recruit. Currently, when we connect an alias mailbox (e.g., jobs@domain.com) from our Zoho One account

    • Automatic Department and Employee Sync Between Zoho One and Zoho People

      Dear Zoho Support, I'm writing to propose a valuable feature request that would streamline data management and improve user experience within the Zoho ecosystem: automatic synchronization between departments and employees in Zoho One and Zoho People.

    • Prefered Bin Missing in android APP

      Andoroid app dosent show preferred bin in the picklist. The workaround support reccomend is to use the computre to create the picklist. it shuld be information to be shown aas basic for the pciker.

    • Open Sans Font in Zoho Books is not Open Sans.

      Font choice in customising PDF Templates is very limited, we cannot upload custom fonts, and to make things worse, the font names are not accurate. I selected Open Sans, and thought the system was bugging, but no, Open Sans is not Open Sans. The real

    • Function #1: Convert an accepted Estimate to Sales Order automatically in Zoho Books

      As you’re aware, Zoho Books provides a default option to have the estimates automatically converted to invoices once your customer accepts them. Many of you wanted a similar option for sales orders, so here’s a workflow that converts accepted estimates

    • Reusable Jira Connection for Multiple Zoho Projects Imports

      Hello Zoho Projects Team, We would like to raise a concern and submit a feature request regarding the Jira → Zoho Projects migration process, specifically around how Jira connections are handled. Current Behavior: When setting up a Jira connection for

    • Zoho invoice doesn't support Arabic language

      I added a clause in the terms & conditions section in Arabic but it doesn't appear when I sent or print it.

    • Recurring Invoice Placeholder Not Updating Billing Period

      Hi, I’m using Zoho Invoice Free and want the billing period to update automatically in recurring invoices. In Item Description I tried: Billing Period: %(m-6)% %(y)% to %(m-1)% %(y)% but even if the invoice date is in 2026, it still shows the period based

    • Related list Mobile Device

      Hello, We use an the Zoho creator application to make reports linked to Accounts. On the computer: it's easy to go the Account and see all the created reports in the related list below On iPad/Phone ZOHO CRM APP: we cannot see the reports on those accounts

    • Can't update the company address in zoho invoice

      Dear Sir/Madam, I want to update the company address in Zoho Invoice but failed. It popped out a sentence "Invalid value passed for Website". Please advice how to solve this problem. Thank you.

    • Is there a plan to allow for the hierarchical organization of Customers / Companies in Zoho Billing?

      We have a few customers who have organizational structures that we haven't quite found a way to deal with in Zoho Billing. In CRM, these sub-companies (or subsidiaries or whatever you want to call them) all have another CRM account as the parent account.

    • 【Zoho CRM】作業リスト機能リリースのお知らせ

      ユーザーの皆さま、こんにちは。コミュニティチームの藤澤です。 今回は「Zoho CRM アップデート情報」の中から、作業リスト機能リリースのお知らせ情報をご紹介します。 目次 作業リスト機能　概要 機能①　自分の未完了の活動 機能②　処理待ち 機能③　自分の作業リスト 作業リスト機能　概要 営業チームでは、CRM内に業務やデータが分散しているため、管理が煩雑になりがちです。この断片化は、機会の見逃しや生産性の低下につながります。 作業リスト機能は、タブを横断する業務項目をひとつのカスタマイズ可能なダッシュボードに集約し、業務を効率的に管理できるようにします。

    • SalesIQとPageSenseの利用について

      初めての投稿で場違いだったらすいません。 弊社ではSalesIQを運用しているのですが、追加でPageSenseの導入もしたいと現場からの声があります。 両サービスともクッキー同意バナーが必要なサービスなのですが 弊社では同意無しに情報はとりませんという方針なので 2つ入れると2つバナーを出す必要がでてきます・・・ 両サービスを運用されてる方があれば運用状況とか教えてほしいです。 PageSenseについては詳細まで機能を理解してないなかでの質問です。

    • Parent-Child Tickets using API or Deluge

      Hi Everyone, We are looking at the parent-child ticketing features in Zoho Desk. We want to be able to create a parent ticket at customer level and nest child tickets underneath. The issue we are facing is to be able to automate this. I'm checking the

    • Closing connected ticket after closing WA conversation

      Hi, At the moment, once someone sends a message to our WA number, a corresponding ticket is automatically created. After the question is answered, our support department closes the chat/conversation. However, after that, the connected ticket is still

    • Next Page