what are data source filter conditions for RDS/PolarDB and how to use them - OpenSearch

Filter conditions let you control which rows from an ApsaraDB RDS or PolarDB table are synced into OpenSearch. Only rows that satisfy the condition are indexed. The condition applies to both full and incremental syncs.

Supported syntax

Field types

Filter conditions support the following SQL field types:

Category	Types
Integer	TINYINT, SMALLINT, INTEGER, BIGINT
Floating-point	FLOAT, REAL, DOUBLE, NUMERIC, DECIMAL
Date and time	TIME, TIMESTAMP

Character (string) fields and DATE/DATETIME fields are supported through specific syntax — see Example scenarios.

Comparison operators

Operator	Applies to
`<`, `>`, `<=`, `>=`	Numeric and date/time fields
`=`, `!=`, `<>`	Numeric and character fields

Logical operators

Only AND logic is supported. Separate conditions with commas (,) — not the AND keyword.

# SQL WHERE clause (not valid in OpenSearch filter conditions)
where status=1 and type=2   # Invalid
status=1 and type=2         # Invalid

# OpenSearch filter condition syntax
status=1,type=2             # Valid

Example scenarios

Filter by character field value

To sync only rows where a string field matches a specific value:

field_string='a'
field_string!='b'

String values accept single quotes ('), double quotes ("), or no quotes.

To sync rows where a string field is empty or contains the string null:

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

Filter by NULL value

To include or exclude rows where a numeric or character field is NULL:

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

IS NULL and IS NOT NULL are not supported. Use = null or != null instead.

Filter by date or datetime field

To sync only rows created after a specific date:

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

The time value must follow the format YYYY-MM-DD HH:MM:SS.

Filter by multiple conditions

To combine multiple conditions (AND logic only), separate them with commas:

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

Usage notes

Filter conditions apply only to ApsaraDB RDS and PolarDB data sources.
Use table field names from the source database, not field names from the OpenSearch application schema.
The filter condition applies to both full and incremental syncs. For example, if status=1 is configured and a document changes from status=1 to status=2 in ApsaraDB RDS, the document is not pulled to OpenSearch.
The following syntax is not supported:
- OR logic
- IS NULL / IS NOT NULL
- Functions and expressions, such as in(type,1,2,3) and length(title)>10
- LIKE and NOT LIKE
- Tautological conditions such as 1=1