Using list or list length to filter JSON arrays

To view this content in our official product documentation, click here.

Introduction

There may be times when you need to define filters based on items in an array field. You can define list type filters to match records based on array field values and/or list length filters to match records based on the number of items in an array.

The list filter type

The list filter type matches records based on array field values. Here, you choose which array field (within incoming payloads) to filter, followed by:

• An operator , to determine how given values should be compared for matching
• The expected value type of values received in this array
• Required list items (i.e. array values to be used for matching)

For example:

 

Value types, operators & options

Like any other filter, a list type filter is defined using value types and operators, summarised below:

Operator	Value type	Other available options
Contains	string, integer, float	-
Does not contain	string, integer, float	-
Equals	string, integer, float	Match order?
Not equals	string, integer, float	Match order?

If the exact order of array field values is important for matching, you should use the equals or not equals operator and toggle the match order? option to on.

When multiple list items are defined, you can set the logic to:

• AND (so a record must match all defined values)
• OR (so a record can match one or more defined values)

Examples

Consider the JSON payload below:

[
  {
    "name": "John",
    "fruit": ["apples", "pears", "oranges"]
  },
  {
    "name": "George",
    "fruit": ["pears", "oranges", "apples"]
  },
  {
    "name": "Ringo",
    "fruit": ["raspberries", "pears", "grapes"]
  },
  {
    "name": "Paul",
    "fruit": ["apples"]
  }
]

Example 1

Match records where the fruit field value contains apples OR pears anywhere in the array. The input would be:

 

Output would be:

[
    {"name":"John","fruit":["apples","pears","oranges"]},
    {"name":"George","fruit":["pears","oranges","apples"]},
    {"name":"Ringo","fruit":["raspberries","pears","grapes"]},
    {"name":"Paul","fruit":["apples"]}
]

Example 2

Match records where the fruit field value contains exactly apples AND pears AND oranges in this sequence. The input would be:

 

Output would be:

[{"name":"John","fruit":["apples","pears","oranges"]}]

Example 3

Match records where the fruit field value is equal to apples AND pears AND oranges in ANY sequence. The input would be:

 

Output would be:

[
    {"name":"John","fruit":["apples","pears","oranges"]}, 
    {"name":"George","fruit":["pears","oranges","apples"]}
]

The list length filter type

The list length filter type matches records based on the number of values in an array field. Here, you choose which array field (within incoming payloads) to filter, followed by:

• An operator , to determine how given values should be compared for matching
• Value (the number of array items to match)

For example:

 

Value types, operators & options

The list length filter type expects a numeric value - as such, the following operators are available:

Operator

Contains

Does not contain

Equals

Not equals

Examples

Consider the JSON payload below:

[
  {
    "name": "John",
    "fruit": ["apples", "pears", "oranges"]
  },
  {
    "name": "George",
    "fruit": ["pears", "oranges", "apples"]
  },
  {
    "name": "Ringo",
    "fruit": ["raspberries", "pears", "grapes"]
  },
  {
    "name": "Paul",
    "fruit": ["apples"]
  }
]

Example 1

Match records where the fruit field value is GREATER THAN 2 . The input would be: