OpenSearch provides built-in user-defined functions (UDFs) for field matching, index retrieval, value mapping, range checking, and score normalization.
UDF list
|
Function name |
Description |
|
Checks if a field value is in a given collection. Supports single and multiple values. |
|
|
Checks if a field value is not in a given collection. Supports single and multiple values. |
|
|
Queries the inverted index of a specified field using a given condition. |
|
|
Queries the inverted index using a given condition. Uses the original HA3 query syntax. |
|
|
Merges multiple int64 values into a single int64 value. |
|
|
Maps a continuous value to a discrete value. |
|
|
Checks if a field value is within a given interval. |
|
|
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 parameter a contains 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. You can use a vertical bar (|) to separate multiple values. The function returns true if any of the values match.
-
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 100USE_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. You can use a vertical bar (|) to separate multiple values. The function returns true if none of the values match.
-
Return value
Returns a boolean value indicating whether parameter a is not in the collection specified by parameter b.
-
Example
Use the
notcontainfunction 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 100USE_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
MATCHINDEXfunction to retrieve records where the inverted index fieldtitlecontains 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
QUERYfunction to query for items where thetitlefield 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 |Note:
The second parameter of the QUERY UDF is parsed by the HA3 query syntax parser. If the second parameter is a constant string, the surrounding single quotation marks are removed before the string is passed to the HA3 query parser. 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, such as in the HA3 query string `query='Huawei phone' AND 'OPPO phone'`, use the expression `QUERY(title, '''Huawei phone'' AND ''OPPO phone''')`. For more information about constant strings in SQL statements, 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
rangevaluefunction 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
rangefunction: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 value of input field v to the double data type. If the field is not initialized, the default score is used.
-
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 v is initialized. Otherwise, returns the value of
defaultScore. -
Example
For three documents that have a `price` field, the original content is as follows:
doc1: price=1.0
doc2: price= (not initialized)
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. |