Does HubSpot's API support partial matches with the IN operator?

Other Questions HubSpot
1

I’ve set up a custom field in HubSpot for companies called erp_identifier. We need to verify if any companies match a list of these identifiers by using the IN operator in the API. However, I encountered an issue where the query only works if every value in the list exists in the database. I expected the API to return partial matches and ignore the non-existent identifiers. Is the API designed to function this way?

Here’s an example of my API call:

{
  "filterGroups": [
    {
      "filters": [
        {
          "values": [
            "ACCT001-9876",
            "ACCT002-5432",
            "ACCT003-1098"
          ],
          "propertyName": "erp_identifier",
          "operator": "IN"
        }
      ]
    }
  ],
  "properties": ["erp_identifier"]
}

In this sample, only one identifier actually exists in the companies table, yet the query returns no results. If I remove the non-existent identifiers and search with just the valid one, the API responds correctly.

Am I misunderstanding how the IN operator is supposed to work with partial matches in HubSpot’s API?

2

Based on my experience working with HubSpot’s API, the IN operator does indeed behave as you’ve described. It’s designed to return results only when all specified values in the list match existing records. This can be frustrating when you’re trying to query for partial matches.

A workaround I’ve used is to split the query into multiple filter groups, each containing a single value. This approach allows for partial matches. Here’s how you could modify your API call:

{
  "filterGroups": [
    {"filters": [{"propertyName": "erp_identifier", "operator": "EQ", "value": "ACCT001-9876"}]},
    {"filters": [{"propertyName": "erp_identifier", "operator": "EQ", "value": "ACCT002-5432"}]},
    {"filters": [{"propertyName": "erp_identifier", "operator": "EQ", "value": "ACCT003-1098"}]}
  ],
  "properties": ["erp_identifier"]
}

This method should return any matching records, even if not all specified identifiers exist in the database.

3

yea, the IN operator in hubspot can be tricky. i’ve run into similar issues before. have u tried using multiple OR conditions instead? like this:

{“filterGroups”:[{“filters”:[{“propertyName”:“erp_identifier”,“operator”:“EQ”,“value”:“ACCT001-9876”},{“propertyName”:“erp_identifier”,“operator”:“EQ”,“value”:“ACCT002-5432”,“operator”:“OR”}]}]}

might work better for partial matches

4

I’ve encountered this exact issue with HubSpot’s API before. It’s a bit counterintuitive, but the IN operator does require all values to exist for a match. One approach that’s worked well for me is using the CONTAINS_TOKEN operator instead. It’s more flexible and allows for partial matches. Your query might look something like this:

{
“filterGroups”: [
{
“filters”: [
{
“propertyName”: “erp_identifier”,
“operator”: “CONTAINS_TOKEN”,
“value”: “ACCT001-9876 ACCT002-5432 ACCT003-1098”
}
]
}
],
“properties”: [“erp_identifier”]
}

This method searches for any of the tokens within the erp_identifier field, effectively giving you the partial match functionality you’re after. It’s been a reliable workaround in my projects when dealing with similar scenarios.