Home
API
COQL Query

How to Fetch Values from Multiple Select Fields in Zoho CRM Using COQL Query by Passing Multiple Field Criteria

1. What are multi-select picklist fields in Zoho CRM? A multi-select picklist field in Zoho CRM allows a single record to hold more than one value from a predefined list. Unlike a standard single-select picklist, a multi-select field lets users pick several options simultaneously. Common examples include:
🏛

Industries served

A recruiter covering Agriculture, Education, and Arts — all in one field.

📍

Geographic areas

A vendor covering Arizona, Colorado, and California stored as one field value.

💼

Job functions

A specialist handling both Business Development and Finance roles.

🏆

Seniority levels

A recruiter who places both Senior Managers and Vice Presidents.

Internally, Zoho CRM stores all selected values from a multi-select picklist as a single semicolon-separated string. According to the official Zoho CRM COQL API documentation[1], a record with three selected industries is stored like this:
Field: What_Industries_Do_They_Service
Stored value: "Agriculture, Food, and Natural Resources;Arts, Culture, Performance, and Entertainment;Education and Learning"
This internal storage format is the root cause of every querying challenge with multi-select fields — understanding it is the key to solving them correctly. 2. Why the standard Zoho search API fails for multi-select fields Zoho CRM's built-in zoho.crm.searchRecords() and the standard REST search endpoint rely on simple equality or basic "contains" matching. They cannot handle complex nested OR/AND logic across multiple multi-select fields simultaneously.
⚠ Common mistake: Using zoho.crm.searchRecords("Contacts", "Industry:equals:Agriculture") only returns records where the field contains exactly that one string — it fails completely on multi-select picklist fields that store several values joined together.
Suppose you need contacts who meet all of these criteria at once:
  • ✓Serve at least one of: Agriculture or Arts & Culture
  • ✓Cover at least one of: Arizona, Colorado, or Anywhere Nationwide
  • ✓Handle at least one of: Business Development or Sales
  • ✓Specialise in at least one of: Senior Manager or Vice President
The standard search API cannot express this multi-field, multi-value OR + AND logic in a single call. This is exactly where Zoho COQL[2] becomes the only correct solution. 3. What is COQL and how does LIKE work on multi-select fields? COQL stands for CRM Object Query Language. As described in the COQL Query API Overview[2], it is Zoho CRM's SQL-inspired query engine. Key limits:
  • Up to 25 criteria per WHERE clause
  • Up to 2,000 records per API call (paginate with OFFSET)
  • All queries are POST requests to https://www.zohoapis.com/crm/v8/coql
The critical operator for multi-select picklist fields is LIKE '%value%'. The Zoho COQL field types documentation[1] explicitly confirms LIKE as the correct comparator for multi-select picklist fields:
COQL — correct vs incorrect for multi-select fields
-- CORRECT: finds contacts where "Agriculture" appears
-- anywhere in the semicolon-separated field string
SELECT First_Name, Last_Name FROM Contacts
WHERE  What_Industries_Do_They_Service LIKE '%Agriculture, Food, and Natural Resources%'

