Panduan pengguna untuk indeks pencarian pada tipe data JSON di Lindorm - Lindorm

Lindorm mendukung pembuatan indeks pencarian pada kolom JSON dalam tabel lebar. Fungsi kueri yang tersedia bergantung pada struktur data JSON Anda. Panduan ini mencakup ketiga pola JSON yang didukung—array elemen dasar, objek JSON, dan array objek—serta menjelaskan jenis indeks dan fungsi kueri yang berlaku untuk masing-masing.

Prasyarat

Sebelum memulai, pastikan bahwa:

Mesin LindormTable adalah versi 2.8.5 atau lebih baru.
Mesin Lindorm LTS adalah versi 3.8.13.3 atau lebih baru.

Untuk memeriksa atau melakukan upgrade versi mesin Anda, lihat Minor version update.

Catatan penggunaan

Saat mengandalkan inferensi tipe otomatis JSON, jika tipe data suatu bidang JSON tidak konsisten antar penulisan (misalnya, bidang a pertama kali ditulis sebagai string dan kemudian sebagai angka), tipe yang disimpulkan dari penulisan pertama akan digunakan. Nilai berikutnya yang tidak sesuai akan dilewati.
Jika dokumen JSON berisi banyak bidang internal, inferensi otomatis dapat mencapai batas kolom indeks pencarian dan menyebabkan tugas sinkronisasi indeks gagal. Kami menyarankan agar Anda hanya mengindeks bidang JSON yang benar-benar dikueri (lihat contoh di bawah untuk detailnya). Batas kolom default adalah 1000. Pada LindormTable 2.8.6.1 dan versi lebih baru, Anda dapat menyesuaikan batas tersebut dengan SQL berikut:
```
ALTER INDEX idx ON search_table SET SEARCH_INDEX_MAX_COLUMN_COUNT='2000';
```

Pilih jenis indeks yang tepat

Sebelum membuat indeks pencarian pada kolom JSON, identifikasi pola mana dari ketiga pola berikut yang diikuti oleh data Anda. Mencampur pola dalam satu kolom menyebabkan data tidak valid dilewati secara diam-diam selama pengindeksan.

Pola JSON	Contoh	Jenis indeks	Fungsi kueri yang didukung
Array elemen dasar	`[1, 2, 3]` atau `["a", "b"]`	`mapping` dengan tipe skalar (`keyword`, `integer`, dll.)	`JSON_CONTAINS`, `JSON_CONTAINS_ANY`
Objek JSON	`{"name": "Alice", "age": 13}`	`type=jsonobject` atau `mapping` dengan `"type": "object"`	`JSON_EXTRACT`, `JSON_EXTRACT_STRING`, `JSON_CONTAINS`, `JSON_CONTAINS_ANY` (pada array bersarang), `MATCH`...`AGAINST`
Array objek	`[{"name": "Alice"}, {"name": "Bob"}]`	`type=jsonarray` atau `mapping` dengan `"type": "nested"`	`SEARCH_QUERY` dengan Elasticsearch DSL saja

Penting

Jangan mencampur ketiga pola tersebut dalam satu kolom. Data tidak valid mungkin dilewati dan tidak diindeks.

Array elemen dasar

Array elemen dasar menyimpan daftar nilai skalar datar di tingkat teratas, seperti ["101", "102", "109"] (array string) atau [1, 2, 3] (array integer).

Buat tabel lebar

CREATE TABLE test_json_array(id VARCHAR, c1 JSON, c2 JSON, PRIMARY KEY (id));

Masukkan data

UPSERT INTO test_json_array(id, c1, c2) VALUES ('1001', '["101", "102", "109"]', '[1, 2, 3]');
UPSERT INTO test_json_array(id, c1, c2) VALUES ('1002', '["999", "888", "777"]', '[1, 2, 3, 4, 5]');

Buat indeks pencarian

Tentukan tipe elemen dalam parameter mapping. Gunakan keyword untuk array string dan integer untuk array integer.

CREATE INDEX idx USING SEARCH ON test_json_array(
  c1(mapping='{
    "type": "keyword"
  }'),
  c2(mapping='{
    "type": "integer"
  }')
);

Kueri

Dua fungsi tersedia:

JSON_CONTAINS: mengembalikan baris di mana semua nilai yang ditentukan ada dalam array.
JSON_CONTAINS_ANY: mengembalikan baris di mana setidaknya satu dari nilai yang ditentukan ada.

`JSON_CONTAINS` — baris yang berisi semua nilai yang ditentukan

-- Tidak ada baris yang berisi "101" dan "999" sekaligus, sehingga hasilnya kosong
Lindorm> SELECT * FROM test_json_array WHERE JSON_CONTAINS(c1, '["101", "999"]');
Empty set (0.11 sec)

-- id=1001 berisi "101" dan "102"
Lindorm> SELECT * FROM test_json_array WHERE JSON_CONTAINS(c1, '["101", "102"]');
+------+-----------------------+-----------+
| id   | c1                    | c2        |
+------+-----------------------+-----------+
| 1001 | ["101", "102", "109"] | [1, 2, 3] |
+------+-----------------------+-----------+
1 row in set (0.01 sec)

`JSON_CONTAINS_ANY` — baris yang berisi setidaknya satu dari nilai yang ditentukan

-- id=1001 memiliki "101" dan id=1002 memiliki "999", sehingga kedua baris dikembalikan
Lindorm> SELECT * FROM test_json_array WHERE JSON_CONTAINS_ANY(c1, '["101", "999"]');
+------+-----------------------+---------------------+
| id   | c1                    | c2                  |
+------+-----------------------+---------------------+
| 1002 | ["999", "888", "777"] | [1, 2, 3, 5, 9, 10] |
| 1001 | ["101", "102", "109"] | [1, 2, 3]           |
+------+-----------------------+---------------------+
2 rows in set (0.01 sec)

Objek JSON

Objek JSON menyimpan dokumen terstruktur di tingkat teratas, seperti {"name": "Alice", "age": 13, "hobbies": ["read", "badminton"]}. Bidang dapat berupa nilai skalar, array, atau objek bersarang.

Buat tabel lebar

CREATE TABLE test_json_object(id VARCHAR, user_info JSON, PRIMARY KEY (id));

Masukkan data

UPSERT INTO test_json_object(id, user_info) VALUES ('1001', '{"name": "Alice", "age": 13, "address": "Hangzhou, Zhejiang Province", "hobbies": ["play games", "read", "badminton"]}');
UPSERT INTO test_json_object(id, user_info) VALUES ('1002', '{"name": "Bob", "age": 9, "address": "Ningbo, Zhejiang Province", "hobbies": ["play games"]}');
UPSERT INTO test_json_object(id, user_info) VALUES ('1003', '{"name": "John", "age": 21, "address": "Shenzhen, Guangdong Province", "hobbies": ["read", "badminton", "food"]}');

Buat indeks pencarian

Dua pendekatan tersedia. Gunakan struktur yang telah ditentukan jika memungkinkan.

Inferensi tipe otomatis

Tentukan type=jsonobject agar sistem menyimpulkan tipe bidang dari nilai pertama yang ditulis. Misalnya, user_info.name disimpulkan sebagai string dan user_info.age sebagai numerik. Data yang ditulis kemudian dan tidak sesuai dengan tipe yang disimpulkan akan dilewati untuk menjaga konsistensi indeks.

CREATE INDEX idx USING SEARCH ON test_json_object(user_info(type=jsonobject));

Tentukan struktur bidang secara eksplisit (disarankan)

Inferensi tipe otomatis memiliki dua keterbatasan:

Untuk menjalankan kueri tokenized (full-text) pada suatu bidang, tipe bidang harus diatur secara eksplisit ke text. Tipe yang disimpulkan secara default adalah keyword, yang tidak mendukung tokenisasi.
Jika objek JSON berisi array objek bersarang, tipe bidang harus diatur secara eksplisit ke nested.

Gunakan mapping untuk menentukan struktur bidang internal. Tipe bidang mengikuti sintaks Elasticsearch.

-- Tentukan tipe bidang secara eksplisit untuk mengaktifkan kueri tokenized dan mengontrol perilaku pengindeksan
CREATE INDEX idx USING SEARCH ON test_json_object(
  user_info(mapping='{
    "type": "object",
    "properties": {
      "name": {
        "type": "keyword"
      },
      "age": {
        "type": "integer"
      },
      "address": {
        "type": "text",
        "analyzer": "ik_max_word"
      },
      "hobbies": {
        "type": "keyword"
      }
    }
  }')
);

Untuk hanya mengindeks subset bidang dan mengabaikan sisanya, tambahkan "dynamic": "false":

CREATE INDEX idx USING SEARCH ON test_json_object(
  user_info(mapping='{
    "type": "object",
    "dynamic": "false",
    "properties": {
      "name": {
        "type": "keyword"
      },
      "age": {
        "type": "integer"
      },
      "address": {
        "type": "text",
        "analyzer": "ik_max_word"
      },
      "hobbies": {
        "type": "keyword"
      }
    }
  }')
);

Kueri

Tiga metode kueri didukung untuk objek JSON:

JSON_EXTRACT / JSON_EXTRACT_STRING — mencocokkan satu bidang skalar berdasarkan path.
JSON_CONTAINS / JSON_CONTAINS_ANY — mencocokkan elemen dalam bidang array di dalam objek.
MATCH...AGAINST dikombinasikan dengan JSON_EXTRACT — pencarian full-text (tokenized) pada bidang text.

Pencocokan sub-objek lengkap tidak didukung. Misalnya, WHERE JSON_EXTRACT(json_col, '$.user') = '{"name": "Alice", "age": 12}' tidak valid. JSON_EXTRACT dalam indeks pencarian hanya mencocokkan elemen skalar tunggal.

`JSON_EXTRACT` — temukan baris berdasarkan nilai bidang skalar

-- Temukan pengguna yang namanya Alice
Lindorm> SELECT * FROM test_json_object WHERE JSON_EXTRACT_STRING(user_info, '$.name')='Alice';
+------+------------------------------------------------------------------------------------------------+
| id   | user_info                                                                                      |
+------+------------------------------------------------------------------------------------------------+
| 1001 | {"name": "Alice", "age": 13, "address": "Hangzhou, Zhejiang Province", "hobbies": ["play games", "read", "badminton"]} |
+------+------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)

-- Temukan pengguna yang usianya 21
Lindorm> SELECT * FROM test_json_object WHERE JSON_EXTRACT(user_info, '$.age')=21;
+------+--------------------------------------------------------------------------------------------------------+
| id   | user_info                                                                                              |
+------+--------------------------------------------------------------------------------------------------------+
| 1003 | {"name": "John", "age": 21, "address": "Shenzhen, Guangdong Province", "hobbies": ["read", "badminton", "food"]}       |
+------+--------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

`MATCH`...`AGAINST` dengan `JSON_EXTRACT` — pencarian full-text pada bidang teks

Bidang address harus ditentukan sebelumnya sebagai type: text. Tipe yang disimpulkan secara default (keyword) tidak mendukung kueri tokenized.

-- Temukan pengguna yang alamatnya mengandung "Zhejiang" (pencocokan tokenized di beberapa kata)
Lindorm> SELECT * FROM test_json_object WHERE MATCH(JSON_EXTRACT(user_info, '$.address')) AGAINST ('Zhejiang');
+------+---------------------------------------------------------------+
| id   | user_info                                                     |
+------+---------------------------------------------------------------+
| 1001 | {"name": "Alice", "age": 13, "address": "Hangzhou, Zhejiang", "hobbies": ["play games", "read", "badminton"]}    |
| 1002 | {"name": "Bob", "age": 9, "address": "Ningbo, Zhejiang", "hobbies": ["play games"]}                              |
+------+---------------------------------------------------------------+
2 rows in set (0.03 sec)

-- Temukan pengguna yang alamatnya mengandung "Hangzhou"
Lindorm> SELECT * FROM test_json_object WHERE MATCH(JSON_EXTRACT(user_info, '$.address')) AGAINST ('Hangzhou');
+------+---------------------------------------------------------------+
| id   | user_info                                                     |
+------+---------------------------------------------------------------+
| 1001 | {"name": "Alice", "age": 13, "address": "Hangzhou, Zhejiang", "hobbies": ["play games", "read", "badminton"]}    |
+------+---------------------------------------------------------------+
1 row in set (0.01 sec)

`JSON_CONTAINS` — temukan baris di mana bidang array berisi nilai tertentu

-- Temukan pengguna yang memiliki "read" dalam daftar hobi mereka
Lindorm> SELECT * FROM test_json_object WHERE JSON_CONTAINS(user_info, '["read"]', '$.hobbies');
+------+---------------------------------------------------------------------------------------------------------------+
| id   | user_info                                                                                                     |
+------+---------------------------------------------------------------------------------------------------------------+
| 1003 | {"name": "John", "age": 21, "address": "Shenzhen, Guangdong Province", "hobbies": ["read", "badminton", "food"]}              |
| 1001 | {"name": "Alice", "age": 13, "address": "Hangzhou, Zhejiang Province", "hobbies": ["play games", "read", "badminton"]}       |
+------+---------------------------------------------------------------------------------------------------------------+
2 rows in set (0.04 sec)

Array objek

Array objek menyimpan array di tingkat teratas di mana setiap elemennya adalah objek JSON, seperti [{"name": "Alice", "age": 12}, {"name": "Bob", "age": 20}].

Mengapa array objek memerlukan jenis indeks berbeda

Tanpa jenis indeks khusus, database meratakan array objek menjadi bidang multi-nilai independen, sehingga kehilangan asosiasi antar bidang yang termasuk dalam objek yang sama. Misalnya, diberikan dua baris berikut:

id	user
1002	`[{"name": "Alice", "age": 9}, {"name": "Bob", "age": 20}]`

Indeks naif (non-nested) akan menyimpan nilai name dan age sebagai daftar datar: name = [Alice, Bob] dan age = [9, 20]. Kueri untuk "Alice dengan usia >= 10" akan salah mencocokkan baris ini karena Alice dan age=20 kebetulan ada dalam bidang yang diratakan, meskipun usia sebenarnya dari Alice adalah 9.

Lindorm menggunakan type: nested secara internal untuk indeks array objek. Setiap objek dalam array diindeks sebagai unit terisolasi, sehingga asosiasi bidang dalam objek yang sama tetap terjaga. Akibatnya, hanya SEARCH_QUERY dengan DSL Elasticsearch yang disematkan yang didukung untuk mengkueri pola ini—kueri DSL nested menyatakan kendala intra-objek ini secara tepat.

Buat tabel lebar

CREATE TABLE test_json_object_array(id VARCHAR, user JSON, primary key(id));

Masukkan data

UPSERT INTO test_json_object_array(id, user) VALUES ('1001', '[{"name": "Alice", "age": 12}]');
UPSERT INTO test_json_object_array(id, user) VALUES ('1002', '[{"name": "Alice", "age": 9},{"name": "Bob", "age": 20}]');
UPSERT INTO test_json_object_array(id, user) VALUES ('1003', '[{"name": "Alice", "age": 12},{"name": "Bob", "age": 20}]');

Buat indeks pencarian

Dua pendekatan tersedia. Gunakan struktur yang telah ditentukan jika memungkinkan.

Inferensi tipe otomatis

Tentukan type=jsonarray agar sistem menyimpulkan tipe bidang dari nilai pertama yang ditulis untuk setiap objek dalam array. Data yang ditulis kemudian dan tidak sesuai dengan tipe yang disimpulkan akan dilewati.

CREATE INDEX idx USING SEARCH ON test_json_object_array(user(type=jsonarray));

Tentukan struktur bidang secara eksplisit (disarankan)

Inferensi tipe otomatis memiliki keterbatasan yang sama seperti pada objek JSON: kueri tokenized memerlukan tipe text eksplisit, dan array objek bersarang dalam suatu bidang memerlukan tipe nested eksplisit.

Gunakan mapping untuk menentukan struktur bidang internal. Tipe tingkat teratas harus nested. Tipe bidang mengikuti sintaks Elasticsearch.

CREATE INDEX idx USING SEARCH ON test_json_object_array(
  user(mapping='{
    "type": "nested",
    "properties": {
      "name": {
        "type": "keyword"
      },
      "age": {
        "type": "integer"
      }
    }
  }')
);

Untuk hanya mengindeks subset bidang dan mengabaikan sisanya, tambahkan "dynamic": "false":

CREATE INDEX idx USING SEARCH ON test_json_object_array(
  user(mapping='{
    "type": "nested",
    "dynamic": "false",
    "properties": {
      "name": {
        "type": "keyword"
      },
      "age": {
        "type": "integer"
      }
    }
  }')
);

Kueri

Kueri array objek menggunakan fungsi SEARCH_QUERY dengan DSL Elasticsearch yang disematkan. Gunakan kueri nested dengan parameter path diatur ke nama kolom.

-- Temukan baris di mana satu objek pengguna memiliki name "Alice" DAN age >= 10
-- (menjaga asosiasi bidang dalam setiap objek)
Lindorm> SELECT * FROM test_json_object_array WHERE SEARCH_QUERY('
  {
    "nested": {
      "path": "user",
      "query": {
        "bool": {
          "must": [
            { "match": { "user.name": "Alice" } },
            { "range": { "user.age": {"gte": 10} } }
          ]
        }
      }
    }
  }
');
+------+-----------------------------------------------------------+
| id   | user                                                      |
+------+-----------------------------------------------------------+
| 1003 | [{"name": "Alice", "age": 12},{"name": "Bob", "age": 20}] |
| 1001 | [{"name": "Alice", "age": 12}]                            |
+------+-----------------------------------------------------------+

id=1002 dikecualikan karena entri "Alice"-nya memiliki age=9, yang kurang dari 10. Indeks nested menjaga asosiasi antar bidang dalam objek yang sama, sehingga Alice dan age=9 dievaluasi bersama secara tepat, bukan dicampur dengan usia Bob yang 20.