Tablestore:Operasi indeks pencarian

Operasi yang didukung

Pencarian teks penuh

Cocokkan data dalam bidang bertipe Text menggunakan TEXT_MATCH untuk pencocokan token atau TEXT_MATCH_PHRASE untuk pencocokan frasa.

TEXT_MATCH(fieldName, text [, options])

TEXT_MATCH_PHRASE(fieldName, text)

Contoh

Kueri data dalam kolom content yang berisi token "tablestore":

SELECT * FROM search_exampletable WHERE TEXT_MATCH(content, 'tablestore') LIMIT 10;

Kueri data dalam kolom content yang berisi frasa berurutan "sql query":

SELECT * FROM search_exampletable WHERE TEXT_MATCH_PHRASE(content, 'sql query') LIMIT 10;

Gunakan parameter options untuk mencocokkan data yang berisi setidaknya dua token:

SELECT * FROM search_exampletable WHERE TEXT_MATCH(content, 'tablestore is cool', 'or', '2') LIMIT 10;

Gunakan operator AND untuk mengharuskan semua token hadir:

SELECT * FROM search_exampletable WHERE TEXT_MATCH(content, 'tablestore is cool', 'and') LIMIT 10;

Kueri array

Gunakan fungsi ARRAY_EXTRACT untuk mengkueri data dalam kolom bertipe array. Konfigurasikan kolom sebagai tipe array dalam indeks pencarian dengan mengaktifkan opsi array di Konsol atau mengatur IsArray ke true di SDK. Saat menulis data, nilai array harus dalam format array JSON, seperti ["a","b","c"].

Pemetaan tipe data

ARRAY_EXTRACT(col_name)

ARRAY_EXTRACT memperluas kolom array dan menggabungkannya dengan operator sebagai kondisi klausa WHERE. Operator yang didukung meliputi kesamaan (=), rentang (>, <), dan LIKE.

Batasan

• ARRAY_EXTRACT hanya dapat digunakan pada tabel pemetaan indeks pencarian, dan hanya satu parameter kolom array yang diperbolehkan per pemanggilan. Fungsi ini hanya dapat digunakan sebagai kondisi klausa WHERE, tidak sebagai ekspresi SELECT maupun untuk agregasi dan pengurutan.

• Kolom array tanpa ARRAY_EXTRACT dapat digunakan sebagai nama kolom SELECT atau ekspresi, tetapi tidak dapat digunakan untuk agregasi dan pengurutan.

• Saat ARRAY_EXTRACT dikombinasikan dengan operator sebagai kondisi kueri, konversi tipe data tidak didukung. Nilai kueri harus sesuai dengan tipe data kolom array. Misalnya, kolom array bertipe Long mendukung ARRAY_EXTRACT(col) = 1 tetapi tidak mendukung ARRAY_EXTRACT(col) = '1'.

• Elemen array bertipe Text harus digunakan dengan fungsi TEXT_MATCH atau TEXT_MATCH_PHRASE, seperti TEXT_MATCH(ARRAY_EXTRACT(col_text), 'keyword').

Contoh

-- Kueri baris yang berisi nilai 'apple' dalam array
SELECT * FROM search_exampletable WHERE ARRAY_EXTRACT(col_array) = 'apple';

-- Kueri baris yang berisi elemen yang dimulai dengan 'd' dalam array
SELECT * FROM search_exampletable WHERE ARRAY_EXTRACT(col_array) LIKE 'd%';

Kueri tipe nested

Kolom tipe nested menyimpan array JSON di mana setiap elemen berisi beberapa subkolom. Tipe data kolom dalam tabel data harus bertipe String. Konfigurasikan kolom sebagai tipe Nested dan tentukan tipe data subkolom saat membuat indeks pencarian.

Saat membuat tabel pemetaan, definisikan kolom tipe nested sebagai MEDIUMTEXT. Subkolom internal dibuat secara otomatis dan dapat dilihat dengan DESCRIBE, seperti col_nested.name dan col_nested.age. Dalam kueri, nama subkolom menggunakan format nested_column.sub_column, dengan titik (.) memisahkan beberapa level nesting, seperti col1.col2.col3.

Pemetaan tipe data

Metode kueri

SELECT * FROM search_exampletable WHERE `col_nested.age` > 30;

NESTED_QUERY(subcol_column_condition)

Perbedaan antara kedua metode

Asumsikan kolom nested tags berisi data baris berikut: [{"tagName":"tag1", "score":0.8}, {"tagName":"tag2", "score":0.2}]:

• tags.tagName — Cocok, karena elemen pertama memenuhi kondisi tagName dan elemen kedua memenuhi kondisi score.

