Get Records through a COQL Query

Purpose

To get the records from the module through a COQL query.

Request Details

Request URL

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

Header

Authorization: Zoho-oauthtoken d92d4xxxxxxxxxxxxx15f52

Scope

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

Possible module names

leads, accounts, contacts, deals, campaigns, tasks, cases, events, calls, solutions, products, vendors, pricebooks, quotes, salesorders, purchaseorders, invoices, and custom

Possible operation types

ALL - Full data access
READ - Get module data

Note

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

Request JSON

  • select_queryJSON key, mandatory

    Represents that the input is a select query.

Sample Request

Copiedcurl "https://www.zohoapis.com/crm/v2/coql"
-H "Authorization: Zoho-oauthtoken 1000.8cb99dxxxxxxxxxxxxx9be93.9b8xxxxxxxxxxxxxxxf"
-d "@input.json"
-X POST
3.0.08.0
Copied//Get instance of QueryOperations Class
QueryOperations queryOperations = new QueryOperations();

//Get instance of BodyWrapper Class that will contain the request body
BodyWrapper bodyWrapper = new BodyWrapper();

String selectQuery = "select Last_Name from Leads where Last_Name is not null limit 500";

bodyWrapper.setSelectQuery(selectQuery);

//Call getRecords method that takes BodyWrapper instance as parameter 
APIResponse < ResponseHandler > response = queryOperations.getRecords(bodyWrapper);
Copiedpackage com.zoho.crm.api.sample.restapi.query;
import javax.net.ssl.SSLContext;
import org.apache.http.HttpEntity;
import org.apache.http.HttpHeaders;
import org.apache.http.HttpResponse;
import org.apache.http.client.methods.HttpEntityEnclosingRequestBase;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.client.methods.HttpUriRequest;
import org.apache.http.client.utils.URIBuilder;
import org.apache.http.conn.ssl.NoopHostnameVerifier;
import org.apache.http.conn.ssl.SSLConnectionSocketFactory;
import org.apache.http.entity.StringEntity;
import org.apache.http.impl.client.CloseableHttpClient;
import org.apache.http.impl.client.HttpClientBuilder;
import org.apache.http.protocol.HTTP;
import org.apache.http.util.EntityUtils;
import org.json.JSONObject;
public class GetRecordsthroughaCOQLQuery 
{
	@SuppressWarnings("deprecation")
	public static void main(String[] args) 
	{
		try
		{
			HttpClientBuilder httpClientBuilder = HttpClientBuilder.create();
			SSLContext sslContext = SSLContext.getDefault();
			SSLConnectionSocketFactory sslConnectionSocketFactory = new SSLConnectionSocketFactory(sslContext, NoopHostnameVerifier.INSTANCE);
			CloseableHttpClient httpclient = httpClientBuilder.setSSLSocketFactory(sslConnectionSocketFactory).build();
			URIBuilder uriBuilder = new URIBuilder("https://www.zohoapis.com/crm/v2/coql");
			HttpUriRequest requestObj = new HttpPost(uriBuilder.build());
			HttpEntityEnclosingRequestBase requestBase = (HttpEntityEnclosingRequestBase) requestObj;
			JSONObject requestBody = new JSONObject();
			requestBody.put("select_query", "select Last_Name, First_Name, Full_Name, Lead_Source from Leads where Last_Name = 'Last Name' limit 2");
			requestBase.setEntity(new StringEntity(requestBody.toString(), HTTP.UTF_8));
			requestObj.addHeader("Authorization", "Zoho-oauthtoken 1000.xxxxxxx.xxxxxxx");
			requestObj.addHeader(HttpHeaders.CONTENT_TYPE, "application/json");
			HttpResponse response = httpclient.execute(requestObj);
			HttpEntity responseEntity = response.getEntity();
			System.out.println("HTTP Status Code : " + response.getStatusLine().getStatusCode());
			if(responseEntity != null)
			{
				Object responseObject = EntityUtils.toString(responseEntity);
				String responseString = responseObject.toString();
				System.out.println(responseString);
			}
		}
		catch(Exception ex)
		{
			ex.printStackTrace();
		}
	}
}
3.0.07.x
Copied//Get instance of QueryOperations Class
$queryOperations = new QueryOperations();
//Get instance of BodyWrapper Class that will contain the request body
$bodyWrapper = new BodyWrapper();
$selectQuery = "select Last_Name from Leads where Last_Name is not null limit 200";
$bodyWrapper->setSelectQuery($selectQuery);
//Call getRecords method that takes BodyWrapper instance as parameter 
$response = $queryOperations->getRecords($bodyWrapper);
Copied<?php
class GetRecordsthroughaCOQLQuery{
    public function execute(){
        $curl_pointer = curl_init();
        
        $curl_options = array();
        $url = "https://www.zohoapis.com/crm/v2/coql";

        $curl_options[CURLOPT_URL] = $url;
        $curl_options[CURLOPT_RETURNTRANSFER] = true;
        $curl_options[CURLOPT_HEADER] = 1;
        $curl_options[CURLOPT_CUSTOMREQUEST] = "POST";
        $requestBody = array();

        $requestBody["select_query"] = "select Last_Name, First_Name, Full_Name, Lead_Source from Leads where Last_Name = 'Last Name' limit 2";
        $curl_options[CURLOPT_POSTFIELDS]= json_encode($requestBody);
        $headersArray = array();
        $headersArray[] = "Authorization". ":" . "Zoho-oauthtoken " ."1000.8cb99dxxxxxxxxxxxxx9be93.9b8xxxxxxxxxxxxxxxf";
        $curl_options[CURLOPT_HTTPHEADER]=$headersArray;
        
        curl_setopt_array($curl_pointer, $curl_options);
        
        $result = curl_exec($curl_pointer);
        $responseInfo = curl_getinfo($curl_pointer);
        curl_close($curl_pointer);
        list ($headers, $content) = explode("\r\n\r\n", $result, 2);
        if(strpos($headers," 100 Continue")!==false){
            list( $headers, $content) = explode( "\r\n\r\n", $content , 2);
        }
        $headerArray = (explode("\r\n", $headers, 50));
        $headerMap = array();
        foreach ($headerArray as $key) {
            if (strpos($key, ":") != false) {
                $firstHalf = substr($key, 0, strpos($key, ":"));
                $secondHalf = substr($key, strpos($key, ":") + 1);
                $headerMap[$firstHalf] = trim($secondHalf);
            }
        }
        $jsonResponse = json_decode($content, true);
        if ($jsonResponse == null && $responseInfo['http_code'] != 204) {
            list ($headers, $content) = explode("\r\n\r\n", $content, 2);
            $jsonResponse = json_decode($content, true);
        }
        var_dump($headerMap);
        var_dump($jsonResponse);
        var_dump($responseInfo['http_code']);
    }
}
(new GetRecordsthroughaCOQLQuery())->execute();
3.0.08.x
Copied//Get instance of QueryOperations Class
QueryOperations queryOperations = new QueryOperations();
//Get instance of BodyWrapper Class that will contain the request body
BodyWrapper bodyWrapper = new BodyWrapper();
string selectQuery = "select Last_Name from Leads where Last_Name is not null limit 200";
bodyWrapper.SelectQuery = selectQuery;
//Call getRecords method that takes BodyWrapper instance as parameter 
APIResponse<ResponseHandler> response = queryOperations.GetRecords(bodyWrapper);
Copiedusing System;
using System.IO;
using System.Net;
using System.Text;
using Newtonsoft.Json.Linq;
namespace Com.Zoho.Crm.API.Sample.RestAPI.Query
{
    public class GetRecordsthroughaCOQLQuery
    {
        public static void GetRecords()
        {
            HttpWebRequest request = (HttpWebRequest)WebRequest.Create("https://www.zohoapis.com/crm/v2/coql");
            request.Method = "POST";
            request.Headers["Authorization"] = "Zoho-oauthtoken 1000.abfeXXXXXXXXXXX2asw.XXXXXXXXXXXXXXXXXXsdc2";
            JObject requestBody = new JObject();
            requestBody.Add("select_query", "select Last_Name, First_Name, Full_Name, Lead_Source from Leads where Last_Name = 'Last Name' limit 2");
            string dataString = requestBody.ToString();
            var data = Encoding.UTF8.GetBytes(dataString);
            int dataLength = data.Length;
            request.ContentLength = dataLength;
            using (var writer = request.GetRequestStream())
            {
                writer.Write(data, 0, dataLength);
            }
            request.KeepAlive = true;
            HttpWebResponse response;
            try
            {
                response = (HttpWebResponse)request.GetResponse();
            }
            catch (WebException e)
            {
                if (e.Response == null) { throw; }
                response = (HttpWebResponse)e.Response;
            }
            HttpWebResponse responseEntity = response;
            Console.WriteLine("HTTP Status Code : " + (int)response.StatusCode);
            string responsestring = new StreamReader(responseEntity.GetResponseStream()).ReadToEnd();
            responseEntity.Close();
            Console.WriteLine(responsestring);
        }
    }
}
3.0.03.x.x
Copied# Get instance of QueryOperations Class
query_operations = QueryOperations()
# Get instance of BodyWrapper Class that will contain the request body
body_wrapper = BodyWrapper()
select_query = "select Last_Name, Account_Name.Parent_Account, Account_Name.Parent_Account.Account_Name, \
First_Name, Full_Name, Created_Time from Contacts where Last_Name is not null limit 200"
body_wrapper.set_select_query(select_query)
# Call get_records method that takes BodyWrapper instance as parameter
response = query_operations.get_records(body_wrapper)
Copieddef get_records_through_coql_query():
    import requests
    import json

    url = 'https://www.zohoapis.com/crm/v2/coql'

    headers = {
        'Authorization': 'Zoho-oauthtoken 1000.04be928e4a96XXXXXXXXXXXXX68.0b9eXXXXXXXXXXXX60396e268',
    }

    request_body = {
        'select_query': "select Last_Name, First_Name, Full_Name, Lead_Source from Leads where Last_Name = 'Last Name' limit 2"
    }

    response = requests.post(url=url, headers=headers, data=json.dumps(request_body).encode('utf-8'))

    if response is not None:
        print("HTTP Status Code : " + str(response.status_code))

        print(response.json())

