📣

TiDB Cloud Premium is now in public preview. Unlimited growth, instant elasticity, advanced security for enterprise workloads. Try it out →

Get Started

Quick Start

Guides

Connect
Manage Resources
Data Integration
Load Data
Unload Data
Multimodal Data Analytics
Performance Tuning
Security & Reliability
Management

Troubleshooting

Tutorials

Ingest & Stream Data
Migrate Data

Reference

SQL Reference
General Reference

Support Services

Full-Text Search Functions

TiDB Cloud Lake's full-text search functions deliver search-engine-style filtering for semi-structured VARIANT data and plain text columns that are indexed with an inverted index. They are ideal for AI-generated metadata—such as perception results from autonomous-driving video frames—stored alongside your assets.

Note

TiDB Cloud Lake's search functions are inspired by Elasticsearch Full-Text Search Functions.

Include an inverted index in the table definition for the columns you plan to search:

CREATE OR REPLACE TABLE frames (
  id INT,
  meta VARIANT,
  INVERTED INDEX idx_meta (meta)
);

Search Functions

Function	Description	Example
MATCH	Performs a relevance-ranked search across the listed columns.	`MATCH('summary, tags', 'traffic light red')`
QUERY	Evaluates a Lucene-style query expression, including nested `VARIANT` fields.	`QUERY('meta.signals.traffic_light:red')`
SCORE	Returns the relevance score for the current row when used with `MATCH` or `QUERY`.	`SELECT summary, SCORE() FROM frame_notes WHERE MATCH('summary, tags', 'traffic light red')`

Query Syntax Examples

Example: Single Keyword

SELECT id, meta['frame']['timestamp'] AS ts
FROM frames
WHERE QUERY('meta.detections.label:pedestrian')
LIMIT 100;

Example: Boolean AND

SELECT id, meta['frame']['timestamp'] AS ts
FROM frames
WHERE QUERY('meta.signals.traffic_light:red AND meta.vehicle.lane:center')
LIMIT 100;

Example: Boolean OR

SELECT id, meta['frame']['timestamp'] AS ts
FROM frames
WHERE QUERY('meta.signals.traffic_light:red OR meta.detections.label:bike')
LIMIT 100;

Example: IN List

SELECT id, meta['frame']['timestamp'] AS ts
FROM frames
WHERE QUERY('meta.tags:IN [stop urban]')
LIMIT 100;

Example: Inclusive Range

SELECT id, meta['frame']['timestamp'] AS ts
FROM frames
WHERE QUERY('meta.vehicle.speed_kmh:[0 TO 10]')
LIMIT 100;

Example: Exclusive Range

SELECT id, meta['frame']['timestamp'] AS ts
FROM frames
WHERE QUERY('meta.vehicle.speed_kmh:{0 TO 10}')
LIMIT 100;

Example: Boosted Fields

SELECT id, meta['frame']['timestamp'] AS ts, SCORE()
FROM frames
WHERE QUERY('meta.signals.traffic_light:red^1.0 AND meta.tags:urban^2.0')
LIMIT 100;