Get records using COQL query

Retrieve the necessary records from a module using COQL query. The SELECT query when passed through the query API returns records with the specified fields and from the specified module that meet the specified criteria.

Endpoints

POST /coql

Request URL

{api-domain}/bigin/v2/coql

Choose domain-specific URL to replace {api-domain}

You can use the domain-specific URL to access Bigin resources. Based on the data center in which the Bigin account's resources are available, replace {api-domain} in the preceding request with one of the following API domain URLs:

For US, https://www.zohoapis.com
For EU, https://www.zohoapis.eu
For AU, https://www.zohoapis.com.au
For IN, https://www.zohoapis.in
For CN, https://www.zohoapis.com.cn
For JP, https://www.zohoapis.jp

For more information, see Multi DC Support.

Authorization

For this endpoint, pass the access token as an authorization header. See OAuth Authentication for more information about access tokens.

Authorization: Zoho-oauthtoken <ACCESS_TOKEN>

You must authenticate using an access token that is associated with one of the following scopes:

ZohoBigin.coql.READ and ZohoBigin.modules.ALL
ZohoBigin.coql.READ and ZohoBigin.modules.{module_name}.ALL
ZohoBigin.coql.READ and ZohoBigin.modules.{module_name}.READ

In the above scope, replace {module_name} with the module for which you want to retrieve the records. The possible modules include pipelines, contacts, accounts (companies in Bigin), products, calls, events, and tasks.

Request parameters

The available request parameters are given below:

Body parameters

select_query stringRequired

Specify the valid SELECT query. You can construct a COQL query and pass it through the select_query parameter.

Field types and their allowed comparators

This section provides the information about the field types and their allowed comparators using examples:

Sample request

Copiedcurl "https://www.zohoapis.com/bigin/v2/coql" \
-X POST \
-H "Authorization: Zoho-oauthtoken 1000.8cb99dxxxxxxxxxxxxx9be93.9b8xxxxxxxxxxxxxxxf" \
-d "@queryinput.json"

Text or Picklist

The supported comparators for Text or Picklist field types are: =, !=, like, not like, in, not in, is null, is not null

For example:

To fetch contacts with the Last_Name "Smith" or "Johnson" and who are not tagged as "Important", sorted in descending order by their Full_Name, limited to 20 records and starting from the 0th record, the query would be:
SELECT Full_Name
FROM Contacts
WHERE Last_Name in ('Smith', 'Johnson') AND Tag != 'Important'
ORDER BY Full_Name DESC
LIMIT 20 OFFSET 0
To fetch products with the Product_Name starting with "Apple" or "Samsung" and the Product_Category of "Mobiles", sorted in ascending order by Product_Name, limited to 50 records and starting from the 10th record, the query would be:
SELECT Product_Name, Product_Category
FROM Products
WHERE ((Product_Name like 'Apple%') OR (Product_Name like 'Samsung%')) AND (Product_Category = 'Mobiles')
ORDER BY Product_Name ASC
LIMIT 50 OFFSET 10

Sample request body - Text or Picklist Fields

Copied//Consider the following data is saved in a file 'queryinput.json'
{
    "select_query": "SELECT Product_Name, Product_Category FROM Products WHERE ((Product_Name like '%Tour') OR (Product_Name like '%Expedition')) AND (Product_Category = 'Vacation') ORDER BY Product_Name ASC LIMIT 50 OFFSET 0"
}

Sample response

Copied{
    "data": [
        {
            "Product_Category": "Vacation",
            "Product_Name": "Cultural Heritage Tour",
            "id": "2034020000000644168"
        },
        {
            "Product_Category": "Vacation",
            "Product_Name": "European Capitals Tour",
            "id": "2034020000000489202"
        }
    ],
    "info": {
        "count": 2,
        "more_records": false
    }
}

Lookup

The supported comparators for Lookup field type are: =, !=, in, not in, is null, is not null

For example:

To fetch pipeline records with Deal_Name "Adventure Tour Package" and related to Account_Name "Blue Ridge Software", limited to 50 rows, the query would be:

SELECT Deal_Name, Sub_Pipeline, Account_Name, Account_Name.Account_Name
FROM Pipelines
WHERE (Deal_Name = 'Adventure Tour Package') AND (Account_Name.Account_Name = 'Blue Rigde Software')
LIMIT 50

Sample request body - Lookup Fields

Copied//Consider the following data is saved in a file 'queryinput.json'
{
    "select_query": "SELECT Deal_Name, Sub_Pipeline, Account_Name, Account_Name.Account_Name FROM Pipelines WHERE (Deal_Name = 'Adventure Tour Package') AND (Account_Name.Account_Name = 'Blue Rigde Software') LIMIT 50"
}

Sample response

Copied{
    "data": [
        {
            "Deal_Name": "Adventure Tour Package",
            "Account_Name.Account_Name": "Blue Rigde Software",
            "Account_Name": {
                "id": "2034020000000489105"
            },
            "id": "2034020000000489124",
            "Sub_Pipeline": "Sales Pipeline Standard",
            "Pipeline": "Sales Pipeline Standard"
        }
    ],
    "info": {
        "count": 1,
        "more_records": false
    }
}