get_records_through_coql_query()
1.0.010.x
Copied//Get instance of QueryOperations Class
let queryOperations = new QueryOperations();
//Get instance of BodyWrapper Class that will contain the request body
let bodyWrapper = new BodyWrapper();
let selectQuery = "select Last_Name, Account_Name.Parent_Account, Account_Name.Parent_Account.Account_Name, First_Name, Full_Name, Created_Time from Contacts where Last_Name is not null limit 200";
bodyWrapper.setSelectQuery(selectQuery);
//Call getRecords method that takes BodyWrapper instance as parameter
let response = await queryOperations.getRecords(bodyWrapper);
Copiedasync function getRecordsThroughCOQLQuery() {
    const got = require("got");
    let url = 'https://www.zohoapis.com/crm/v2/coql'
    let headers = {
        Authorization : "Zoho-oauthtoken 1000.8cb99dxxxxxxxxxxxxx9be93.9b8xxxxxxxxxxxxxxxf"
    }
    let requestBody = {
        'select_query': "select Last_Name, First_Name, Full_Name, Lead_Source from Leads where Last_Name = 'Last Name' limit 200"
    }

    let requestDetails = {
        method : "POST",
        headers : headers,
        body : JSON.stringify(requestBody),
        encoding: "utf8",
        throwHttpErrors : false
    };
    
    let response = await got(url, requestDetails)
    
    if(response != null) {
        console.log(response.statusCode);
        console.log(response.body);
    }  
}
getRecordsThroughCOQLQuery()
2.02.x.x
Copied# Get instance of QueryOperations Class
qo = Query::QueryOperations.new
# Get instance of BodyWrapper Class that will contain the request body
bw = Query::BodyWrapper.new
select_query = 'select Last_Name from Leads where Last_Name is not null limit 5'
bw.select_query = select_query
# Call get_records method that takes BodyWrapper instance as parameter
response = qo.get_records(bw)
Copiedclass GetRecordsthroughaCOQLQuery
    def execute
       
        url ="https://www.zohoapis.com/crm/v2/coql"
        url = URI(url)
        req = Net::HTTP::Post.new(url.request_uri)
        http = Net::HTTP.new(url.host, url.port)
        http.use_ssl = true
        headers={}
        headers["Authorization"]="Zoho-oauthtoken 1000.50XXXXXXXXX&77e3a.44XXXXXXXXX8353"
        headers&.each { |key, value| req.add_field(key, value) }
       
        request_body = {};
        request_body["select_query"] = "select Last_Name, First_Name, Full_Name, Lead_Source from Leads where Last_Name = 'Last Name' limit 2";
        request_json = request_body.to_json
        req.body = request_json.to_s
        response=http.request(req)
        status_code = response.code.to_i
        headers = response.each_header.to_h
        print status_code
        print headers
        unless response.body.nil?
            print  response.body
        end
    end
