SLS search() function - full-text search in SQL analytics - Simple Log Service

Prerequisites

A Standard LogStore is created. Manage a LogStore.
Log data is collected. Data ingestion overview.
An index is created with scan mode disabled.

Examples

Combine with SQL predicates

Combine search() with SQL predicates using AND for more precise filtering.

-- search + comparison operator
* | SELECT * FROM log
  WHERE search('status: 200') AND request_time > 100

-- search + IN clause
* | SELECT * FROM log
  WHERE search('request_method: GET') AND status IN (200, 301, 302)

-- search + LIKE
* | SELECT * FROM log
  WHERE search('status: 200') AND http_user_agent LIKE '%Chrome%'

-- search + BETWEEN
* | SELECT * FROM log
  WHERE search('request_method: POST') AND request_time BETWEEN 100 AND 500

-- search + complex condition combination (OR cannot include search)
* | SELECT * FROM log
  WHERE search('request_method: GET')
    AND (status = 200 OR status = 302)
    AND request_time > 50

Only AND can connect search() with other SQL predicates.
search() cannot appear in any OR expression. For example, search('error') OR status = 500 is not allowed.
OR is allowed among SQL predicates that do not include search(). For example, search('error') AND (status = 500 OR status = 502) is allowed.

Multi-table JOIN scenarios

search() supports multi-LogStore JOINs. Each subquery applies its own search() filter independently.

Note

Each subquery can contain only one search() call. A multi-table JOIN can have multiple search() calls because each search() belongs to a different subquery.

Sample data

The following examples use an orders table (orders LogStore) and a users table (users LogStore).

Schema of the orders table (orders LogStore):

Field name	Field type	Description
order_id	long	Order ID
user_id	long	User ID, associated with the users table
status	text	Order status, such as completed, pending, or cancelled
amount	double	Order amount
order_type	text	Order type, such as normal or vip

Schema of the users table (users LogStore):

Field name	Field type	Description
user_id	long	User ID, primary key
username	text	Username
region	text	The region where the user is located, such as hangzhou, shanghai, or beijing
email	text	User's email
age	long	User's age

INNER JOIN

Query completed orders (status = completed) with matching users in the hangzhou region.

* | SELECT o.order_id, o.status, o.amount, u.username, u.region
    FROM (
        SELECT * FROM orders.log
        WHERE search('status: completed')
    ) o
    JOIN (
        SELECT * FROM users.log
        WHERE search('region: hangzhou')
    ) u
    ON o.user_id = u.user_id
    ORDER BY o.order_id

LEFT JOIN

Return all completed orders and match only users in the shanghai region. Orders without a matching user show null for user-related fields.

* | SELECT o.order_id, o.status, u.username, u.region
    FROM (
        SELECT * FROM orders.log
        WHERE search('status: completed')
    ) o
    LEFT JOIN (
        SELECT * FROM users.log
        WHERE search('region: shanghai')
    ) u
    ON o.user_id = u.user_id
    ORDER BY o.order_id

Self-join

In a self-join, apply different search conditions to subqueries with different aliases.

The following example uses an employees table (employees LogStore) with this schema:

Field name	Field type	Description
employee_id	long	Employee ID
employee_name	text	Employee name
department	text	Department, such as engineering or finance
level	text	Level, such as junior or senior
manager_id	long	The employee_id of the manager

Query employees in the engineering department along with their senior-level managers:

* | SELECT e.employee_name AS employee, e.department,
           m.employee_name AS manager, m.level AS manager_level
    FROM (
        SELECT * FROM employees.log
        WHERE search('department: engineering')
    ) e
    JOIN (
        SELECT * FROM employees.log
        WHERE search('level: senior')
    ) m
    ON e.manager_id = m.employee_id
    ORDER BY e.employee_name

Limits

Limit	Description
Single instance limit	Each subquery can contain only one `search()` call. To apply multiple conditions, merge them into one `search()` call, for example, `search('error AND timeout')`.
OR operator limit	`search()` cannot be combined with OR at the SQL level. OR is supported inside the search function, for example, `search('error OR warning')`.
Scan mode limit	The search function is not supported in scan mode.
Query syntax input conflict	`search()` is not supported when the query syntax input contains filter conditions. The function works only when the query input is empty or `*`.
Parameter type	The search function parameter must be a string literal. Dynamic values such as column references, variables, or function expressions are not supported.

Combination usage limits

Scenario	Supported	Example
Single search function	Supported	`WHERE search('error AND timeout')`
search + AND + SQL condition	Supported	`WHERE search('error') AND status = 500`
search + AND + (c1 OR c2)	Supported	`WHERE search('error') AND (status = 500 OR status = 502)`
OR operator used inside search	Supported	`WHERE search('error OR warning')`
search in each subquery of a multi-table JOIN	Supported	Each subquery operates independently, without affecting other subqueries.
Multiple search functions in the same subquery	Not supported	Not supported`WHERE search('error') AND search('timeout')` Instead, use `WHERE search('error AND timeout')`.
search + OR + SQL condition	Not supported	Not supported`WHERE search('error') OR status = 500`

Best practices

Merge query conditions: Merge all full-text search conditions into one search() call. Multiple search() calls in the same subquery are not supported.
Use field-specific queries: Use field: value format instead of general full-text search to improve precision and performance.
Use numeric types: For long or double fields, use range queries (field in [min max]) instead of text matching.
Use subfield paths for JSON fields: Specify a precise subfield path such as content.status to avoid fuzzy matching at the parent level.
Combine SQL predicates effectively: Use search() for full-text filtering and SQL predicates for numeric comparisons. Combine with AND for both performance and flexibility.

FAQ

Error: `key (xxx) is not config as key value config`

The queried field has no field index. Check your index configuration and create a field index for this field.

Error: `Multiple search() functions in a single query are not supported`

Multiple search() calls exist in the same subquery. Merge them into one call, for example, change search('error') AND search('timeout') to search('error AND timeout').

Error: `The search() function cannot be combined with OR operator`

search() is connected to other conditions with OR. Move the OR logic inside the search function. For example, change search('error') OR status = 500 to search('error OR status: 500').

Error: `The search() function is not supported in scan mode`

Scan mode does not support search(). Switch to index mode to use this function.