Date, DateTime, Number, or Currency

The supported comparators for Date, DateTime, Number, or Currency field types are: =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null

For example:

To fetch pipeline records created between June 27th, 2023 and July 14th, 2023, with an unspecified amount, limited to 50 rows, the query would be:

SELECT Deal_Name, Sub_Pipeline, Stage, Created_Time, Amount
FROM Pipelines
WHERE (Created_Time between '2023-06-27T15:18:00+05:30' and '2023-07-14T12:47:22+05:30') AND (Amount is null)
LIMIT 100

Sample request body - Date, DateTime, Number, or Currency Fields

Copied//Consider the following data is saved in a file 'queryinput.json'
{
    "select_query": "SELECT Deal_Name, Sub_Pipeline, Stage, Created_Time, Amount FROM Pipelines WHERE (Created_Time between '2023-06-27T15:18:00+05:30' and '2023-07-14T12:47:22+05:30') AND (Amount is null) LIMIT 100"
}

Sample response

Copied{
    "data": [
        {
            "Deal_Name": "Adventure Tour Package",
            "Created_Time": "2023-06-27T15:18:00+05:30",
            "Amount": null,
            "Stage": "Needs Analysis",
            "id": "2034020000000644063",
            "Sub_Pipeline": "Sales Pipeline Standard",
            "Pipeline": "Sales Pipeline Standard"
        },
        {
            "Deal_Name": "Campaign for Wellness Retreat Program",
            "Created_Time": "2023-06-27T15:37:15+05:30",
            "Amount": null,
            "Stage": "Campaign Planning",
            "id": "2034020000000644087",
            "Sub_Pipeline": "Marketing",
            "Pipeline": "Marketing"
        },
        {
            "Deal_Name": "Wildlife Safari Social Media Activity",
            "Created_Time": "2023-07-14T12:47:22+05:30",
            "Amount": null,
            "Stage": "Campaign Planning",
            "id": "2034020000000683017",
            "Sub_Pipeline": "Marketing",
            "Pipeline": "Marketing"
        }
    ],
    "info": {
        "count": 3,
        "more_records": false
    }
}

Boolean

The supported comparators for Boolean field type is: =

For example:

To fetch contacts who have not opted for email, limited to 2 rows, the query would be:

SELECT Last_Name, First_Name, Full_Name, Email_Opt_Out
FROM Contacts
WHERE Email_Opt_Out = 'true'
LIMIT 2

Sample request body - Boolean Fields

Copied//Consider the following data is saved in a file 'queryinput.json'
{
    "select_query": "SELECT Last_Name, First_Name, Full_Name, Email_Opt_Out FROM Contacts WHERE Email_Opt_Out = 'true' LIMIT 2"
}

Sample response

Copied{
    "data": [
        {
            "First_Name": "Sophia",
            "Full_Name": "Sophia Brookssss",
            "Last_Name": "Brookssss",
            "id": "2034020000000489022",
            "Email_Opt_Out": true
        },
        {
            "First_Name": "Ava",
            "Full_Name": "Ava Bell",
            "Last_Name": "Bell",
            "id": "2034020000000489033",
            "Email_Opt_Out": true
        }
    ],
    "info": {
        "count": 2,
        "more_records": true
    }
}

Roles and Profiles Data

The supported comparators for Roles and Profiles related field type is: =, !=, in, not in, is null, is not null

This can be used to retrieve the profile details of the owner, including their ID, name, creator, modifier, description, creation time, modification time, as well as the details of their role, such as ID, name, reporting superior, data sharing with peers, and description, from a module.

For example:

To fetch the owner's profile ID, name, as well as the owner's role ID from Pipelines module whose Deal_Name is not null and limited to 2 rows, the query would be:

SELECT Deal_Name,Owner.profile.id,Owner.profile.name,Owner.role.id
FROM Pipelines
WHERE Deal_Name is not null
LIMIT 2

Sample request body - Roles and Profiles Data

Copied//Consider the following data is saved in a file 'queryinput.json'
{
    "select_query": "select Deal_Name,Owner.profile.id,Owner.profile.name,Owner.role.id from Pipelines where Deal_Name is not null limit 2"
}

Sample response

Copied{
    "data": [
        {
            "Owner.profile.id": "2034020000000015972",
            "Deal_Name": "City Tour Package",
            "Owner.role.id": "2034020000000015966",
            "id": "2034020000000478136",
            "Owner.profile.name": null
        },
        {
            "Owner.profile.id": "2034020000000015972",
            "Deal_Name": "Cruise Package",
            "Owner.role.id": "2034020000000015966",
            "id": "2034020000000478164",
            "Owner.profile.name": null
        }
    ],
    "info": {
        "count": 2,
        "more_records": true
    }
}

Response object

The response object contains a list of records, fetched and filtered based on the SELECT query.

Possible error codes

The response of this resource includes HTTP status and error codes.