Get Records through a COQL Query

Purpose

To get records from the module through a COQL query.

Request URL

https://www.zohoapis.com/crm/v2/coql

Request Method

POST

Although you "get" records from the module, the HTTP method is POST as you "post" the query. Refer CRM Object Query Language(COQL) - An Overview to learn how to construct a COQL query.

Scope

scope=ZohoCRM.coql.READ
(and)
scope=ZohoCRM.modules.all
(or)
scope=ZohoCRM.modules.{module_name}.{operation_type}

Possible module names	Possible operation types
leads, accounts, contacts, deals, campaigns, tasks, cases, events, calls, solutions, products, vendors, pricebooks, quotes, salesorders, purchaseorders, invoices, and custom	ALL - Full data access READ - Get module data

Request JSON

Attribute	Data Type	Description
select_query	JSON key	Represents that the input is a select query. This is a mandatory key.

Fields and Allowed Comparators in the Select Query

Field Type	Allowed COQL Comparators
Text	=, !=, like(used for starts_with, ends_with, contains), not like(used for not_contains), in, not in, is null, is not null
Lookup	=, !=, in, not in, is null, is not null Note: When you query a lookup field, the response only contains the ID of the field. To get the name of the field, you must include the field_API_name in the query. Sample: "select Account_Name, Account_Name.Account_Name from Contacts where Last_Name='Boyle'" Here, Account_Name returns the ID of the account and Account_Name.Account_Name returns the account name of the account that the contact is associated with.
PickList	=, !=, like(used for starts_with, ends_with, contains), not like(used for not_contains), 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
Number	=, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null
Currency	=, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null
Multi-Select PickList	=, !=, like(used for starts_with, ends_with, contains), not like(used for not_contains), in, not in, is null, is not null
Boolean	=

Note:

Sample input and response for each field type is included in the right pane of this page.

Possible Errors

HTTP Status	Error Code	Message	Reason
400	SYNTAX_ ERROR	error occurred while parsing the query	The query does not contain proper criteria The query does not contain the base table The request does not have the where clause
400	SYNTAX_ ERROR	missing clause	The query does not have the from clause
400	SYNTAX_ ERROR	given coql query not supported	The request contains a query other than select. Example: "select_query" : "update Leads set Last_Name = 'Last' where id = 12356"
400	LIMIT_ EXCEEDED	select columns limit exceeded	The select column(field_API_names) limit has exceeded the maximum allowed limit of 50
400	LIMIT_ EXCEEDED	limit exceeded	The value of limit clause has exceeded the maximum limit of 200. Example: limit 500
400	INVALID_ QUERY	column given seems to be invalid	The query contains an invalid column name(field_API_name). Example: "select Last_Name, Testing from Leads where Last_Name is not null"
400	INVALID_ QUERY	data type not supported	The query contains unsupported data type. Example: "select Last_Name, Contacts from Leads where Last_Name is not null" Here, Contacts is a multi-select lookup field and not supported in COQL.
400	INVALID_ QUERY	value given seems to be invalid for the column	The data type of the value of the select column is invalid. Example: "select_query" : "select Last_Name from Leads where Last_Name is not null and No_of_Employees = 'adfkahfd'" Here, the expected data type for No_of_Employees is a number, whereas the value given is a string.
400	INVALID_ QUERY	invalid operator found	The query contains an invalid operator Example: "select_query" : "select Last_Name from Leads where Last_Name is not null and Last_Name >= 'adfkahfd'"
401	OAUTH_ SCOPE_ MISMATCH	invalid oauth scope to access this URL	User does not have the required scope to access the module
401	DUPLICATE _DATA	duplicate data	The query contains duplicate select columns(field_API_names). Example: "select_query" : "select Last_Name, First_Name, Full_Name, Created_Time, Full_Name from Contacts where Lead_Source = Advertisement limit 2" Here, the query contains Full_Name twice.

Note:

You can only use Select query in COQL to fetch records from a module.
You can only use two relations(joins) in a select query. If you include more than two relations, the system validates only the last two relations.
By default, system sorts the records in ascending order based on the record ID, if you do not include order by in the query.
The default value for OFFSET is 0 i.e., the system does not skip fetching any record.
Refer COQL Limitations for more details.

Sample Request

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

In the request, @input.json contains the sample input data.
The request remains the same for all types of inputs.

Sample Input

Copied{
 "select_query" : "select Last_Name, First_Name, Full_Name
                  from Contacts 
                  where Last_Name = 'Boyle' and First_Name is not null limit 2"
}

Sample Response

