filter clause

The filtering feature allows you to select documents of interest based on query conditions. It further filters the documents retrieved by the query clause to return the final results.

Filter condition format: field=value

• Filter conditions support common conditional operators such as >, <, =, <=, >=, !=, and in/notin, and arithmetic operators such as +, -, *, /, &, ^, and |.

• You can configure multiple filter conditions and connect them by using the logical operators AND and OR and parentheses (). AND and OR must be in uppercase.

1. The filter clause is optional.

2. Fields that appear in the filter clause must be configured as attribute fields when you define the application schema.

3. Due to precision issues, exact equality cannot be determined for FLOAT and DOUBLE values. In such scenarios, use > and < instead.

4. Values of LITERAL fields must be enclosed in double quotation marks in the filter clause. Otherwise, error 6135 (constant expression type error) is returned. LITERAL fields support all relational operations but do not support arithmetic operations.

5. Functions in the function items of the sort feature, such as the distance function, can be used in the filter clause.

6. Filtering on LITERAL fields supports only the = and != operations, which mean equal to and not equal to. Relational operations such as > and < are not supported. (LITERAL fields are not tokenized and require an exact match.)

7. in/notin determines whether a field value is (or is not) in the specified list. It supports the INT, LITERAL, FLOAT, and DOUBLE types (ARRAY, TEXT, and fuzzy tokenization types are not supported). For more information, see in and notin.

8. Tokenized field types, such as TEXT and SHORT_TEXT, cannot be configured as attribute fields. Only numeric field types and non-tokenized field types can be configured as attribute fields. For example, the INT, INT_ARRAY, FLOAT, FLOAT_ARRAY, DOUBLE, DOUBLE_ARRAY, LITERAL, and LITERAL_ARRAY field types are supported.

• distance: obtains the spherical distance between two points. This function is generally used for distance calculation in location-based services (LBS).

Example:

Search for Grandma Kitchen restaurants within 10 kilometers of the user at (120.34256,30.56982). lon and lat are the longitude and latitude values of the merchant recorded in the document and must be configured as attribute fields:

query=default:'Grandma Kitchen'&&filter=distance(lon,lat,"120.34256","30.56982")

• in_polygon: determines whether a point is within a polygon. This function is generally used to determine delivery ranges.

Example:

Determine whether a user is within the delivery range of merchants. For example, the delivery range of a merchant is stored in the coordinates field, and the user location is (120.307234, 39.294245). Filter merchants whose delivery range covers the user:

query=default:'food'&&filter=in_polygon(coordinates, 120.307234, 39.294245)>0

• in_query_polygon: determines whether the specified point in a document is within the polygon specified by the user.

Example:

Search for Grandma Kitchen restaurants in the Intime business district (xA,yA,xB,Yb,xC,Yc;xD,yD,xE,yE,xF,yF,xG,yG). Merchant locations are stored in the point field.

query=default:'Grandma Kitchen'&&filter=in_query_polygon("polygons",point)>0&&kvpairs=polygons:xA\,yA\,xB\,Yb\,xC\,Yc;xD\,yD\,xE\,yE\,xF\,yF\,xG\,yG

• bit_struct: groups the values of an INT_ARRAY field in a custom way and allows specified operations to be performed on the grouped values.

Example:

Which stores are open during a given time period? Assume that the user documents contain an int64_array field named open_time, in which each value represents a business-hours period. The upper 32 bits of the int64 value represent the start time and the lower 32 bits represent the end time. To query stores that are open from 14:00 to 15:30, convert the time into a period measured in minutes from 00:00 of the day. In this case, 14:00 to 15:30 is represented as (840,930):

filter=bit_struct(open_time, "0-31,32-63","overlap,$1,$2,840,930")!=-1

• fieldlen: obtains the length of a LITERAL field.

Example:

Return documents in which the username field usr_name is not empty:

query=default:'keyword'&&filter=fieldlen(usr_name)>0

• in/notin: determines whether a field value is (or is not) in the specified list.

Example:

Query documents that contain "iphone" and whose type (INT type) is 1, 2, or 3:

query=default:'iphone'&&filter=in(type, "1|2|3")

Query documents that contain "iphone" and whose type (INT32 type) is not 1, 2, or 3:

query=default:'iphone'&&filter=notin(type, "1|2|3")

• multi_attr: returns the value at the specified position of an array field.

Example:

A product has multiple prices [market price, discount price, sales price], which are stored in the prices field. Query mobile phones whose sales price is less than 1000:

query=default:'mobile phone'&&filter=multi_attr(price,2)<1000

1. An application has an int32 field named category, whose values include 1 (news) and 2 (bbs). Query documents whose category is 2 (bbs) and that contain "Zhejiang University":

    query=default:'Zhejiang University' AND category_search:'2'   //An index field named category_search is created for the category field.
     //Or
    query=default:'Zhejiang University'&&filter=category=2

2. A novel application has a LITERAL_ARRAY field named tags, which represents novel style tags such as "palace", "suspense horror", and "romance". Query documents that contain "Empresses in the Palace" and whose tags include "palace":

    query=default:'Empresses in the Palace'&&filter=tags="palace"

3. An e-commerce application has the int32 fields hit (clicks) and sale (sales volume), and the int64 field create_time. Query documents that contain "dress", for which the sum of sales volume and clicks multiplied by rate (offset rate) exceeds 10000, and whose creation time is earlier than 1402345600:

    query=default:'dress'&&filter=(hit+sale)*rate>10000 AND create_time<1402345600