COQL API in Zoho CRM - Part I

COQL API in Zoho CRM - 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 TypeComparators
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:





        • Recent Topics

        • geographic search filter in map view

          Hi, I have a recruiting and timesheet system built in Creator. The client wants to enhance the search for candidates based on their location and filter by job skills - currently they look on the Map View which uses the geo location or post code of the

        • Invoice status on write-off is "Paid" - how do I change this to "Written off"

          HI guys, I want to write off a couple of outstanding invoices, but when I do this, the status of the invoices shows as "Paid". Clearly this is not the case and I need to be able to see that they are written off in the customer's history. Is there a way

        • Announcement: Upcoming changes to the permission grant flow for OAuth apps

          This announcement is intended for app developers who use the Zoho API console. We're going to implement an important update to the way users grant permission for the OAuth apps created through the API console. What’s changing? Currently, users can grant

        • "Copy Field Values from one Module to another" how to use?

          Hi everyone! I'm sorry if this question was already asked, I didn't find it! So let me explain:  In my Tickets module, we have the custome field "customer type" where we indicate if it's a lead, user, etc... In the Contact module I used the "type" field with exactely the same entries. I would like when in the tickets module an operator choose an option that it automatically update it in the contact module. I found the "Copy Field Values from one Module to another" custom function which seems perfect

        • Add Google Workspace Module to Zoho Flow

          Dear Zoho Flow Team, I hope this message finds you well. We’d like to request the addition of a dedicated Google Workspace module in Zoho Flow. Currently, there are no triggers or actions for Google Workspace, which limits our ability to integrate and

        • Create 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 were

        • How do i remove the Powered by Zoho logo from my careers page

          Can I remove this? ​

        • Totals on Pivot Table

          Is there a way to change the way the subtotal calculates? In this example I have a formula to give me the average monthly attendance ....but I want the grand total of the month to be the sum of all the average attendances...any ideas? Thank you!@

        • Team Inbox is not working AGAIN

          I like Team Inbox in general. It makes using a collaborative inbox easy - when it works. The problem is that it doesn't work at times - and it seems to not work, a lot. It's not catastrophic failure, it's little things. Unable to send messages Unable

        • HOW TO USE ZOHO

          IDK

        • Zoho account sign in with passkey

          Hello, I am trying to sign in using passkey, but the option doesn't show up in the web and is disabled in Oneauth on mobile, saying the admin has restricted the use. On the Admin page in Security MFA I can find no option for passkey. Help would be greatly

        • Pivot table with Text values - "Matrix Report"

          User Story - As a user, I would like the ability to display textual data in a two-way table, matrix format (text datatypes, not numerical datatypes displayed as a dimension) One major feature missing from the Pivot tables in Zoho Analytics is the ability

        • Canvas View in Zoho Recruit

          Is it possible or would it be possible to have the new 'Canvas View' in Zoho Recruit?

        • Allow standard layout to be used/edited as a starting point for canvas

          Hello, I would really love to improve the look of our CRM interface using Canvas but we have spent so much time customising the screens already (in standard view) that the thought of starting from scratch and building everything again, field by field,

        • Can Wisestamp email signature be use with Zoho mail?

          Does a Wisestamp email signature work with Zoho mail?

        • WiseStamp

          WiseStamp is an excellent social media signature tool. It integrated seamlessly into Gmail and Thunderbird, plus a few more. Are there any plans to get this incredible app integrated into Zoho. check it out here: http://www.wisestamp.com/ thanks Tim

        • Merge tickets

          Hello, I am attempting to merge two tickets; however, the option to merge does not appear to be available. Could you kindly confirm if this feature has been relocated or removed? Additionally, could you please provide guidance on the correct process to

        • Add "Merge by Ticket ID" Option on Ticket Screen

          Dear Zoho Team, We would like to request a new feature for the ticket interface in Zoho Desk. Specifically, we propose adding a button to the ticket screen that allows users to merge the current ticket with another ticket by directly providing the second

        • Power BI connector (Zoho Creator) to Zoho Projects

          How can i connect power bi to Zoho Projects? domain is zoho.com How can i find workspace name, application link name and Report link name?

        • A recap of Zoho Sprints 2024

        • Update your Google connection with Zoho TeamInbox

          Dear all, Wishing you a Happy New Year! Google has recently updated its security policy to enhance user protection, requiring all third-party apps and services to use OAuth authentication and password-less login methods. This update impacts users who

        • Possible to backdate Stage shifts in Deals module?

          I want to enter my organisation's historical dealflow data into the CRM and it's important that the Stage history is showing the correct dates in the past of when it happened. Is there a way for me to modify the "Modified Time" in the Stage History Related

        • Easy way to delete attachments

          I've reached my data limit and would like to run a view/report, and mass delete attachments. Is there an easy, fast way to do this? Moderation Update: Post Summary: There are two features the post discusses a) Easy way to remove Email attachments Will

        • Sites Speed and Performance Grades

          I noticed that there are no recent inquiries or complaints about load speed or performance issues with Zoho Sites websites. However, I wanted to understand what Zoho has done to ensure that speed remains optimized, images are compressed and lazy loaded,

        • Integrating Quotes with leads Information

          Our business requires giving a quote to a lead (not Account / Potential - as we define it differently). Currently, Zoho CRM's Quotes are integrated with Accounts / Potentials and not with Leads. Is there a way I could get the Lead information to the Quotes

        • Flow to follow up on trade fair contacts

          Hi, Before we moved to Zoho we had some flows (sequences) in HubSpot to follow up on trade fair contacts. To explain further on this it had the following characteristics: New contacts could be added to the sequence When added a flow of communication started.

        • Unknown error occurred

          Hi, When we want to publish or edit a page in our website, we encounter with "Unknown error occurred" problem. I share a screenshot here. Our website is www.essoft.com. It happens every page. We want to solve this ASAP.

        • Zoho one

          I am starting Zoho one as a beginner and there is Zoho crm and Zoho sales which i needed to work on but it appears in unassigned apps. What Should i do now?

        • Dialing Microsoft Teams Phone Service via Zoho CRM

          I am using the VOIP option in Microsoft teams for my office phone system. I was hoping to have a way to dial numbers directly from Zoho CRM, but don't see anything in the Teams Integration or in the Telephony integration that will enable this. Does anyone

        • Emailing lookup field but placing this as an ID or number rather than text

          Hi there, First time poster and have been a user of Zoho Creator for approx 6weeks so forgive my ignorance as I learn to code. We have a need to send an email to a specific email address with some of the fields triggered by the submission of a form. In

        • Search mails in shared mailbox

          Hi everyone, is there a way to search mails in shared mailbox's? Search in streams or mail doesn't return anything from mails in shared mailboxes. Thanks! Rafal

        • User Emails Blocked

          Community: I keep running into issues where our users stop receiving notifications from CRM because their email addresses get blocked in on the backend some how. I reach out to support, they confirm, they fix, and we carry on, but then it happens again.

        • Why wont Zoho Support Grammarly!! --- PLEASE VOTE FOR THIS to show Zoho we need this

          The spell check and grammar in ZOHO are so buggy and a waste of time. Please support Grammarly! I'm sure I'm not the only one — there are other CRMs that support this. If you're not planning to add this feature, Please let others know before accepting

        • Is it possible to hide Developer Space for all user in Zoho Projects

          Hello! I am Zoho admin in a company and we want to use Zoho Project to manage projects, but after a few days of testing we are not able to "hide" the Developer Space from all kind of users except the admin. To sum up, I want to hide this for all users.

        • API (v2) Search Criteria using CONCAT

          With API I can search for a contact using First_Name and Last_Name. However, when I need to search the Contact Module using a full name — and because CRM does not provide an API for full name — I am not finding a way to do this in the traditional way

        • Weekly Tips: Stay Focused with Email Snooze!

          New Year, New Resolutions Being back at work also means being back to the constant barrage of messages from work and clients. The constant flood of incoming emails can lead to the missing of important messages, especially when you can't respond right

        • Schedule Zoho CRM reports only on Business Days

          Hello, Is it possible to schedule reports only on business days ? We currently get daily sales reports on weekend which has no value since the sales team doesn't work on weekends. Thanks

        • Zoho Payroll's Year in Review 2024

          As we roll into 2025, we'd like to pay tribute to all the milestones we hit in 2024! From releasing out new features that streamlined your workflows to updates that made payroll management smoother, we’ve had a prolific year—all while keeping you, our

        • Using tickets to train Zia

          Hi Team, I would like to know if there is any way that Zia can also learn from previous tickets in addition to the articles from the knowledge base. Since we have most of our knowledge curerently in the tickets and that this is hard to combine into a

        • 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

        • Next Page