end

GetRecordsthroughaCOQLQuery.new.execute
1.0.0ES6
Copied//Get instance of QueryOperations Class
let queryOperations = new ZCRM.Query.Operations();
//Get instance of BodyWrapper Class that will contain the request body
let bodyWrapper = new ZCRM.Query.Model.BodyWrapper();
let selectQuery = "select Last_Name, Account_Name.Parent_Account, Account_Name.Parent_Account.Account_Name, First_Name, Full_Name, Created_Time from Contacts where Last_Name is not null limit 200";
bodyWrapper.setSelectQuery(selectQuery);
//Call getRecords method that takes BodyWrapper instance as parameter
let response = await queryOperations.getRecords(bodyWrapper);
Copiedvar listener = 0;
class GetRecordsthroughaCOQLQuery {

	async getRecords()	{
		var url = "https://www.zohoapis.com/crm/v2/coql"
        var parameters = new Map()
        var headers = new Map()
        var token = {
            clientId:"1000.NPY9M1V0XXXXXXXXXXXXXXXXXXXF7H",
            redirectUrl:"http://127.0.0.1:5500/redirect.html",
            scope:"ZohoCRM.users.ALL,ZohoCRM.bulk.read,ZohoCRM.modules.ALL,ZohoCRM.settings.ALL,Aaaserver.profile.Read,ZohoCRM.org.ALL,profile.userphoto.READ,ZohoFiles.files.ALL,ZohoCRM.bulk.ALL,ZohoCRM.settings.variable_groups.ALL"
        }
        var accesstoken = await new GetRecordsthroughaCOQLQuery().getToken(token)
        headers.set("Content-Type", "application/json")
        headers.set("Authorization", "Zoho-oauthtoken " + accesstoken)
        var requestMethod = "POST"
        var reqBody = {"select_query":"select Last_Name from Leads where Last_Name is not null"}
        var params = "";
        parameters.forEach(function(value, key) {
            if (parameters.has(key)) {
                if (params) {
                    params = params + key + '=' + value + '&';
                }
                else {
                    params = key + '=' + value + '&';
                }
            }
        });
        var apiHeaders = {};
        if(headers) {
            headers.forEach(function(value, key) {
                apiHeaders[key] = value;
            });
        }
        if (params.length > 0){
            url = url + '?' + params.substring(0, params.length - 1);
        }
        var requestObj = {
            uri : url,
            method : requestMethod,
            headers : apiHeaders,
            body : JSON.stringify(reqBody),
            encoding: "utf8",
            allowGetBody : true,
			throwHttpErrors : false
        };
        var result = await new GetRecordsthroughaCOQLQuery().makeAPICall(requestObj);
        console.log(result.status)
        console.log(result.response)
	}

