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

This topic provides instructions for using user-defined functions (UDFs).

UDF list

Function name	Description
contain	Checks if a field value is in a given collection. Supports single and multiple values.
notcontain	Checks if a field value is not in a given collection. Supports single and multiple values.
MATCHINDEX	Queries the inverted index of a specified field using a given condition.
QUERY	Queries the inverted index using a given condition. Uses the original HA3 query syntax.
hashcombine	Merges multiple int64 values into a single int64 value.
rangevalue	Maps field values based on ranges.
range	Checks if a field value is within a given interval.
normalizescore	Normalizes a field value.

Retrieval examples

Retrieve all table content

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 a single-value or multi-value parameter a contains the content specified in b.

Parameters
Parameter a: The input parameter. Supported data types are INT, LITERAL, INT_ARRAY, and LITERAL_ARRAY.
Parameter b: A constant string expression separated by |. Matching any item is sufficient.
Return value
Returns a boolean value indicating whether parameter a contains the collection specified 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 parameter a does not contain the content specified in parameter b.
Parameters
Parameter a: The input parameter. Supported data types are INT, LITERAL, INT_ARRAY, and LITERAL_ARRAY.
Parameter b: A constant string expression separated by |. None of the items can match.
Return value
Returns a boolean value indicating whether parameter a is not in the collection specified by parameter b.

Example

Use the notcontain function to retrieve all records where the value of the `nid` field 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 specified in b. This function is used for single-field index retrieval.
Used only for inverted index acceleration and optimization during the retrieval phase of an index table. Can be used in the WHERE clause.
Parameters
Parameter a: A constant string specifying the field for which an inverted index is created for optimization.
Parameter b: A constant string specifying the content to search for.

The value of Parameter b is searched as a single string.

Return value
Returns a boolean value indicating whether field a contains the content specified in parameter b.

Example

Use the MATCHINDEX function to retrieve records where the inverted index field title contains the keyword "camera".

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

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

QUERY

Prototype

boolean QUERY(const string a, const string b)

Description
Checks whether field a contains the content specified in b. This function provides automatic tokenization and retrieval capabilities.
This function lets you use native HA3 query syntax in SQL mode.
Used only for inverted index acceleration and optimization during the retrieval phase of an index table. Can be used in the WHERE clause.
Parameters
Parameter a: A constant string specifying the default index field.
Parameter b: A string constant providing the query expression.

This parameter is parsed by the query parser and supports range indexes.

Return value
Returns a boolean value indicating whether field a contains the content specified in parameter b.
Example
Use the QUERY function to query for 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 records 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:
The second parameter of the QUERY UDF is parsed by the HA3 query syntax parser. If it is a constant string, remove the surrounding single quotation marks when passing it to HA3 query syntax. For example, QUERY(title, 'Huawei phone OPPO phone') is equivalent to query=Huawei phone OPPO phone. To include quotation marks within the query, as in query='Huawei phone' AND 'OPPO phone', use QUERY(title, '''Huawei phone'' AND ''OPPO phone'''). For notes on constant strings in SQL, see the 'Constant strings' section in [Limits].
Common errors:

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

rangevalue

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

Maps a continuous value to a discrete value.

Parameters
Parameter v: The column containing the continuous values.
Parameter desc: The mapping rule.
Return value
The mapped discrete value.
Example
Use the rangevalue function to map the values of the `price` field. 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. Other values are not mapped and retain their original values.
```
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 falls within a specified interval.

Parameters
Parameter v: The field, which supports single-value numeric types.
Parameter rangeDesc: A constant specifying the numeric range. Open, closed, and half-open intervals are supported.
Return value
Returns a boolean value indicating whether v falls within the range specified by rangeDesc. The following table lists the supported expressions 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

Note: The `rangeDesc` parameter also supports using an exclamation point (!) at the beginning of the string to negate the specified interval.

Example

Use the 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 SQL keyword 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 double. If v is initialized, its original value is returned. Otherwise, defaultScore is returned.

Parameters
Parameter v: The field, which supports single-value numeric types.
Parameter defaultScore: A constant specifying the default score. Must be a string that can be converted to a valid double value.
Return value
Returns the original value of v if it is initialized, or defaultScore otherwise.
Example

For three documents with a price field, the original values are:
doc1: price=1.0
doc2: price= (not initialized)
doc3: price=2.0

Run:
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.   |