Using IN operator with HubSpot API: Unexpected search results

Other Questions HubSpot
1

I’ve hit a snag with the HubSpot API. I set up a custom field called ‘acct_num’ for companies. Now I’m trying to search for multiple companies using their account numbers.

I thought the IN operator would return matches and ignore non-existent values. But it’s not working as expected. Here’s what I mean:

POST /crm/v3/objects/companies/search
{
  "filterGroups": [{
    "filters": [{
      "propertyName": "acct_num",
      "operator": "IN",
      "values": [
        "ABC123",
        "XYZ789",
        "DEF456"
      ]
    }]
  }],
  "properties": ["acct_num"]
}

Only one of these account numbers actually exists. But the search returns nothing. If I remove the non-existent values and search with just the real one, it works fine.

Is this normal for HubSpot’s API? I expected it to return matches and ignore the rest. Am I missing something here?

2

hey evelynh, i had this issue too. hubspot’s in op acts strict; if one acct_num value fails, no results come back. try using separate eq filters like:

{“filters”:[{“propertyName”:“acct_num”,“operator”:“EQ”,“value”:“ABC123”},{“propertyName”:“acct_num”,“operator”:“EQ”,“value”:“XYZ789”}]}

hope it works!

3

I’ve dealt with this quirk in the HubSpot API before. It’s not intuitive, but the IN operator is quite strict. It requires all values to be present, or it returns nothing.

For your use case, I’d suggest breaking it down into multiple API calls. First, query each account number individually. Then, aggregate the results on your end. It’s more work, but it’s reliable.

Alternatively, if you’re dealing with a large number of account numbers, consider using HubSpot’s batch API endpoints. They’re designed for operations on multiple records and might be more suitable for your needs.

Remember, HubSpot’s search functionality is optimized for performance, not flexibility. Sometimes we need to adapt our approach to fit their system constraints.

4

I’ve encountered a similar issue with HubSpot’s API, and it can be quite frustrating. In my experience, the IN operator behaves differently than you might expect. It seems to require all values in the array to match existing records, or it returns nothing.

A workaround I’ve found effective is to use multiple OR conditions instead of IN. Something like this:

"filterGroups": [{
  "filters": [
    {"propertyName": "acct_num", "operator": "EQ", "value": "ABC123"},
    {"propertyName": "acct_num", "operator": "EQ", "value": "XYZ789"},
    {"propertyName": "acct_num", "operator": "EQ", "value": "DEF456"}
  ]
}]

This approach has worked well for me, returning matches for existing values while ignoring non-existent ones. It’s not as clean as using IN, but it gets the job done. Hope this helps!