-- INCORRECT: only matches if the ENTIRE field
-- contains exactly this one value and nothing else
SELECT First_Name FROM Contacts
WHERE  What_Industries_Do_They_Service = 'Agriculture, Food, and Natural Resources'
💡 Why wildcards on BOTH sides? The leading % handles cases where the target is not the first item in the stored list. The trailing % handles cases where it is not the last. Per the Zoho docs[1], NOT LIKE also requires wildcards on both sides for a "not contains" check.
4. COQL vs standard search API — comparison table
FeatureStandard zoho.crm.searchRecords()COQL API
Multi-select picklist partial match✗ Not supported✓ LIKE '%value%'
Nested OR / AND logic✗ Very limited✓ Full nesting supported
Multiple field criteria in one call✗ Basic only✓ Up to 25 criteria
Dynamic query building✗ Cumbersome✓ String concat in Deluge
Records per API call200 max✓ Up to 2,000
Aggregate functions (COUNT, SUM…)✗ Not available✓ Full support
ORDER BY / GROUP BY✗ Not available✓ Fully supported
Pagination (OFFSET)Page-number only✓ LIMIT + OFFSET
5. Setting up the Deluge function — parsing the input The Deluge function receives filter criteria as a JSON body. Each key maps to a CRM multi-select field, and each value is a list of options selected by the user. The function supports both JSON List [] and JSON Set {} input formats transparently via Deluge's for each loop. Expected input format
JSON — input body
{
  "geographic_area": ["Anywhere Nationwide", "Arizona", "Colorado"],
  "industries":       ["Agriculture, Food, and Natural Resources",
                        "Arts, Culture, Performance, and Entertainment"],
  "job":              ["Business Development", "Sales"],
  "snr_level":        ["Senior Manager", "Vice President"]
}
Function signature and input parsing
Deluge — parse input & extract lists
string standalone.Fetch_Matching_Recruiters(String crmAPIRequest)
{
  // Parse incoming request body
  resp      = crmAPIRequest.toMap();
  inputData = ifnull(resp.getJSON("body"), "");

  // Configuration
  size        = "20";
  addUserType = true;
  userType    = "Recruiter";

  // Extract each filter into a Deluge List
  // for each handles BOTH {} Set and [] List formats transparently
  indList = List();
  for each opt in inputData.get("industries")     { indList.add(opt); }

  geoList = List();
  for each opt in inputData.get("geographic_area") { geoList.add(opt); }

  jobList = List();
  for each opt in inputData.get("job")              { jobList.add(opt); }

  snrList = List();
  for each opt in inputData.get("snr_level")        { snrList.add(opt); }
}
6. Building OR clauses for each multi-select field For each filter field, loop through values and progressively wrap each new LIKE condition in parentheses alongside the previous ones — creating a nested OR chain. The COQL Overview[2] confirms a WHERE clause supports up to 25 individual criteria.
⚠ Operator precedence: COQL evaluates AND before OR (same as SQL). Without explicit parentheses around each OR group, your query can silently return wrong results.
Deluge — building OR clause for each multi-select field
// Section 1 — Industries OR clause
indClause = ""; indFirst = true;
for each val in indList
{
  part = "(What_Industries_Do_They_Service like '%" + val + "%')";
  if(indFirst) { indClause = part; indFirst = false; }
  else         { indClause = "(" + indClause + " or " + part + ")"; }
}
// Section 2 — Geographic Areas OR clause
geoClause = ""; geoFirst = true;
for each val in geoList
{
  part = "(Geographic_Areas like '%" + val + "%')";
  if(geoFirst) { geoClause = part; geoFirst = false; }
  else         { geoClause = "(" + geoClause + " or " + part + ")"; }
}
// Section 3 — Job Functions OR clause
jobClause = ""; jobFirst = true;
for each val in jobList
{
  part = "(Job_Functions like '%" + val + "%')";
  if(jobFirst) { jobClause = part; jobFirst = false; }
  else         { jobClause = "(" + jobClause + " or " + part + ")"; }
}
// Section 4 — Seniority Levels OR clause
snrClause = ""; snrFirst = true;
for each val in snrList
{
  part = "(Seniority_levels like '%" + val + "%')";
  if(snrFirst) { snrClause = part; snrFirst = false; }
  else         { snrClause = "(" + snrClause + " or " + part + ")"; }
}
7. Combining multiple field criteria with AND Once each field has its own OR clause, combine them with AND so a matching record must satisfy at least one value from every field simultaneously. This happens in four steps:
  • AGeo AND Job — combine Geographic_Areas OR clause AND Job_Functions OR clause into the base coreFilter.
  • BAND Seniority — appended only when snrList.size() > 0. Skipped entirely when no seniority values are passed.
  • CAND User_Type — controlled by an addUserType boolean flag. Set to false to skip entirely.
  • DIndustries AND coreFilter — wraps everything into the final fullWhere string.
Deluge — combining all clauses into fullWhere
// Step A: geo AND job
if(geoClause != "" && jobClause != "")
  { coreFilter = "(" + geoClause + " and " + jobClause + ")"; }
else if(geoClause != "") { coreFilter = geoClause; }
else if(jobClause != "") { coreFilter = jobClause; }
else                    { coreFilter = ""; }

// Step B: AND seniority (only when snrList is non-empty)
if(snrList.size() > 0 && snrClause != "")
{
  if(coreFilter != "")
    { coreFilter = "(" + coreFilter + " and " + snrClause + ")"; }
  else { coreFilter = snrClause; }
}

// Step C: AND User_Type (only when addUserType = true)
if(addUserType == true && userType != "")
{
  userTypeClause = "(User_Type = '" + userType + "')";
  if(coreFilter != "")
    { coreFilter = "(" + coreFilter + " and " + userTypeClause + ")"; }
  else { coreFilter = userTypeClause; }
}

// Step D: industries AND coreFilter = fullWhere
if(indClause != "" && coreFilter != "")
  { fullWhere = "(" + indClause + " and " + coreFilter + ")"; }
else if(indClause != "")  { fullWhere = indClause; }
else if(coreFilter != "") { fullWhere = coreFilter; }
else                      { fullWhere = "id is not null"; }
8. Executing the COQL query and returning results With the WHERE clause assembled, build the SELECT string and POST it to the Zoho CRM v8 COQL endpoint[1] using Deluge's invokeurl.
Deluge — execute COQL and return count
selectQuery = "select What_Industries_Do_They_Service from Contacts where "
            + fullWhere + " limit " + size;

queryMap = Map();
queryMap.put("select_query", selectQuery);

response = invokeurl
[
  url        : "https://www.zohoapis.com/crm/v8/coql"
  type       : POST
  parameters : queryMap.toString()
  connection : "zcrm_coql"
];

count = response.getJSON("info").getJSON("count");
return {"count" : count};
🔔 Pagination: Per the COQL API docs[2], fetch up to 2,000 records with LIMIT 0, 2000. For datasets beyond 100,000 records, add id > {last_record_id} to continue paging.
Real-world use cases — input, generated query & expected output The three cases below show the dynamic query builder with different input formats, varying value counts, and duplicate entries. All use the same Deluge function and the same COQL endpoint[1].
Use case 1 — JSON List [], multiple values per field
Two industries, three geo areas (incl. "Anywhere Nationwide"), two jobs, two seniority levelsAll four fields use standard JSON List array [] notation.
✓ Output: 1 matching record
Industries[] ListAgriculture, Food, and Natural ResourcesArts, Culture, Performance, and Entertainment
Geographic area[] ListAnywhere NationwideArizonaColorado
Job functions[] ListBusiness DevelopmentSales
Seniority level[] ListSenior ManagerVice President
Deluge — Case 1 inputData
// Case 1 — JSON List [] notation
inputData = {
  "geographic_area": ["Anywhere Nationwide", "Arizona", "Colorado"],
  "industries":       ["Agriculture, Food, and Natural Resources",
                        "Arts, Culture, Performance, and Entertainment"],
  "job":              ["Business Development", "Sales"],
  "snr_level":        ["Senior Manager", "Vice President"]
};
Generated COQL query — Case 1
SELECT What_Industries_Do_They_Service FROM Contacts
WHERE
((What_Industries_Do_They_Service LIKE '%Agriculture, Food, and Natural Resources%')
  OR (What_Industries_Do_They_Service LIKE '%Arts, Culture, Performance, and Entertainment%'))
AND
((((Geographic_Areas LIKE '%Anywhere Nationwide%')
    OR (Geographic_Areas LIKE '%Arizona%'))
    OR (Geographic_Areas LIKE '%Colorado%'))
 AND ((Job_Functions LIKE '%Business Development%') OR (Job_Functions LIKE '%Sales%'))
 AND ((Seniority_levels LIKE '%Senior Manager%') OR (Seniority_levels LIKE '%Vice President%'))
 AND  (User_Type = 'Recruiter'))
LIMIT 20
Expected API response 
{
  "data": [
    { "What_Industries_Do_They_Service": "Agriculture, Food, and Natural Resources;Arts, Culture...",
      "id": "5725767000000678203" }
  ],
  "info": { "count": 1, "more_records": false }
}

💡 Why output = 1

  • All four OR clauses are AND-ed together — only records matching at least one value from every field pass the filter.
  • With addUserType = true, the query also filters by User_Type = 'Recruiter', making conditions very specific.
  • "Anywhere Nationwide" is the most permissive geo option, yet the strict seniority + industry combination limits results to 1 record.
