OpenSearch:Configure conditions to filter fields in ApsaraDB RDS and PolarDB data sources

• 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: all values including null

1. Filter fields of the character type: field_string='a' or field_string! ='b' Note that you can use single quotation marks (') or double quotation marks ("), or do not use quotation marks. The following examples show the conditions that can be used to filter empty fields and fields with a null value:

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

2. Filter fields with a null value: The following examples show the conditions that can be used to filter fields of the numeric type or the character type with a null value.

# The following filter conditions are valid:
field = null 
field = NULL
field != null
field != NULL
field <> null
field <> NULL

3. Filter fields of the DATE or DATETIME type: createtime>'2021-02-05 00:00:00'

4. Filter fields based on multiple conditions: status=1,literal_id='abcd',createtime>'2021-02-05 00:00:00'

• You can filter only the fields from ApsaraDB RDS and PolarDB data sources.

• When you configure conditions to filter the data of data sources, you must use table fields in source databases instead of fields in the schemas of OpenSearch applications.

• After a filter condition takes effect, only data entries that meet the condition are pulled. The filter condition applies to full and incremental data. For example, after the filter condition status=1 is configured, a document status=1 in ApsaraDB RDS is changed to status=2. In this case, this document is not pulled to OpenSearch.

• Only the AND logical operation is supported. Commas (,) are used to implement the AND logical operation. The OR logical operation is not supported.

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

# Configure filtering of data sources in OpenSearch.
where status=1 and type=2 # Invalid.
status=1 and type=2 # Invalid.
status=1,type=2 # Valid.

• If you need to filter fields with a null value or a non-null value, you cannot use is null or is not null. For more information about the valid syntax, see the conditions for filtering fields with a null value in the "Examples" section of this topic.

• If you need 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'.

• Functions and expressions, such as in(type,1,2,3) and length(title)>10, are not supported.

• like and not like, such as like '%aaa' and not like '%bbb', are not supported.

• Identical equations, such as 1=1, are not supported.

• If primary and secondary tables are configured for a data source, we recommend that you filter data in both the primary and secondary tables. This prevents data in the secondary table from occupying excessive storage space.