Updated Filter Operation Scenarios

In this guide, we will be discussing various rules and scenarios in which Filter operation can be used and how the users can add them to their integration bridge.

Rules for Using Filter Operations

1. When the Target is an API

Single Filter Condition:

If a single filter condition is used to filter data and the filtered data is sent to the API as the request body (dropping unfiltered data), Data Aggregation operation is not required.

Multiple Filter Conditions:

If multiple filter conditions are used, and all the filtered data needs to be sent to the API as the request body:

After applying the first Filter condition, use the API operation to send the filtered data.

Use the Filter End operation to close the first filter flow.

Apply the second Filter condition and send the resulting data to the API operation or Target.

2. When the Target is Datalake

General Rule:

Whether using one or more filter conditions, always:

After applying the first Filter condition, use the Datalake operation to send the filtered data.

Use the Filter End operation to close the first filter flow.

Apply the second Filter condition and send the resulting data to the Datalake operation or Target. (if want to use second filter)

Dropping Unfiltered Data:

After applying the Filter operation, send the filtered data to the Datalake using the Datalake operation or target.

Retaining All Data:

Use the first Filter operation to filter the data, then send the filtered data to the Datalake using the Datalake operation. After the Filter End operation, apply another Filter operation using the opposite condition of the first filter and send the resulting data to the Datalake using the Datalake operation or target. This ensures both filtered and unfiltered data are sent to the Database.

3.When the Target is a Database

General Rule:

For one or more filter conditions, always:

Use Append ops to add a dummy key for aggregation

Aggregate the data using the Data Aggregation operation.

Apply the SingleLine to Tuple operation.

Send the data to the Database.

Dropping Unfiltered Data:

After the filter operation, append a dummy key to serve as a group-by key in the Data Aggregation operation. This ensures only filtered data is sent to the Database.

Retaining All Data:

Apply a Filter operation and send the filtered data to the Database after grouping it using Data Aggregation and converting it into a tuple. After the Filter End operation, apply another Filter using the opposite condition and send the remaining data to the Database in the same way. This ensures both filtered and unfiltered data are sent to the Database.

4.When Ending a Filter Condition

Always use the Filter End operation once data transformation and data transfer are completed.
The Filter End operation finalizes the filter condition. After Filter End, no further filtering is applied, and the original data flow is restored.

To show these scenarios, a dataset of 12 records has been used in multiple ways. The dataset consists of records:

Scenario 1: With Target as Datalake and 1 filter condition

In this scenario, one filter condition will be applied on the data coming from the source and then the filtered record will be sent to Datalake.

Operations Used:

To send the records to Datalake, we apply SingleLine to Multiline operation as the data coming from source is a SingleLine JSON data.
Next, the filter condition is applied as per the requirement (Here, Filter condition applied is ID not equal to 2, which means records associated with ID 2 should not be sent to target) and now send the records to the target.

Example: Data in Source

[
{ 
  "id": 1, 
  "email": "george.bluth@reqres.in", 
  "first_name": "George", 
  "last_name": "Bluth" 
}, 
{ 
  "id": 2, 
  "email": "janet.weaver@reqres.in", 
  "first_name": "Janet", 
  "last_name": "Weaver" 
}, 
{ 
  "id": 3, 
  "email": "emma.wong@reqres.in", 
  "first_name": "Emma", 
  "last_name": "Wong" 
}
]

 Filter Condition: 

 data['id'] !=2

Output in Datalake:

id	email	first_name	last_name
1	george.bluth@reqres.in	George	Bluth
3	emma.wong@reqres.in	Emma	Wong

Scenario 2: With Target as API and 1 filter condition

In this scenario, one filter condition will be applied to the data coming from the source and then the filtered record will be sent to the target which is API.

To send the records to the target (API), we apply SingleLine To Multiline operation as the data coming from source is a SingleLine JSON data.
Next, the filter condition is applied as per the requirement (Here, Filter condition applied is ID not equal to 3, which means records associated with ID 3 should not be sent to target) and now send the records to the target.

Note: Further operations can be used in the integration bridge depending on the Target and data type.

Example: Data in Source

[
{ 
      "id": 1, 
      "email": "george.bluth@reqres.in", 
      "first_name": "George", 
      "last_name": "Bluth" 
}, 
{ 
      "id": 2, 
      "email": "janet.weaver@reqres.in", 
      "first_name": "Janet", 
      "last_name": "Weaver" 
}, 
{ 

      "id": 3, 
      "email": "emma.wong@reqres.in", 
      "first_name": "Emma", 
      "last_name": "Wong" 
}
]

Filter Condition:

data['id'] !=3

Output in Target:

{ 
"id": 1, 
"email": "george.bluth@reqres.in", 
"first_name": "George", 
"last_name": "Bluth" 

}, 
{ 
"id": 2, 
"email": "janet.weaver@reqres.in", 
"first_name": "Janet", 
"last_name": "Weaver" 
}

Scenario 3: With Target as Database and 1 filter condition

In this scenario, one filter condition will be applied to the data coming from the source and then the filtered record will be sent to the Database.

Operations Used:

To send the records to database, we apply SingleLine to Multiline operation on the data coming from source, as it is a SingleLine JSON data.
Next, the filter condition is applied as per the requirement (Here, Filter condition applied is ID not equal to 1, which means records associated with ID 1 should not be sent to target).
Once the filter operation is applied, create a new column using Append, which will help to aggregate data.
To send data to the Database, a Tuple must be created. Before creating the Tuple, aggregate all required columns using the Data Aggregation operation, then use the key created by the Append operation and store the aggregated data under the Tuple key.
Use SingleLine to Tuple and send the records to the Database.

Example: Data in Source

[
{ 
"id": 1, 
"email": "george.bluth@reqres.in", 
"first_name": "George", 
"last_name": "Bluth" 
}, 

{ 
"id": 2, 
"email": "janet.weaver@reqres.in", 
"first_name": "Janet", 
"last_name": "Weaver" 
}, 

{ 
"id": 3, 
"email": "emma.wong@reqres.in", 
"first_name": "Emma", 
"last_name": "Wong" 
}
]

Filter Condition: 

data['id'] !=1

Output in Database:

id	email	first_name	last_name
2	janet.weaver@reqres.in	Janet	Weaver
3	emma.wong@reqres.in	Emma	Wong

Scenario 4: With Target as Datalake but more than 1 filter condition

In this scenario, more than one filter condition will be applied to the data coming from the source and then the filtered record will be sent to the Datalake.

Operations Used:

To send the records to Datalake, we will use the operation SingleLine to Multiline as your records are in SingleLine JSON data.
Apply the Filter condition, send the filtered records to the Datalake, and end the filter using the Filter End operation.
Apply another Filter condition and send the resulting data to the Datalake (using the same or a different index).

Example: Data in Source

[
{ 
"id": 1, 
"email": "george.bluth@reqres.in", 
"first_name": "George", 
"last_name": "Bluth" 
}, 
{ 
"id": 2, 
"email": "janet.weaver@reqres.in", 
"first_name": "Janet", 
"last_name": "Weaver" 
}, 
{ 
"id": 4, 
"email": "eve.holt@reqres.in", 
"first_name": "Eve", 
"last_name": "Holt" 
}, 
{ 
"id": 5, 
"email": "charles.morris@reqres.in", 
"first_name": "Charles", 
"last_name": "Morris"
}
]

Condition 1

Filter Condition:

data['id’] == 1

Condition 2

Filter Condition:

data['id’] == 2

Output in Datalake:

id	email	first_name	last_name
1	george.bluth@reqres.in	George	Bluth
2	janet.weaver@reqres.in	Janet	Weaver

Scenario 5: With Target as Database but more than 1 filter condition

In this scenario, more than one filter condition will be applied to the data coming from the source and then the filtered record will be sent to the Database.

Operations Used:

To send the records to Database, we will use the operation SingleLine to Multiline as your records are in SingleLine JSON data.
Apply the filter conditions along with the Append operation to add a one dummy key-value pair as this will be used to aggregate filtered records
Now, aggregate filtered records using the dummy key.
To send the records to the Database, create a tuple using SingleLine to Tuple and send the records to the target and end the filter using the Filter End operation.
Now, again apply filter condition along with the Append operation to create a one dummy key-value pair.
Now, aggregate all the records using the newly created dummy key and create tuple using SingleLine to Tuple and send the records to the target (same or different table).

Example: Data in Source

[
{ 
"id": 1, 
"email": "george.bluth@reqres.in", 
"first_name": "George", 
"last_name": "Bluth" 
}, 
{ 
"id": 2, 
"email": "janet.weaver@reqres.in", 
"first_name": "Janet", 
"last_name": "Weaver" 
}, 
{ 
"id": 4, 
"email": "eve.holt@reqres.in", 
"first_name": "Eve", 
"last_name": "Holt" 
}, 
{ 
"id": 5, 
"email": "charles.morris@reqres.in", 
"first_name": "Charles", 
"last_name": "Morris" 
}
]

Condition 1

Filter Condition:

data['id’] == 2

Condition 2

Filter Condition:

data['id’] == 4

Output in Database:

id	email	first_name	last_name
2	janet.weaver@reqres.in	Janet	Weaver
4	eve.holt@reqres.in	Eve	Holt