Use case 2 — JSON Set {}, single/few values per field
One industry, four specific states, two jobs, one seniority levelAll fields use JSON Set {} curly-brace notation instead of List arrays.
✓ Output: 2 matching records
Industries{} SetAgriculture, Food, and Natural Resources
Geographic area{} SetAlabamaAlaskaCaliforniaColorado
Job functions{} SetBusiness DevelopmentFinance
Seniority level{} SetSenior Manager
Deluge — Case 2 inputData
// Case 2 — JSON Set {} notation
inputData = {
  "geographic_area": {"Alabama", "Alaska", "California", "Colorado"},
  "industries":       {"Agriculture, Food, and Natural Resources"},
  "job":              {"Business Development", "Finance"},
  "snr_level":        {"Senior Manager"}
};
Generated COQL query — Case 2
SELECT What_Industries_Do_They_Service FROM Contacts
WHERE
(What_Industries_Do_They_Service LIKE '%Agriculture, Food, and Natural Resources%')
AND
((((Geographic_Areas LIKE '%Alabama%') OR (Geographic_Areas LIKE '%Alaska%'))
    OR (Geographic_Areas LIKE '%California%')) OR (Geographic_Areas LIKE '%Colorado%'))
 AND ((Job_Functions LIKE '%Business Development%') OR (Job_Functions LIKE '%Finance%'))
 AND  (Seniority_levels LIKE '%Senior Manager%')
 AND  (User_Type = 'Recruiter'))
LIMIT 20
Expected API response 
{
  "data": [
    { "What_Industries_Do_They_Service": "Agriculture, Food, and Natural Resources", "id": "5725767000000678210" },
    { "What_Industries_Do_They_Service": "Agriculture, Food, and Natural Resources;Education...", "id": "5725767000000678215" }
  ],
  "info": { "count": 2, "more_records": false }
}

💡 Why output = 2 — and why Set {} works like List []

  • Deluge's for each iterates JSON Set {} and JSON List [] transparently — no type-checking needed.
  • Industry has one value so its clause is a single LIKE condition — no OR wrapping needed.
  • Four specific US states plus "Finance" as a job broadens the match — 2 recruiter contacts satisfy all conditions.
Use case 3 — JSON List [], duplicate values in seniority
Two industries, four geo areas, two jobs — seniority list contains "Vice President" twiceShows how duplicate input values affect the generated query and why they don't change results but consume criteria slots.
✓ Output: 2 matching records
Industries[] ListAgriculture, Food, and Natural ResourcesArts, Culture, Performance, and Entertainment
Geographic area[] ListAlabamaAlaskaCaliforniaColorado
Job functions[] ListBusiness DevelopmentFinance
Seniority level[] ListSenior ManagerVice PresidentVice President ⚠ duplicateSenior Vice President
Deluge — Case 3 inputData
// Case 3 — List [] with duplicate "Vice President" in snr_level
inputData = {
  "geographic_area": ["Alabama", "Alaska", "California", "Colorado"],
  "industries":       ["Agriculture, Food, and Natural Resources",
                        "Arts, Culture, Performance, and Entertainment"],
  "job":              ["Business Development", "Finance"],
  "snr_level":        ["Senior Manager", "Vice President",
                        "Vice President", // ← duplicate entry
                        "Senior Vice President"]
};
Generated COQL query — Case 3 (duplicate highlighted)
SELECT What_Industries_Do_They_Service FROM Contacts
WHERE
((What_Industries_Do_They_Service LIKE '%Agriculture, Food, and Natural Resources%')
  OR (What_Industries_Do_They_Service LIKE '%Arts, Culture, Performance, and Entertainment%'))
AND
((((Geographic_Areas LIKE '%Alabama%') OR (Geographic_Areas LIKE '%Alaska%'))
    OR (Geographic_Areas LIKE '%California%')) OR (Geographic_Areas LIKE '%Colorado%'))
 AND ((Job_Functions LIKE '%Business Development%') OR (Job_Functions LIKE '%Finance%'))
 AND ((((Seniority_levels LIKE '%Senior Manager%')
         OR (Seniority_levels LIKE '%Vice President%'))
         OR (Seniority_levels LIKE '%Vice President%'))  ← duplicate, no effect on results
         OR (Seniority_levels LIKE '%Senior Vice President%'))
 AND  (User_Type = 'Recruiter'))
LIMIT 20
Expected API response 
{
  "data": [
    { "What_Industries_Do_They_Service": "Agriculture, Food, and Natural Resources", "id": "5725767000000678210" },
    { "What_Industries_Do_They_Service": "Agriculture, Food, and Natural Resources;Arts, Culture...", "id": "5725767000000678224" }
  ],
  "info": { "count": 2, "more_records": false }
}