• NESTED_QUERY( — Tidak cocok, karena tidak ada elemen tunggal yang memenuhi kedua kondisi sekaligus.

Batasan

• NESTED_QUERY hanya dapat digunakan pada tabel pemetaan indeks pencarian dan hanya sebagai klausa WHERE. Fungsi ini tidak dapat digunakan sebagai ekspresi SELECT maupun untuk agregasi, pengelompokan, atau pengurutan.

• Subkolom nested tidak dapat digunakan sebagai ekspresi SELECT maupun untuk agregasi, pengelompokan, atau pengurutan.

• ALTER TABLE tidak dapat langsung menambah atau menghapus subkolom nested. Anda hanya dapat menambah atau menghapus seluruh kolom nested, dan subkolom akan secara otomatis ditambahkan atau dihapus bersamanya.

• Subkolom nested tidak mendukung konversi tipe data atau komputasi fungsi yang tidak dapat di-push down ke indeks pencarian. Pastikan tipe data subkolom nested sudah benar.

Contoh

-- Kueri subkolom langsung: kueri baris di mana age > 30 dalam kolom nested
SELECT * FROM search_exampletable WHERE `col_nested.age` > 30;

-- NESTED_QUERY: kueri baris di mana satu elemen memiliki name yang dimulai dengan 'I' dan age < 20
SELECT * FROM search_exampletable WHERE NESTED_QUERY(`col_nested.name` LIKE 'I%' AND `col_nested.age` < 20);

-- Nesting multi-level
SELECT * FROM search_exampletable WHERE NESTED_QUERY(`col1.col2` = 1 AND NESTED_QUERY(`col1.col3.col4` = 2));

Kueri kolom virtual

Kolom virtual indeks pencarian memungkinkan Anda mengkueri bidang dan tipe baru dengan memodifikasi skema indeks pencarian, tanpa mengubah struktur penyimpanan tabel data. Kolom virtual didefinisikan dalam tabel pemetaan dengan tipe data SQL aktualnya.

Pemetaan tipe data

Penggunaan yang didukung

• Menyaring data dalam klausa WHERE. Tipe data kolom virtual dalam kondisi harus sesuai dengan tipe parameter kueri.

• Digunakan dalam agregasi dan pengelompokan. Tipe data sumber kolom virtual harus kompatibel dengan operasi tersebut. Misalnya, hanya tipe Long dan Double yang mendukung SUM. Kolom virtual bertipe Keyword tidak dapat dijumlahkan, dan kolom virtual bertipe Text tidak mendukung pengelompokan.

• Kueri TopN dan pengurutan didukung. Pengurutan memerlukan LIMIT.

Batasan

• Kolom virtual hanya dapat digunakan dalam tabel pemetaan indeks pencarian.

• Kolom virtual hanya dapat digunakan dalam kondisi kueri. Kolom ini tidak dapat digunakan dalam SELECT untuk mengembalikan nilai kolom. Untuk mengembalikan nilai, tentukan kolom sumber dari kolom virtual tersebut. SELECT * tidak terpengaruh dan secara otomatis mengecualikan kolom virtual dari hasil.

• Kolom virtual tidak dapat digunakan untuk perbandingan kolom, komputasi, atau JOIN.

• Kolom virtual tidak mendukung konversi tipe data atau komputasi fungsi yang tidak dapat di-push down ke indeks pencarian. Saat ini, hanya fungsi agregat yang dapat di-push down dalam kueri SQL.

Contoh

Buat tabel pemetaan indeks pencarian yang mencakup kolom virtual:

CREATE TABLE search_exampletable(
    col_keyword MEDIUMTEXT,
    col_keyword_virtual_long BIGINT
)
ENGINE='searchindex',
ENGINE_ATTRIBUTE='{"index_name":"exampletable_index","table_name":"exampletable"}';

Kueri dengan kolom virtual:

SELECT * FROM search_exampletable WHERE col_keyword_virtual_long > 100 LIMIT 10;

Pencarian vektor

Gunakan fungsi VECTOR_QUERY_FLOAT32 untuk kueri approximate nearest neighbor (ANN). Bidang vektor disimpan sebagai string dalam tabel data. Konfigurasikan sebagai tipe vektor dalam indeks pencarian, dan tentukan dimensi, tipe data, serta metrik jarak. Tipe data SQL kolom vektor dalam tabel pemetaan adalah MEDIUMTEXT.

VECTOR_QUERY_FLOAT32

VECTOR_QUERY_FLOAT32(fieldName, float32QueryVector, topK, filter)

Fungsi SCORE()

Gunakan SCORE() dengan VECTOR_QUERY_FLOAT32 sebagai ekspresi SELECT untuk mengembalikan skor relevansi setiap hasil. Skor yang lebih tinggi menunjukkan kemiripan yang lebih besar.

SCORE()

Batasan

• VECTOR_QUERY_FLOAT32 hanya dapat digunakan pada tabel pemetaan indeks pencarian dan harus digunakan dengan LIMIT. Klausul HAVING tidak didukung.

• VECTOR_QUERY_FLOAT32 hanya dapat digunakan sebagai klausa WHERE. Fungsi ini tidak dapat digunakan sebagai ekspresi SELECT maupun untuk agregasi, pengelompokan, atau pengurutan.

• SCORE() hanya dapat digunakan dengan VECTOR_QUERY_FLOAT32 dan hanya sebagai ekspresi SELECT. Fungsi ini tidak dapat digunakan dalam klausa WHERE, agregasi, atau pengurutan.

• Kondisi lain dalam klausa WHERE harus mendukung pushdown ke indeks pencarian. Jika tidak, kueri akan gagal. Untuk daftar operator push-down yang didukung, lihat Optimasi kueri.

Contoh

Kueri 10 hasil paling mirip dengan vektor yang ditentukan dalam kolom col_vector:

SELECT *, SCORE() FROM exampletable WHERE VECTOR_QUERY_FLOAT32(col_vector, '[1.5, -1.5, 2.5, -2.5]', 10) LIMIT 10;

Gunakan filter untuk mempersempit set kandidat sebelum pencarian vektor guna memperoleh hasil yang lebih akurat:

SELECT *, SCORE() FROM exampletable WHERE VECTOR_QUERY_FLOAT32(col_vector, '[1.5, -1.5, 2.5, -2.5]', 100, col_keyword='cat_a' AND year_num=2024) LIMIT 10;

Gunakan AND dalam klausa WHERE untuk penyaringan pasca-pencarian vektor. Hasil topK mungkin tidak mencakup semua baris yang cocok:

SELECT *, SCORE() FROM exampletable WHERE col_keyword='cat_a' AND VECTOR_QUERY_FLOAT32(col_vector, '[1.5, -1.5, 2.5, -2.5]', 500) LIMIT 10;

Fungsi JSON

Fungsi JSON SQL Tablestore mengikuti sintaks MySQL 5.7 dan mengekstraksi data dari kolom berformat JSON.

->> (ekstraksi path JSON)

Mengekstraksi nilai pada path yang ditentukan dari kolom JSON dan meng-unquote-nya menjadi string. Setara dengan JSON_UNQUOTE(JSON_EXTRACT()).

column->>'$.path'

Contoh

SELECT col_json->>'$.city' AS city FROM exampletable LIMIT 10;

JSON_UNQUOTE

Menghapus tanda kutip luar dari nilai JSON dan mengembalikan string. Mengembalikan NULL jika argumennya NULL.

JSON_UNQUOTE(json_val)

Contoh

SELECT JSON_UNQUOTE(JSON_EXTRACT(col_json, '$.city')) AS city FROM exampletable LIMIT 10;

JSON_EXTRACT

Menyaring sub-dokumen pada path yang ditentukan dari kolom JSON. Nilai kembali mempertahankan format JSON, dengan nilai string dibungkus tanda kutip. Beberapa path dapat ditentukan, dan hasilnya dikembalikan dalam format array.

JSON_EXTRACT(json_doc, path[, path] ...)

Contoh

Ekstraksi satu path:

SELECT JSON_UNQUOTE(JSON_EXTRACT(col_json, '$.city')) AS city FROM exampletable WHERE pk = 1;

Ekstraksi beberapa path. Hasilnya dikembalikan dalam format array:

-- Asumsikan col_json berisi {"a": 1, "b": 2, "c": {"d": 4}}
SELECT JSON_UNQUOTE(JSON_EXTRACT(col_json, '$.a', '$.b', '$.c.d')) AS subdoc FROM exampletable WHERE pk = 1;
-- Hasil: [1, 2, 4]

Sintaks JSON Path

Path harus dimulai dengan $, yang merepresentasikan seluruh dokumen JSON. Tambahkan selector path setelah $. Selector dapat dikombinasikan.

Contoh kueri objek JSON

Asumsikan kolom JSON berisi {"a": 1, "f": [1, 2, 3], "c": {"d": 4}}:

Contoh kueri array JSON

Asumsikan kolom JSON berisi [3, {"a": [5, 6], "b": 10}, [99, 100]]. Nilai kembali non-scalar mendukung kueri bersarang.