Copied{
    "data": [
        {
            "First_Name": "Patricia",
            "Full_Name": "Patricia Boyle",
            "Last_Name": "Boyle",
            "id": "554023000000310003"
        },
        {
            "First_Name": "Steve",
            "Full_Name": "Steve Boyle",
            "Last_Name": "Boyle",
            "id": "554023000000310012"
        }
    ],
    "info": {
        "count": 2,
        "more_records": false
    }
}

Sample Input: With Single relation(join)

Copied{
 "select_query": "select Last_Name, First_Name, Full_Name, Account_Name
                  from Contacts
                  where (((Last_Name = 'Boyle') and (First_Name is not null)) and (Account_Name.Account_Name = 'Zylker'))
                  limit 2"
}

Sample Response

Copied{
    "data": [
        {
            "First_Name": "Patricia",
            "Full_Name": "Patricia Boyle",
            "Vendor_Name": {
                "id": "554023000000310037"
            },
            "Last_Name": "Boyle",
            "Account_Name.Account_Name": "Zylker",
            "Account_Name": {
                "id": "554023000000238116"
            },
            "id": "554023000000310003"
        }
    ],
    "info": {
        "count": 1,
        "more_records": true
    }
}

Sample Input: With two relations(joins)

Copied{
 "select_query" : "select Last_Name, Account_Name.Parent_Account, Account_Name.Parent_Account.Account_Name
                  from Contacts
                  where Last_Name is not null and Account_Name.Parent_Account.Account_Name is not null"
}

In this query, two joins are established using the lookup field Account_Name in the Contacts module and another lookup field Parent_Account in the Accounts module. Here, the relation Account_Name.Parent_Account returns the ID of the parent account of the account associated with the contact. The relation Account_Name.Parent_Account.Account_Name returns the name of the parent account of the account associated with the contact.

Sample Response

Copied{
    "data": [
        {
            "Account_Name.Parent_Account.Account_Name": "Zylker",
            "Last_Name": "Boyle",
            "Account_Name.Parent_Account": {
                "id": "554023000000238121"
            },
            "id": "554023000000310003"
        },
        {
            "Account_Name.Parent_Account.Account_Name": "Zylker",
            "Last_Name": "Patricia",
            "Account_Name.Parent_Account": {
                "id": "554023000000238121"
            },
            "id": "554023000000310012"
        }
    ],
    "info": {
        "count": 2,
        "more_records": false
    }
}

Sample Input: With Date and DateTime Fields

Copied{
 "select_query": "select Last_Name, First_Name, Full_Name, Created_Time, Date_1
                  from Contacts
                  where Created_Time = '2019-03-11T10:33:32+05:30' and Date_1 = '2019-03-11'
                  limit 2"
}

This query fetches records from the Contacts module that were created on the specified date and at the specified time. Here, Date_1 is a custom field whose data type is Date.

Sample Response

Copied{
    "data": [
        {
            "First_Name": "Patricia",
            "Full_Name": "Patricia Boyle",
            "Last_Name": "Boyle",
            "Created_Time": "2019-03-11T10:33:32+05:30",
            "id": "554023000000310012",
            "Date_1": "2019-03-11"
        }
    ],
    "info": {
        "count": 1,
        "more_records": false
    }
}

Sample Input: With Date and DateTime Range

Copied{
 "select_query": "select Last_Name, First_Name, Full_Name, Created_Time, Date_1
                  from Contacts
                  where Created_Time between '2019-03-03T00:00:01+05:30' and '2019-03-11T23:59:59+05:30' and
                  Date_1 between '2019-03-04' and '2019-03-11' limit 2"
}

This query fetches records from the Contacts module that were created between the specified dates and in the specified time
range. The operator BETWEEN is used in the WHERE clause while specifying a range.
Here, Date_1 is a custom field whose data type is Date.

Sample Response

Copied{
    "data": [
        {
            "First_Name": null,
            "Full_Name": "Last_Name1",
            "Last_Name": "Last_Name1",
            "Created_Time": "2019-03-04T11:52:26+05:30",
            "id": "554023000000298013",
            "Date_created": "2019-03-04"
        },
        {
            "First_Name": "Patricia",
            "Full_Name": "Patricia Boyle",
            "Last_Name": "Boyle",
            "Created_Time": "2019-03-11T10:30:13+05:30",
            "id": "554023000000310003",
            "Date_created": "2019-03-11"
        }
    ],
    "info": {
        "count": 2,
        "more_records": true
    }
}

Sample Input: With Pick List and Multi-Select Pick List

Copied{
 "select_query": "select Last_Name, First_Name, Full_Name, Created_Time, Date_1, Lead_Source, Languages_Known
                  from Contacts
                  where (((First_Name is  not null) and (Lead_Source = Advertisement)) and Languages_Known = 'English;German')
                  limit 2"
}

