how to use UDFs - OpenSearch - Alibaba Cloud Documentation Center

Built-in and third-party user-defined functions (UDFs) enable advanced filtering, index retrieval, distance calculation, and score normalization in queries.

Built-in UDFs

Complex type functions

Function name	Description	Version
contain	Checks if a field value is in a given collection.	ALL
notcontain	Checks if a field value is not in a given collection.	ALL
MATCHINDEX	Queries the inverted index of a specified field based on given conditions.	ALL
QUERY	Queries the inverted index based on given conditions. This function uses the native HA3 query syntax.	ALL
sphere_distance	Calculates the distance between a pair of longitude and latitude coordinates.	>=3.7.5
range		>=3.9.0
normalizescore		>=3.9.0

Examples

Retrieval example

Retrieve all content from a table.

SELECT nid, price, brand, size FROM phone ORDER BY nid LIMIT 1000

USE_TIME: 0.881, ROW_COUNT: 10

------------------------------- TABLE INFO ---------------------------
                 nid |               price |               brand |                size |
                   1 |                3599 |              Huawei |                 5.9 |
                   2 |                4388 |              Huawei |                 5.5 |
                   3 |                 899 |              Xiaomi |                   5 |
                   4 |                2999 |                OPPO |                 5.5 |
                   5 |                1299 |               Meizu |                 5.5 |
                   6 |                 169 |               Nokia |                 1.4 |
                   7 |                3599 |               Apple |                 4.7 |
                   8 |                5998 |               Apple |                 5.5 |
                   9 |                4298 |               Apple |                 4.7 |
                  10 |                5688 |             Samsung |                 5.6 |

contain

Prototype

boolean contain(int32 a, const string b)
boolean contain(int64 a, const string b)
boolean contain(string a, const string b)
boolean contain(ARRAY<int32> a, const string b)
boolean contain(ARRAY<int64> a, const string b)
boolean contain(ARRAY<string> a, const string b)

Description

Checks if the single-value or multi-value field `a` contains any of the values specified in `b`.

Parameters

Parameter `a`: An input field of the `int32`, `int64`, or `string` type. This can be a single-value or multi-value field.

Parameter `b`: A constant string expression. Use the pipe character (|) to separate multiple values. The condition is met if any of the values match.

Return value

Returns a boolean value. A value of `true` indicates that parameter `a` contains a value specified in parameter `b`.

Example

Use `contain` to retrieve all records where the value of the `nid` field is `1`, `2`, or `3`.

SELECT nid, price, brand, size FROM phone WHERE contain(nid, '1|2|3') ORDER BY nid LIMIT 100

USE_TIME: 0.059, ROW_COUNT: 3

------------------------------- TABLE INFO ---------------------------
                 nid |               price |               brand |                size |
                   1 |                3599 |              Huawei |                 5.9 |
                   2 |                4388 |              Huawei |                 5.5 |
                   3 |                 899 |              Xiaomi |                   5 |

notcontain

Prototype

boolean notcontain(int32 a, const string b)
boolean notcontain(int64 a, const string b)
boolean notcontain(string a, const string b)
boolean notcontain(ARRAY<int32> a, const string b)
boolean notcontain(ARRAY<int64> a, const string b)
boolean notcontain(ARRAY<string> a, const string b)

Description

Checks if the single-value or multi-value field `a` does not contain any of the values specified in `b`.

Parameters

Parameter `a`: An input field of the `int32`, `int64`, or `string` type. This can be a single-value or multi-value field.

Parameter `b`: A constant string expression. Use the pipe character (|) to separate multiple values. The condition is met if none of the values match.

Return value

Returns a boolean value. A value of `true` indicates that parameter `a` does not contain any value specified in parameter `b`.

Example

Use `notcontain` to retrieve all records where the value of the `nid` field is not `1`, `2`, or `3`.

SELECT nid, price, brand, size FROM phone WHERE notcontain(nid, '1|2|3') ORDER BY nid LIMIT 100

USE_TIME: 0.092, ROW_COUNT: 7

------------------------------- TABLE INFO ---------------------------
                 nid |               price |               brand |                size |
                   4 |                2999 |                OPPO |                 5.5 |
                   5 |                1299 |               Meizu |                 5.5 |
                   6 |                 169 |               Nokia |                 1.4 |
                   7 |                3599 |               Apple |                 4.7 |
                   8 |                5998 |               Apple |                 5.5 |
                   9 |                4298 |               Apple |                 4.7 |
                  10 |                5688 |             Samsung |                 5.6 |

