How to use user-defined functions (UDFs) - OpenSearch - Alibaba Cloud Documentation Center

Usage notes for user-defined functions (UDFs).

UDF list

Function	Description
contain	Checks whether a field value is included in a specified set. Supports single-value and multi-value fields.
notcontain	Checks whether a field value is excluded from a specified set. Supports single-value and multi-value fields.
MATCHINDEX	Queries the inverted index of a specified field based on the specified conditions.
QUERY	Queries inverted indexes based on the specified conditions, using the original HA3 query syntax.
hashcombine	Merges multiple INT64 values into a single INT64 value.
rangevalue	Maps field values by range.
range	Checks whether a field value is included in a specified interval.
normalizescore	Normalizes field values.

Sample queries

Query full data in the 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(INT a, const string b)
boolean contain(LITERAL a, const string b)
boolean contain(INT_ARRAY a, const string b)
boolean contain(LITERAL_ARRAY a, const string b)

Description

Checks whether the single-value or multi-value field a contains the content described in b.

Parameters
Parameter a: An input of the INT, LITERAL, INT_ARRAY, or LITERAL_ARRAY type.
Parameter b: A constant string expression, separated by |, which indicates that matching any one item is sufficient.
Return value
A boolean value that indicates whether parameter a contains the set described in parameter b.
Example

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(INT a, const string b)
boolean notcontain(LITERAL a, const string b)
boolean notcontain(INT_ARRAY a, const string b)
boolean notcontain(LITERAL_ARRAY a, const string b)

Description
Checks whether the single-value or multi-value field a is not in the content described in b.
Parameters
Parameter a: An input of the INT, LITERAL, INT_ARRAY, or LITERAL_ARRAY type.
Parameter b: A constant string expression, separated by |, which indicates that none of the items can be matched.
Return value
A boolean value that indicates whether parameter a is not in the set described in parameter b.

Example

Use notcontain to retrieve all records whose nid field value is not in the range [1,2,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

boolean MATCHINDEX(const string a, const string b)

Description
Checks whether field a contains the content described in b, for single-field index recall.
Used only for inverted index acceleration optimization during the index table recall phase, in WHERE conditions.
Parameters
Parameter a: An input of the constant string type, corresponding to the field for which the inverted index optimization is built.
Parameter b: An input of the constant string type, whose content is the string description.

Parameter b can be queried as a single string.

Return value
A boolean value that indicates whether field a contains the content described in parameter b.

Example

Use MATCHINDEX to retrieve records whose 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)

Description
Checks whether field a contains the content described in b, providing automatic tokenization and retrieval capabilities.
Used to support the HA3 engine's native query syntax (HA3 query syntax) in SQL mode.
Used only for inverted index acceleration optimization during the index table recall phase, in WHERE conditions.
Parameters
Parameter a: An input of the constant string type, which is used as the default index field.
Parameter b: An input of the constant string type, whose content is the string description.

It is appended to the query parsing and can be used for range indexes.

Return value
A boolean value that indicates whether field a contains the content described in parameter b.
Example
Use QUERY to query entries whose title 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 a combined condition to retrieve entries whose `title` 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 |

Note:
Parameter 2 of the QUERY UDF is parsed by the HA3 query syntax parser. In HA3 query syntax, when parameter 2 is a constant string, note that the leading and trailing single quotation marks must be removed when it is passed into the HA3 query. For example, QUERY(title, 'Huawei phone OPPO phone') is equivalent to the HA3 query query=Huawei phone OPPO phone. If you need to add quotation marks inside the query description, such as the HA3 query string query='Huawei phone' AND 'OPPO phone', the equivalent form in the QUERY UDF is QUERY(title, '''Huawei phone'' AND ''OPPO phone'''). For usage notes about constant strings in SQL descriptions, see the 'Constant strings' section in [Limits].
Common mistakes:

Error type	Incorrect form	Correct form
Syntax error, query returns no results	QUERY('pidvid','123:456')	QUERY('pidvid','"123:456"')

rangevalue

Prototype
```
float rangevalue(float v, string desc)
```
Description

Maps continuous values to discrete values.

Parameters
Parameter v: A column of continuous values.
Parameter desc: The mapping rule.
Return value
The discrete value after mapping.
Example
Use rangevalue to map the price value: values less than or equal to 1000 are mapped to 1.0, values greater than 1000 and less than or equal to 5000 are mapped to 2.0, and other values retain the original price.
```
SELECT rangevalue(price,'(,1000]:1.0;(1000,5000]:2.0') FROM phone;
```

range

Prototype

boolean range(INT v, const string rangeDesc)
boolean range(FLOAT v, const string rangeDesc)
boolean range(DOUBLE v, const string rangeDesc)

Description

Checks whether a forward index field value is within an interval.

Parameters
Parameter v: A field. Single-value numeric types are supported.
Parameter rangeDesc: A constant that describes the numeric range interval. Open, closed, and half-open intervals are supported.
Return value
Checks whether v is within the range described by rangeDesc. The following table lists the supported notations and their return values.

Call notation example	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

Note: rangeDesc also supports the "!" symbol at the beginning of the line to negate the intended interval.

Example

Use range

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

Note: range is a reserved keyword in SQL and must be escaped.

normalizescore

Prototype

double normalizescore(INT v, const double defaultScore)
double normalizescore(FLOAT v, const double defaultScore)
double normalizescore(DOUBLE v, const double defaultScore)

Description

Normalizes the input field v and converts it to the double type. If it is initialized, the original value is returned; if it is not initialized, defaultScore is returned.

Parameters
Parameter v: A field. Single-value numeric types are supported.
Parameter defaultScore: A constant description that must be a string convertible to a valid double type.
Return value
If v is initialized, the original value is returned; if it is not initialized, defaultScore is returned.
Example

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

Execute
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.   |