    async getToken(token) {

        if(listener == 0) {

            window.addEventListener("storage", function(reponse) {
                if(reponse.key === "access_token" && (reponse.oldValue != reponse.newValue || reponse.oldValue == null)){
                    location.reload();
                }
                if(reponse.key === "access_token"){

                    sessionStorage.removeItem("__auth_process");
                }
            }, false);
            listener = 1;
            if(sessionStorage.getItem("__auth_process")) {
                sessionStorage.removeItem("__auth_process");
            }
        }
        ["granted_for_session", "access_token","expires_in","expires_in_sec","location","api_domain","state","__token_init","__auth_process"].forEach(function (k) {
            var isKeyExists = localStorage.hasOwnProperty(k);
            if(isKeyExists) {
                sessionStorage.setItem(k, localStorage[k]);
            }
            localStorage.removeItem(k);
        });
        var valueInStore = sessionStorage.getItem("access_token");
        var tokenInit = sessionStorage.getItem("__token_init");
        if(tokenInit != null && valueInStore != null && Date.now() >= parseInt(tokenInit) + 59 * 60 * 1000){ // check after 59th minute
            valueInStore = null;
            sessionStorage.removeItem("access_token");
        }

        var auth_process = sessionStorage.getItem("__auth_process");
        if ((valueInStore == null && auth_process == null) || (valueInStore == 'undefined' && (auth_process == null || auth_process == "true"))) {
            var accountsUrl = "https://accounts.zoho.com/oauth/v2/auth"
            var clientId;
            var scope;
            var redirectUrl;
            if(token != null) {
                clientId = token.clientId;
                scope = token.scope;
                redirectUrl = token.redirectUrl;
            }

            var fullGrant = sessionStorage.getItem("full_grant");
            var grantedForSession = sessionStorage.getItem("granted_for_session");
            if(sessionStorage.getItem("__token_init") != null && ((fullGrant != null && "true" == full_grant) || (grantedForSession != null && "true" == grantedForSession))) {
                accountsUrl += '/refresh';
            }
            if (clientId && scope) {
                sessionStorage.setItem("__token_init", Date.now());
                sessionStorage.removeItem("access_token");
                sessionStorage.setItem("__auth_process", "true");
                window.open(accountsUrl + "?" + "scope" + "=" + scope + "&"+ "client_id" +"=" + clientId + "&response_type=token&state=zohocrmclient&redirect_uri=" + redirectUrl);
                ["granted_for_session", "access_token","expires_in","expires_in_sec","location","api_domain","state","__token_init","__auth_process"].forEach(function (k) {
                    var isKeyExists = localStorage.hasOwnProperty(k);
                    if(isKeyExists){
                        sessionStorage.setItem(k, localStorage[k]);
                    }
                    localStorage.removeItem(k);
                });
                valueInStore = sessionStorage.getItem("access_token");
            }
        }
        if(token != null && valueInStore != 'undefined'){
            token.accessToken = valueInStore;
        }
        return token.accessToken;
    }