MATCHINDEX

Prototype
Description

Checks if field `a` contains the content specified in `b`. This function is used for single-field index retrieval.

This function is used only in the `WHERE` clause to accelerate retrieval on an index table using an inverted index.

Parameters

Parameter `a`: A constant string that specifies the field for which an inverted index is created.

Parameter `b`: A constant string that contains the content to search for.

Parameter `b` can be used as a whole string for queries or for spatial vector indexes.

Parameter `c`: An optional parameter that specifies settings such as tokenizers and stop words for tokenizing the content in `b`.

The settings are configured using the following keywords. For more information, see the appendix.

Use colons (`:`) and commas (`,`) to separate items.

global_analyzer
specific_index_analyzer
no_token_indexes
tokenize_query
remove_stopwords
default_op

Return value

Returns a boolean value. A value of `true` indicates that field `a` contains the content specified in parameter `b`.

Example

Use `MATCHINDEX` to retrieve records where the inverted index field `title` contains the keyword "lens".

SELECT nid, brand FROM phone WHERE MATCHINDEX('title', 'lens')

------------------------------- TABLE INFO ---------------------------
                 nid |               brand |
                   1 |              Huawei |

QUERY

Prototype

boolean QUERY(const string a, const string b)
boolean QUERY(const string a, const string b, const string c)

Description

Checks if field `a` contains the content specified in `b`. This function provides automatic tokenization and retrieval capabilities.

This function supports the native query syntax of the HA3 engine, the ha3_query clause, in SQL mode.

This function is used only in the `WHERE` clause to accelerate retrieval on an index table using an inverted index.

Parameters

Parameter `a`: A constant string that specifies the default index field. For more information, see default_index.

Parameter `b`: A constant string that contains the content to search for.

This parameter is concatenated into the query for parsing and can be used for range indexes.

Parameter `c`: An optional parameter that specifies settings such as tokenizers and stop words for tokenizing the content in `b`.

Use :, to separate each item.

The settings are configured using the following keywords:

global_analyzer
specific_index_analyzer
no_token_indexes
tokenize_query
remove_stopwords
default_op
Return value

Returns a boolean value. A value of `true` indicates that field `a` contains the content specified in parameter `b`.

Example
You can use `QUERY` to find items where the `title` field contains "Huawei phone".

SELECT nid, price, brand, size FROM phone WHERE QUERY('title', 'Huawei phone')

USE_TIME: 0.034, ROW_COUNT: 1

------------------------------- TABLE INFO ---------------------------
                 nid |               price |               brand |                size |
                   2 |                4388 |              Huawei |                 5.5 |

Use combined conditions to retrieve items where the `title` field contains "Huawei phone" or "OPPO phone".

SELECT nid, price, brand, size FROM phone 
   WHERE QUERY('title', 'Huawei phone OR OPPO phone')

USE_TIME: 0.03, ROW_COUNT: 2

------------------------------- TABLE INFO ---------------------------
                 nid |               price |               brand |                size |
                   2 |                4388 |              Huawei |                 5.5 |
                   4 |                2999 |                OPPO |                 5.5 |

To specify usages such as default_op, use : as the separator.

SELECT nid, price, brand, size FROM phone 
   WHERE QUERY('title', 'Huawei phone OPPO phone', 'default_op:OR,remove_stopwords:true')

Note:

The second parameter of the `QUERY` UDF is parsed by the HA3 query syntax parser. For more information about the HA3 query syntax, see the query clause topic. If the second parameter is a constant string, the surrounding single quotation marks are removed before the string is passed to the HA3 query. For example, `QUERY(title, 'Huawei phone OPPO phone')` is equivalent to the HA3 query `query=Huawei phone OPPO phone`. To include quotation marks in the query description, such as in the HA3 query string `query='Huawei phone' AND 'OPPO phone'`, the `QUERY` UDF must be written as `QUERY(title, '''Huawei phone'' AND ''OPPO phone''')`.

Common errors

Error type	Incorrect format	Correct format
Syntax error, no results returned	QUERY('pidvid','123:456')	QUERY('pidvid','"123:456"')

sphere_distance

Prototype

double sphere_distance(LOCATION point, double longitude, double latitude)

Description

Calculates the distance between the specified longitude and latitude and the location of the retrieved document.

Parameters

Parameter `point`: A column of the `LOCATION` data type that contains single values.

Parameter `longitude`: Specifies the longitude.

`latitude`: Specifies the latitude.