💡 Why output = 2 — and what the duplicate does

  • The duplicate generates a redundant LIKE '%Vice President%' — zero effect on results inside an OR group.
  • However, it counts toward the 25-criteria limit — strongly recommended to deduplicate input lists in production.
  • Addition of "Senior Vice President" and the broader state-level geo scope explains 2 matching records vs 1 in Case 1.
Use case comparison summary
Case 11List [], "Anywhere Nationwide" geo, strict seniority
Case 22Set {}, 4 specific states, single industry & seniority
Case 32List [], duplicate VP entry, broader seniority scope
FactorCase 1Case 2Case 3
Input formatJSON List []JSON Set {}JSON List []
Industries2 values1 value2 values
Geo filter3 (incl. Nationwide)4 specific states4 specific states
Job filterSales + Biz DevBiz Dev + FinanceBiz Dev + Finance
Seniority filterSr Manager + VPSr Manager onlySr Mgr + VP + VP(dup) + Sr VP
Duplicate valuesNoneNoneYes — "VP" ×2
Output count122
Conclusion Querying multi-select picklist fields in Zoho CRM is a challenge the standard search API was never built to handle elegantly. The correct approach is to combine Zoho's COQL Query API[2] with a dynamically built WHERE clause in Deluge scripting — using the LIKE '%value%' operator that the official Zoho documentation[1] confirms as the correct comparator for multi-select fields.

🎯 Key takeaways

  • Multi-select fields store all values as one semicolon-separated string — exact-match = will not work.
  • Use LIKE '%value%' with wildcards on both sides to match at any position in the stored string.
  • Build one OR clause per field by iterating selected values and wrapping each in parentheses.
  • Combine all field OR clauses with AND — explicit parentheses enforce correct operator precedence.
  • Deluge's for each handles both JSON Set {} and JSON List [] without any extra type-checking.
  • Make optional filters conditional using size checks and boolean flags — empty inputs are skipped cleanly.
  • Deduplicate input lists in production — duplicates consume criteria slots (max 25 per WHERE).
  • COQL supports up to 2,000 records per call with full OFFSET pagination.
This pattern is fully reusable across any Zoho CRM module and any combination of multi-select fields. For advanced queries, see the COQL Subquery documentation[3] for IN, NOT IN, and BETWEEN operators.
Frequently asked questions
Can I use this approach for multi-select fields in modules other than Contacts?+
Yes. Replace Contacts in the SELECT clause with any module API name such as Leads, Deals, or a custom module. The LIKE-based OR pattern works for any multi-select picklist in any module. Refer to the Zoho COQL field types documentation for all supported comparators.
What is the maximum number of records COQL returns per call?+
Per the COQL API Overview, you can retrieve up to 2,000 records per call using LIMIT 0, 2000. For larger datasets paginate using OFFSET. Beyond 100,000 records, add id > {last_record_id} to your WHERE clause.
Why does each OR condition need its own parentheses?+
COQL evaluates AND before OR — same precedence as SQL. Without parentheses around each OR group, the database applies AND first and produces incorrect results. Parentheses ensure OR options for one field are grouped before the AND between fields is applied.
What happens if the user passes no values for a field?+
Each clause variable stays as an empty string "". The combination logic skips empty clauses entirely. If all clauses are empty the function falls back to WHERE id is not null, returning all records up to the LIMIT.
How do I set up the zcrm_coql OAuth connection?+
Go to Zoho CRM → Setup → Developer Space → Connections → New Connection. Select Zoho CRM, add scopes ZohoCRM.modules.contacts.READ and ZohoCRM.coql.READ, name the connection zcrm_coql. This must exactly match the connection parameter in the invokeurl block.
Should I deduplicate input values before passing them to the function?+
Yes — strongly recommended for production. As shown in Use Case 3, a duplicate value generates a redundant LIKE condition that has no effect on results but does count toward the 25-criteria limit per WHERE clause.
Can I return the full record data instead of just the count?+
Yes. Access response.getJSON("data") to retrieve the full list of matching records. Add more fields to the SELECT clause to include additional CRM field values in each returned record.
References All links below point to official Zoho documentation and the official Zoho developer community.

Post a Comment