how to use filter conditions for RDS/PolarDB data sources - OpenSearch

Introduction

Supported field types: TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, REAL, DOUBLE, NUMERIC, DECIMAL, TIME, and TIMESTAMP
Supported comparison operators: <, >, <=, >=, =, !=, <>. Note: =, !=, and <> can be used to filter fields of the numeric and character types.
Supported values: NULL or any other value

Examples

Filter by character type

For example, field_string='a' or field_string!='b'.

Note: You can use single quotation marks ('), double quotation marks ("), or no quotation marks for character values.

field = ''
field != ''
field <> ''
field = 'null'
field = 'NULL'
field != 'null'
field != 'NULL'
field <> 'null'
field <> 'NULL'

Filter by NULL value

Note: This applies to both numeric and character fields.

# All of the following syntaxes are valid
field = null 
field = NULL
field != null
field != NULL
field <> null
field <> NULL

Filter by date or datetime type

createtime>'2021-02-05 00:00:00'

Filter by multiple conditions

status=1,literal_id='abcd',createtime>'2021-02-05 00:00:00'

Usage notes

The data source filtering feature is available for RDS and PolarDB data sources.
For the filter condition, you must use the field names from the database table, not the field names from the OpenSearch application schema.
When a filter condition is active, only records that meet the condition are pulled. This applies to both full and incremental data.

For example, if you configure the filter condition status=1 and the status of a document in RDS is later changed from 1 to 2, that document is deleted from OpenSearch.

Only the AND logical operator is supported. Use a comma (,) to represent the AND operator. The OR operator is not supported.

## The WHERE clause in an SQL statement:
where status=1 and type=2

# The configuration in the OpenSearch data source filter:
where status=1 and type=2 # Incorrect syntax
status=1 and type=2 # Incorrect syntax
status=1,type=2 # Correct syntax

To check for null values, do not use `is null` or `is not null`. For the correct syntax, see the "Filtering by NULL value" example.
To filter a field of the DATE or DATETIME type, such as the field named createtime, in a database table, the time in the filter condition must be in the required format. Example: createtime>'2018-03-01 00:00:00'.
Complex operations such as functions and expressions are not supported.

For example: in(type,1,2,3) or length(title)>10
The `like` and `not like` operators are not supported.

For example: like '%aaa' or not like '%bbb'
Identity equations are not supported.

For example: 1=1
If a data source has a primary table and a secondary table, you must apply filter conditions to both tables. This prevents the secondary table from consuming unnecessary storage space.