Return value

Returns the spherical distance on the Earth's surface between the two points.

Example:

The `sphere_distance` function requires the `geo` field to be a single-value `LOCATION` field with a forward index.

SELECT sphere_distance(geo,127.0,30.0) FROM phone

range

Prototypes

boolean range(int8 v, const string rangeDesc)
boolean range(uint8 v, const string rangeDesc)
boolean range(int16 v, const string rangeDesc)
boolean range(uint16 v, const string rangeDesc)
boolean range(int32 v, const string rangeDesc)
boolean range(uint32 v, const string rangeDesc)
boolean range(int64 v, const string rangeDesc)
boolean range(uint64 v, const string rangeDesc)
boolean range(float v, const string rangeDesc)
boolean range(double v, const string rangeDesc)

Description

Determines whether the value of a forward index field is within a specified interval.

Parameters

Parameter `v`: The field. The value must be a single-value numeric type.

`rangeDesc` parameter: A constant that describes a numeric range. The value can be an open, closed, or half-open interval.

Return values

Checks whether `v` is within the range specified by `rangeDesc`. The following table lists the supported formats and their return values.

Example call	Return value
range(v, "[0, 100]")	0<=v<=100
range(v, "(0, 100)")	0<v<100
range(v, "[0, 100)")	0<=v<100
range(v, "(0, 100]")	0<v<=100
range(v, "(0,)") range(v, "(0,]")	0<v
range(v, "[0,)") range(v, "[0,]")	0<=v
range(v, "(,100)") range(v, "[,100)")	v<100
range(v, "(,100]") range(v, "[,100]")	v<=100
range(v, "(,)") range(v, "[,]") range(v, "[,)") range(v, "(,]")	true

In addition, you can prepend an exclamation mark (!) to `rangeDesc` to negate the specified interval.

Example

Using range

SELECT nid FROM phone where range(price,"(127.0,30.0)")
SELECT nid FROM phone where range(price,"!(127.0,30.0)")

normalizescore

Prototypes

double normalizescore(int8 v, const string defaultScore)
double normalizescore(uint8 v, const string defaultScore)
double normalizescore(int16 v, const string defaultScore)
double normalizescore(uint16 v, const string defaultScore)
double normalizescore(int32 v, const string defaultScore)
double normalizescore(uint32 v, const string defaultScore)
double normalizescore(int64 v, const string defaultScore)
double normalizescore(uint64 v, const string defaultScore)
double normalizescore(float v, const string defaultScore)
double normalizescore(double v, const string defaultScore)

Description

Normalizes the input field `v` and converts it to the `double` type, returning the original value if the field is initialized, or the `defaultScore` value otherwise.

Parameters

Parameter `v`: The field. The value must be a single-value numeric type.

The `defaultScore` parameter is a constant string that is convertible to a `double` value.

Return Value

If `v` is initialized, its original value is returned. Otherwise, the value of `defaultScore` is returned.

Example

For three documents that have a price field, the original content is as follows:
doc1: price=1.0
doc2: price= (uninitialized)
doc3: price=2.0

Execute the following statement:
select normalizescore(price, "1000.0") as normalized_score from phone

USE_TIME: 32.141ms, ROW_COUNT: 2

------------------------------- TABLE INFO ---------------------------
          normalized_score(double) |
                            1.0    |
                            1000.0 |
                            2.0    |

Appendix

Tokenizer Description

The following parameters configure tokenization behavior for the `MATCHINDEX` and `QUERY` functions.

global_analyzer:

Specifies a global tokenizer for the query, which overwrites the tokenizer defined in the schema. The specified tokenizer must be configured in the `analyzer.json` file.

specific_index_analyzer

Specifies a tokenizer for an index to use at query time. This tokenizer overwrites the global and schema tokenizers.

no_token_indexes

Specifies the indexes in a query that are exempt from tokenization. Other processes, such as normalization and stop word removal, are still applied.

Use semicolons (`;`) to separate indexes.

tokenize_query

`true` or `false`. Specifies whether to tokenize the query. This parameter is ignored if a tokenizer is specified in the `QUERY` UDF.

The default value is `true`.

remove_stopwords

`true` or `false`. Specifies whether to remove the stop words that are configured in the tokenizer.

The default value is `true`.

default_op

Specifies the default operator (`AND` or `OR`) for connecting tokens within a tokenized query. The default behavior is configured in biz.

Third-party UDFs

Complex type functions

Function name	Description	Version
type cast	Type casting	ALL