Endpoint Read Data (COQL Query Builder)

Name

get_module_data_coql_builder

Description

Build and execute a Zoho CRM COQL query using guided parameters instead of writing the full query manually. Select a module, choose fields, enter a filter condition, and optionally add GROUP BY and ORDER BY clauses. The connector generates the final COQL query and sends it to Zoho's /coql API. Results are paged automatically by adding LIMIT offset, PageSize to the generated query and continuing while Zoho returns more records. API v2 supports up to 200 rows per page. Newer API versions such as v7/v8 support larger page sizes, up to 2000 rows per page. Zoho COQL offset paging has a documented maximum window: v7/v8 allow up to 100,000 rows per query criteria, while v6 documents a 10,000-row window. For larger pulls, split the query using a stable condition such as id > last_record_id and repeat the request. Use v2 for the Emails module or when a COQL feature fails in newer API versions. If aggregate queries such as COUNT, SUM, AVG, MIN, or MAX fail, try another API version. [API reference]

Parameters

Output Columns

Input Columns

Examples

SSIS

Use Zoho CRM Connector in API Source or in API Destination SSIS Data Flow components to read or write data.

API Source

API Source - Zoho CRM

Read and write Zoho CRM data effortlessly. Integrate, manage, and automate accounts, leads, contacts, and deals — almost no coding required.

Zoho CRM

Read Data (COQL Query Builder)

Required Parameters
Module	Fill-in the parameter...
Select Fields	Fill-in the parameter...
Where Condition	Fill-in the parameter...
Optional Parameters
Group By Fields
Order By	id desc
API Version
NextUrlEndIndicator	false
StopIndicatorAttributeOrExpr	$.info.more_records
NextUrlWaitInMs	0
PagingMode	ByPostData
PagingByUrlAttributeName	{%offset%}
PagingByUrlCurrentPage	0
PageSize	200
PagingIncrementBy	200

API Destination

API Destination - Zoho CRM

Read and write Zoho CRM data effortlessly. Integrate, manage, and automate accounts, leads, contacts, and deals — almost no coding required.

Zoho CRM

Read Data (COQL Query Builder)

Required Parameters
Module	Fill-in the parameter...
Select Fields	Fill-in the parameter...
Where Condition	Fill-in the parameter...
Optional Parameters
Group By Fields
Order By	id desc
API Version
NextUrlEndIndicator	false
StopIndicatorAttributeOrExpr	$.info.more_records
NextUrlWaitInMs	0
PagingMode	ByPostData
PagingByUrlAttributeName	{%offset%}
PagingByUrlCurrentPage	0
PageSize	200
PagingIncrementBy	200

ODBC application

Use these SQL queries in your ODBC application data source:

Read data using COQL Query Builder

<p>Builds and runs a Zoho CRM COQL query using the <code>get_module_data_coql_builder</code> endpoint. Instead of typing the full COQL statement, provide the module, selected fields, filter condition, and optional grouping or sorting. The connector generates the final COQL query and sends it to Zoho's <code>/coql</code> API.</p> <p>This example queries the <code>Leads</code> module, groups records by <code>Country</code> and <code>Lead_Status</code>, and returns a <code>COUNT(id)</code> value for each group.</p> <p>Use API <code>v7</code> or <code>v8</code> for standard modules when you want larger pages, up to <strong>2000 rows per API call</strong>. API <code>v2</code> is limited to <strong>200 rows per COQL call</strong>. Zoho COQL offset pagination has a documented window limit: <code>v7</code>/<code>v8</code> allow up to <strong>100,000 rows</strong> for the same query criteria, while <code>v6</code> documents a <strong>10,000-row</strong> offset window. For larger pulls, split the query using a stable condition such as <code>id &gt; last_record_id</code> and repeat the request.</p> <p>Aggregate functions such as <code>COUNT</code>, <code>SUM</code>, <code>MIN</code>, <code>MAX</code>, and <code>AVG</code> must be uppercase. When mixing aggregate functions with regular fields, every regular field in <code>Select</code> must also be listed in <code>GroupBy</code>.</p>

SELECT *
	FROM get_module_data_coql_builder
	WITH(
		  Module = 'Leads'
		, Version = 'v8'
		, PageSize = '2000'

		-- Select regular fields and aggregate functions.
		-- Aggregate function names must be uppercase: COUNT, SUM, MIN, MAX, AVG.
		, Select = 'Country,Lead_Status,COUNT(id)'

		-- Enter condition only. Do not include the WHERE keyword.
		, Where = 'Lead_Status=''Junk Lead'' and Created_Time>''2015-07-08T03:07:16-04:00'''

		-- Required because Select mixes regular fields with COUNT(id).
		, GroupBy = 'Country,Lead_Status'

		-- Enter sort expression only. Do not include the ORDER BY keyword.
		, OrderBy = 'Country desc'
	)

