Syntax and examples of SQL user-defined functions - OpenSearch

This topic describes user-defined functions (UDFs).

UDFs

Function	Description
contain	Returns true if a specific value in the specified field is included in the specified value set. The field can be a single-value field or multi-value field.
notcontain	Returns true if a specific value in the specified field is excluded from the specified value set. The field can be a single-value field or multi-value field.
MATCHINDEX	Returns true if the inverted index of the specified field meets the specified conditions.
QUERY	Returns inverted indexes based on the specified conditions. The syntax of this function is the same as the syntax of the query clause supported by HA3.
hashcombine	Merges values in multiple fields of the INT64 type into one value of the INT64 type.
rangevalue	Converts values in the specified ranges in a field to values of the specified data type.
range	Returns true if a specific value in the specified field is included in the specified range.
normalizescore	Normalizes values in fields.

Sample queries

Query full data in the phone 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

You can use this function to determine whether values in the a field are included in the b value set. The a field can be a single-value field or multi-value field.

Parameters:
a: specifies a field in the data table. The field that you specify can be a field of the INT, LITERAL, INT_ARRAY, or LITERAL_ARRAY type.
b: specifies a constant string. Separate the specified constants with vertical bars (|). If a value matches one of the specified constants, this function returns true.
Return values
This function returns values of the BOOLEAN type. A value of true indicates that the value in the a field is included in the b value set. A value of false indicates that the value in the a field is excluded from the b value set.
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
You can use this function to determine whether values in the a field are not included in the b value set.
Parameters:
a: specifies a field in the data table. The field that you specify can be a field of the INT, LITERAL, INT_ARRAY, or LITERAL_ARRAY type.
b: specifies a constant string. Separate the specified constants with vertical bars (|). If a value matches one of the specified constants, this function returns false.
Return values
This function returns values of the BOOLEAN type. A value of true indicates that the value in the a field is not included in the b value set. A value of false indicates that the value in the a field is included in the b value set.

Example

In the following statement, the notcontain function is used to query data entries in the rows in which the values of the nid field are 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

boolean MATCHINDEX(const string a, const string b)

Description
You can use this function to determine whether a value in the a field includes the value of the b parameter. You can use this function to obtain the index of a field.
You can use this function in only the WHERE clause to optimize inverted indexing in the index retrieval phase.
Parameters:
a: specifies a field of the STRING type. The system creates an inverted index based on the specified field to optimize the query.
b: specifies a keyword of the STRING type.

The value of the b parameter can be used as a search query string.

Return values
This function returns values of the BOOLEAN type. A value of true indicates that the value in the a field includes the value of the b parameter. A value of false indicates that the value in the a field does not include the value of the b parameter.

Example

In the following statement, the MATCHINDEX function is used to query data entries in the rows in which the values of the title field include the Lens keyword.

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
You can use this function to determine whether a value in the a field includes the value of the b parameter. The system automatically converts the specified keyword to terms and performs a query.
This function supports the syntax of the query clause in SQL statements supported by HA3.
You can use this function in only the WHERE clause to optimize inverted indexing in the index retrieval phase.
Parameters:
a: specifies a field of the STRING type. The system automatically uses the specified field as the value of the default_index parameter in the config clause.
b: specifies a keyword of the STRING type.

The system includes the value of the b parameter in the statement after the system optimizes the statement and queries data from the specified ranges.

Return values
This function returns values of the BOOLEAN type. A value of true indicates that the value in the a field includes the value of the b parameter. A value of false indicates that the value in the a field does not include the value of the b parameter.
Examples

In the following statement, the QUERY function is used to query data entries in the rows in which the values of the title field include the Huawei keyword.

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

USE_TIME: 0.034, ROW_COUNT: 1

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

In the following statement, the Huawei keyword and the OPPO keyword are used to query data entries in the rows in which the values of the title field include the Huawei keyword or the OPPO keyword.

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

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 b parameter in this function is parsed by the syntax parser for query clauses provided by HA3. If you set the b parameter to a constant string and execute a statement to perform the query, do not enclose the constant string in single quotation marks ('). For example, the query=Huawei OPPO clause in a statement is equivalent to the WHERE QUERY ('title', 'Huawei OPPO') condition in a QUERY function. If you use a logical operator in the query clause, you can enclose each constant in single quotation marks ('). For example, the query='Huawei' AND 'OPPO' clause in a statement is equivalent to the WHERE QUERY ('title', 'Huawei' AND 'OPPO') condition in a QUERY function. For more information about the limits on constant strings in SQL statements, see Limits.

Common error

Error type	Error cause	Correction
The query fails because the function syntax is invalid.	QUERY('pidvid','123:456')	QUERY('pidvid','"123:456"')

rangevalue

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

You can use this function to convert continuous values to discrete values.

Parameters:
v: specifies the field in which the continuous values that you want to convert are stored.
desc: describes the mapping rules.
Return values
The values that are returned after the specified values are converted to the specified discrete values.
Example
In the following statement, the rangevalue function is used to convert values in the price field based on the following rules: Values that are smaller than or equal to 1000 are converted to 1.0, values that are greater than 1000 and smaller than 5000 are converted to 2.0, and other values are not converted.
```
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

You can use this function to determine whether a value in the v field that corresponds to a forward index is in the range specified by the rangeDesc parameter.

Parameters:
v: specifies the field in which the data that you want to query is stored. The field must be a single-value field.
rangeDesc: specifies a value range, which can be an open interval, a closed interval, or a half-closed interval.
Return values
The following table describes samples of this function and the corresponding return values.

Sample	Return values
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: If you prefix the value of the rangeDesc parameter with an exclamation point (!), the values in the specified value range are not returned.

Example

In the following statement, the range function is used.

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

normalizescore

Prototype

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

Description

You can use this function to normalize the value in the v field to a value of the DOUBLE type. If the value is initialized, the function returns the input value. If the value is uninitialized, the function returns the value of the defaultScore parameter.

Parameters:
v: specifies the field in which the data that you want to query is stored. The field must be a single-value field.
defaultScore: specifies a constant string that can be converted to a string of the DOUBLE type.
Return values
If the value in the v field is initialized, the function returns the input value. If the value in the v field is uninitialized, the function returns the value of the defaultScore parameter.
Example

In the following statement, the normalizescore function is used to normalize the price field in doc1, doc2, and doc3.
doc1: price=1.0
doc2: price= (Uninitialized)
doc3: price=2.0

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