In this query, Lead_Source is a Pick List and Languages_known is a custom multi-select pick list field. The query returns the name of the contact that contains the mentioned value in the Lead_Source pick list and the specified languages in the Languages_Known multi-select pick list.

Sample Response

Copied{
    "data": [
        {
            "First_Name": "Patricia",
            "Full_Name": "Patricia Boyle",
            "Last_Name": "Boyle",
            "Created_Time": "2019-03-11T10:30:13+05:30",
            "Languages_Known": [
                "English",
                "German"
            ],
            "Lead_Source": "Advertisement",
            "id": "554023000000310003",
            "Date_created": "2019-03-11"
        },
        {
            "First_Name": "Steve",
            "Full_Name": "Steve Boyle",
            "Last_Name": "Boyle",
            "Created_Time": "2019-03-11T10:33:32+05:30",
            "Languages_Known": [
                "English",
                "German"
            ],
            "Lead_Source": "Advertisement",
            "id": "554023000000310012",
            "Date_created": "2019-03-11"
        }
    ],
    "info": {
        "count": 2,
        "more_records": false
    }
}

Sample Input: With User/Owner Lookup Field

Copied{
 "select_query" : "select Last_Name, First_Name, Full_Name, Owner
                  from Contacts
                  where Last_Name = 'Boyle' and Owner = '554023000000235011'
                  limit 3"
}

In this query, Owner is the lookup field in the Contacts module. This query fetches records from the contacts module with the specified last name and whose owner id is 554023000000235011.

Sample Response

Copied{
    "data": [
        {
            "First_Name": "Patricia",
            "Full_Name": "Patricia Boyle",
            "Owner": {
                "id": "554023000000235011"
            },
            "Last_Name": "Boyle",
            "id": "554023000000310003"
        }
    ],
    "info": {
        "count": 1,
        "more_records": false
    }
}

Sample Input: With Number Field

Copied{
 "select_query" : "select Last_Name, First_Name, Full_Name, No_of_Employees
                  from Contacts
                  where No_of_Employees between 0 and 100
                  limit 2"
}

In this query, No_of_Employees is the Number field.

Sample Response

Copied{
    "data": [
        {
            "First_Name": null,
            "Full_Name": "Last_Name1",
            "Last_Name": "Last_Name1",
            "id": "554023000000298013",
            "No_of_Employees": 100
        },
        {
            "First_Name": "Patricia",
            "Full_Name": "Patricia Boyle",
            "Last_Name": "Boyle",
            "id": "554023000000310003",
            "No_of_Employees": 60
        }
    ],
    "info": {
        "count": 2,
        "more_records": false
    }
}

Sample Input: With Currency Field

Copied{
 "select_query" : "select Last_Name, First_Name, Full_Name, Currency_1
                  from Contacts
                  where Currency_1 between 100 and 1000000
                  limit 2"
}

Sample Response

Copied{
    "data": [
        {
            "First_Name": null,
            "Full_Name": "Last_Name1",
            "Currency_1": "100",
            "Last_Name": "Last_Name1",
            "id": "554023000000298003"
        },
        {
            "First_Name": "Patricia",
            "Full_Name": "Patricia Boyle",
            "Currency_1": "1000000",
            "Last_Name": "Boyle",
            "id": "554023000000310003"
        }
    ],
    "info": {
        "count": 2,
        "more_records": false
    }
}

Sample Input: With Boolean Field

Copied{
 "select_query" : "select Last_Name, First_Name, Full_Name, Checkbox_1
                  from Contacts
                  where Checkbox_1 = 'true'
                  limit 2"
}

Sample Response

Copied{
    "data": [
        {
            "First_Name": "Patricia",
            "Full_Name": "Patricia Boyle",
            "Checkbox_1": true,
            "Last_Name": "Boyle",
            "id": "554023000000310003"
        }
    ],
    "info": {
        "count": 1,
        "more_records": false
    }
}

Sample Input: With Lookup Field

Copied{
 "select_query" : "select Last_Name, First_Name, Full_Name, Associated_Contacts
                  from Leads
                  where Associated_Contacts in ('554023000000316022', '554023000000310003')
                  limit 2"
}

In this query, Associated_Contacts is the lookup field in the Leads module.

Sample Reponse

Copied{
    "data": [
        {
            "First_Name": "Patricia1",
            "Full_Name": "Patricia1 Boyle1",
            "Last_Name": "Boyle1",
            "id": "554023000000322019",
            "Associated_Contacts": {
                "id": "554023000000316022"
            }
        },
        {
            "First_Name": null,
            "Full_Name": "Lead_1",
            "Last_Name": "Lead_1",
            "id": "554023000000322097",
            "Associated_Contacts": {
                "id": "554023000000310003"
            }
        }
    ],
    "info": {
        "count": 2,
        "more_records": false
    }
}