Read email status metrics using COQL Query Builder (Emails module, v2)

<p>Queries the <code>Emails</code> module via COQL for metrics such as status (bounced, opened, clicked), open/click times, bounce reason, and related fields. Zoho exposes Emails in COQL only through API <strong>v2</strong>; from v3 onward this approach does not apply. With v2, <code>PageSize</code> must be 200 or less. For other modules, use v7/v8 (or v3+) if you need page sizes larger than 200.</p>

SELECT *
FROM get_module_data_coql_builder
WITH(
	  Module='Emails'
	, Version='v2' --must be v2 for Emails module , newer versions dont support Emails module in COQL
	, Select='Owner,Subject,Entity_Id,Modified_By,Created_Time,Modified_Time,Sent_To,Module,Last_Opened,Last_Clicked,Bounced_Time,First_Opened,First_Clicked,No_of_Opens,No_of_Clicks,Template_Name,Sent_On,Status,Source,Cc,Sender,Bounce_Reason,Bcc'
	--, Where='Status = ''Bounced'' and Created_Time>''2015-07-08T03:07:16-04:00''  '

  --, OrderBy='Created_Time desc'
)

SQL Server

Use these SQL queries in SQL Server after you create a data source in Data Gateway:

Read data using COQL Query Builder

<p>Builds and runs a Zoho CRM COQL query using the <code>get_module_data_coql_builder</code> endpoint. Instead of typing the full COQL statement, provide the module, selected fields, filter condition, and optional grouping or sorting. The connector generates the final COQL query and sends it to Zoho's <code>/coql</code> API.</p> <p>This example queries the <code>Leads</code> module, groups records by <code>Country</code> and <code>Lead_Status</code>, and returns a <code>COUNT(id)</code> value for each group.</p> <p>Use API <code>v7</code> or <code>v8</code> for standard modules when you want larger pages, up to <strong>2000 rows per API call</strong>. API <code>v2</code> is limited to <strong>200 rows per COQL call</strong>. Zoho COQL offset pagination has a documented window limit: <code>v7</code>/<code>v8</code> allow up to <strong>100,000 rows</strong> for the same query criteria, while <code>v6</code> documents a <strong>10,000-row</strong> offset window. For larger pulls, split the query using a stable condition such as <code>id &gt; last_record_id</code> and repeat the request.</p> <p>Aggregate functions such as <code>COUNT</code>, <code>SUM</code>, <code>MIN</code>, <code>MAX</code>, and <code>AVG</code> must be uppercase. When mixing aggregate functions with regular fields, every regular field in <code>Select</code> must also be listed in <code>GroupBy</code>.</p>

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT *
	FROM get_module_data_coql_builder
	WITH(
		  Module = ''Leads''
		, Version = ''v8''
		, PageSize = ''2000''

		-- Select regular fields and aggregate functions.
		-- Aggregate function names must be uppercase: COUNT, SUM, MIN, MAX, AVG.
		, Select = ''Country,Lead_Status,COUNT(id)''

		-- Enter condition only. Do not include the WHERE keyword.
		, Where = ''Lead_Status=''''Junk Lead'''' and Created_Time>''''2015-07-08T03:07:16-04:00''''''

		-- Required because Select mixes regular fields with COUNT(id).
		, GroupBy = ''Country,Lead_Status''

		-- Enter sort expression only. Do not include the ORDER BY keyword.
		, OrderBy = ''Country desc''
	)';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];

Read email status metrics using COQL Query Builder (Emails module, v2)

<p>Queries the <code>Emails</code> module via COQL for metrics such as status (bounced, opened, clicked), open/click times, bounce reason, and related fields. Zoho exposes Emails in COQL only through API <strong>v2</strong>; from v3 onward this approach does not apply. With v2, <code>PageSize</code> must be 200 or less. For other modules, use v7/v8 (or v3+) if you need page sizes larger than 200.</p>

DECLARE @MyQuery NVARCHAR(MAX) = 'SELECT *
FROM get_module_data_coql_builder
WITH(
	  Module=''Emails''
	, Version=''v2'' --must be v2 for Emails module , newer versions dont support Emails module in COQL
	, Select=''Owner,Subject,Entity_Id,Modified_By,Created_Time,Modified_Time,Sent_To,Module,Last_Opened,Last_Clicked,Bounced_Time,First_Opened,First_Clicked,No_of_Opens,No_of_Clicks,Template_Name,Sent_On,Status,Source,Cc,Sender,Bounce_Reason,Bcc''
	--, Where=''Status = ''''Bounced'''' and Created_Time>''''2015-07-08T03:07:16-04:00''''  ''

  --, OrderBy=''Created_Time desc''
)';

EXEC (@MyQuery) AT [LS_TO_ZOHO_CRM_IN_GATEWAY];