Using contains one of many or does not contain one of many for string filters

Introduction

There may be times when you need to define a filter based on incoming data matching one of many given values. Conversely, you might want to define a filter based on incoming data NOT matching one of many given values. This can be achieved using the following operators in string-type filters:

Using these operators, you can specify a comma-separated list of values that a record must have/not have in a string-type field, to be a match.

This information applies wherever filters are available, not just the filter shape.

These operators are designed to work with string-type fields only.

Contains one of many

The contains one of many operator is used to match incoming records if the value of a given field DOES match any item from a provided (comma delimited) list of values. For example, consider the following payload of customer records:

[
    {"name": "John Smith",  "country": "FR"},
    {"name": "Bob Jones",  "country": "BE"},
    {"name": "Hank Smith",  "country": "AU"},
    {"name": "Jane Smith",  "country": "DE"},
    {"name": "Jack Jones",  "country": "SE"},
    {"name": "Hank Smith",  "country": "US"},
    {"name": "Paul Smith",  "country": "IE"}
]

Suppose you only want to process customer records with a European country code in the country field (which is a string type field).

You can add a filter for the country field and select the contains one of many operator - then provide a comma-separated list of acceptable country codes as the value:

The resulting payload would only include records where the country field includes one of the specified values - i.e.:

[
    {"name": "John Smith",  "country": "FR"},
    {"name": "Bob Jones",  "country": "BE"},
    {"name": "Jane Smith",  "country": "DE"},
    {"name": "Jack Jones",  "country": "SE"},
    {"name": "Paul Smith",  "country": "IE"}
]

Does not contain one of many

The does not contain one of many operator is used to match incoming records if the value of a given field DOES NOT match any items from a provided (comma delimited) list of values. For example, consider the following payload of customer records:

[
    {"name": "John Smith",  "country": "FR"},
    {"name": "Bob Jones",  "country": "BE"},
    {"name": "Hank Smith",  "country": "AU"},
    {"name": "Jane Smith",  "country": "DE"},
    {"name": "Jack Jones",  "country": "SE"},
    {"name": "Hank Smith",  "country": "US"},
    {"name": "Paul Smith",  "country": "IE"}
]

Suppose you only want to process customer records that do NOT have US or AU in the country field (which is a string type field).

You can add a filter for the country field and select the does not contain one of many operator - then provide a comma-separated list of unacceptable country codes as the value:

The resulting payload would only include records where the country field does NOT include one of the specified values - i.e.:

[
    {"name": "John Smith",  "country": "FR"},
    {"name": "Bob Jones",  "country": "BE"},
    {"name": "Jane Smith",  "country": "DE"},
    {"name": "Jack Jones",  "country": "SE"},
    {"name": "Paul Smith",  "country": "IE"}
]

List syntax notes

When defining your 'many values' list as the value for a contains one of many or a does not contain one of many filter, there are a couple of things to keep in mind:

Format

It's important that your 'many values' are specified as a comma-separated list - so in our example:

FR,BE,DE,SE,IE

...will match as required, but:

FR BE DE SE IE

...will NOT match as required.

Spaces

Any spaces included in your 'many values' list ARE considered when matching. For example, consider our original payload:

[
    {"name": "John Smith",  "country": "FR"},
    {"name": "Bob Jones",  "country": "BE"},
    {"name": "Hank Smith",  "country": "AU"},
    {"name": "Jane Smith",  "country": "DE"},
    {"name": "Jack Jones",  "country": "SE"},
    {"name": "Hank Smith",  "country": "US"},
    {"name": "Paul Smith",  "country": "IE"}
]

Suppose we are using the contains one of many operator to match all European countries and the value field is defined as below: 

FR,BE,DE,SE, IE

Notice that the final IE list item is preceded by a space. This means that our filter will only match the IE country code if it's preceded by a space in the payload, so the output would be:

[
    {"name": "John Smith",  "country": "FR"},
    {"name": "Bob Jones",  "country": "BE"},
    {"name": "Jane Smith",  "country": "DE"},
    {"name": "Jack Jones",  "country": "SE"}
]

Our IE record (line 8 in the payload) isn't matched because there's no space before the country code in the country field.

PreviousUsing regex for string-type filtersNextFlow control shape

Last updated