    async makeAPICall(requestDetails) {
        return new Promise(function (resolve, reject) {
            var body, xhr, i;
            body = requestDetails.body || null;
            xhr = new XMLHttpRequest();
            xhr.withCredentials = true;
            xhr.open(requestDetails.method, requestDetails.uri, true);
            for (i in requestDetails.headers) {
                xhr.setRequestHeader(i, requestDetails.headers[i]);
            }
            xhr.send(body);
            xhr.onreadystatechange = function() {
                if(xhr.readyState == 4) {
                    resolve(xhr);
                }
            }
        })
    }
}
CopiedqueryMap = Map();
queryMap.put("select_query", "select Last_Name, First_Name, Full_Name from Contacts where Last_Name = 'Boyle' and First_Name is not null limit 2");
response = invokeurl
[
	url :"https://www.zohoapis.com/crm/v2/coql"
	type :POST
	parameters: queryMap.toString()
	connection:"crm_oauth_connection"
];
info response;

In the request, "@input.json" contains the sample input data.

Field Types and their Comparators

The following sections describe the field types and their allowed comparators in the COQL query with an example each.

  • Text, Picklist

    =, !=, like(used for starts_with, ends_with, contains), not like(used for not_contains), in, not in, is null, is not null.

Sample Input

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

Response JSON Keys

  • First_Namestring

    Represents the first name of the contact.

  • Last_Namestring

    Represents the last name of the contact.

  • Full_Namestring

    Represents the full name of the contact.

  • idstring

    Represents the unique ID of the contact.

  • Languages_KnownJSON array

    Represents the values selected in the multi-select picklist.

  • Lead_Sourcestring

    Represents the value selected in the picklist.

Sample Response

Copied{
  "data": [
    {
      "First_Name": "Patricia",
      "Full_Name": "Patricia Boyle",
      "Last_Name": "Boyle",
      "Languages_Known": [
        "English",
        "German"
      ],
      "Lead_Source": "Advertisement",
      "id": "554023000000310003"
    },
    {
      "First_Name": "Steve",
      "Full_Name": "Steve Boyle",
      "Last_Name": "Boyle",
      "Languages_Known": [
        "English",
        "German"
      ],
      "Lead_Source": "Advertisement",
      "id": "554023000000310012"
    }
  ],
  "info": {
    "count": 2,
    "more_records": false
  }
}
  • 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 Input

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"
}

In this query, the join is established through the lookup field Account_Name in the Contacts module.

Response JSON Keys

  • Account_Name.Account_Namestring

    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.

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 query With two relations (joins)

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.

Sample Input

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"
}

Response JSON Keys

  • Account_Name.Parent_Account.Account_Namestring

    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 query with User/Owner Lookup Field

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 Input

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

Response JSON Keys

  • OwnerJSON object

    Represents the unique ID of the record owner.

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
    }
}
  • Date, DateTime, Number, Currency

    =, !=, >=, >, <=, <, between, not between, in, not in, is null, is not null.

Sample Input

Copied{
"select_query": "select Last_Name, First_Name, Full_Name, Created_Time, Date_1, No_of_Employees, Annual_Revenue 
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' and No_of_Employees between 0 and 100 and Annual_Revenue between 100 and 1000000 limit 2"
}

Response JSON Keys

  • Created_TimeDate Time in ISO 8601 format

    Represents the date and time at which the record was created.

  • Date_1Date

    Represents the date at which the record was created.

  • No_of_EmployeesNumber

    Represents the total number of employees in the company.

  • Annual_RevenueCurrency

    Represents the annual revenue of the company.

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",
      "No_of_Employees": 100,
      "Annual_Revenue": "1000"
    },
    {
      "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",
      "No_of_Employees": 60,
      "Annual_Revenue": "10000"
    }
  ],
  "info": {
    "count": 2,
    "more_records": true
  }
}
  • Boolean

    =

Sample Request

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

Response JSON Keys

  • Email_Opt_OutBoolean

    Represents the email preference of the contact.

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.

  • The value of the fields with sensitive health data will be retrieved only when Restrict Data access through API option in the compliance settings is disabled. If the option is enabled, the value will be null. Refer to HIPAA compliance for more details.

Sample Response

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

    =, !=, in, not in, is null, is not null.

    The What_Id support applicable only for Tasks, Calls, and Events. While using What_Id to retrieve records in COQL, the associated record may be one of several different types of records. For example, the What_Id field of a Task may be a Lead or a Contact.

Sample Input

Copied{
    "select_query": "select 'What_Id->Leads.Last_Name' from Tasks where 'What_Id->Leads.id' = '4150868000004479013'"
}

Response JSON Keys

  • What_Id->Leads.Last_NameString

    Represents last name of the Lead for whom the activity (Task, Call, Event) was created.

Sample Response

Copied{
    "data": [
        {
            "What_Id->Leads.Last_Name": "Patricia Boyle",
            "id": "4150868000004920001"
        }
    ],
    "info": {
        "count": 1,
        "more_records": false
    }
}

Possible Errors

  • SYNTAX_ERRORHTTP 400

    The query does not contain either proper criteria, base table, or the where clause.
    Resolution: Form a query with proper criteria, base table, and the where clause.

  • SYNTAX_ERRORHTTP 400

    The query does not contain the from clause.
    Resolution: Form a query with the from clause.

  • SYNTAX_ERRORHTTP 400

    The request contains a query other than select.
    Example: ""select_query" : "update Leads set Last_Name = 'Last' where id = 12356".
    Resolution: You can only use the Select statement in your select query.

  • SYNTAX_ERRORHTTP 400

    Parsing is happening for so long for the given query. please validate the query.
    Resolution: The given query is either too complex or has unbalanced parentheses. Please specify a valid and optimized query.

  • LIMIT_EXCEEDEDHTTP 400

    The value of limit clause or the select column (field API names) has exceeded the maximum limit of 200 and 50, respectively.
    Resolution: You can fetch only a maximum of 200 records and 50 fields in a single API call.

  • LIMIT_EXCEEDEDHTTP 400

    The query has more than two joins.
    Example: ""select_query" : "select Account_Name.Account_Name, Account_Name.Parent_Account.Account_Name, Vendor_Name.Vendor_Name from Contacts where Lead_Source = Advertisement"
    Resolution: You can only have a maximum of two joins in a select query.

  • INVALID_QUERYHTTP 400

    The query contains an invalid column name (field_API_name).
    Example: "select Last_Name, Testing from Leads where Last_Name is not null"
    Resolution: Provide a valid field API name.

  • INVALID_QUERYHTTP 400

    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.
    Resolution: Provide field API names with valid data type.

  • INVALID_QUERYHTTP 400

    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.
    Resolution: Provide the values of the select column corresponding to its data type.

  • INVALID_QUERYHTTP 400

    What_Id references should refer the single module in the query.
    Example: "select_query" : "select_query":"select 'What_Id->Contacts.Last_Name' from Tasks/Calls/Events where 'What_Id->Leads.id' = '111111000000048055'"; Resolution: Ensure that you refer to a single module with What_Id.

  • INVALID_QUERYHTTP 400

    The query contains an invalid operator.
    Example: "select_query" : "select Last_Name from Leads where Last_Name is not null and Last_Name >= 'adfkahfd'" Resolution: Use only those operators that are accepted by the respective fields.

  • OAUTH_SCOPE_MISMATCHHTTP 401

    User does not have the required scope to access the module.
    Resolution: Contact your administrator to obtain the required permission.

  • DUPLICATE_DATAHTTP 401

    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.
    Resolution: Ensure that there are no duplicate select columns in the query.

  • INTERNAL_ERRORHTTP 500

    Internal Server Error
    Resolution: Unexpected and unhandled exception in Server. Contact support team.

  • INVALID_REQUEST_METHODHTTP 400

    The http request method type is not a valid one
    Resolution: You have specified an invalid HTTP method to access the API URL. Specify a valid request method. Refer to endpoints section above.