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

Topik ini menjelaskan cara membuat indeks pencarian untuk tipe data JSON.

Prasyarat

Engine tabel Lindorm harus versi 2.8.5 atau lebih baru. Untuk melihat atau melakukan upgrade versi saat ini, lihat Minor version update.
Engine LTS Lindorm harus versi 3.8.13.3 atau lebih baru. Untuk melihat atau melakukan upgrade versi saat ini, lihat Minor version update.

Catatan penggunaan

Saat membuat indeks pencarian, Anda harus menentukan tipe bidang indeks berdasarkan pola data JSON Anda. Tiga pola utama adalah:

Array elemen dasar
Elemen tingkat atas berupa array, dan setiap elemen dalam array tersebut merupakan elemen dasar. Contohnya termasuk array numerik, seperti [1, 2, 3], dan array string, seperti ["1", "2", "3"].
Objek JSON
Elemen tingkat atas berupa objek JSON. Contohnya, {"id": "GTY102289", "scores": [1.1, 2.9], "extra": {"A": "daew", "B": 88.99}}.
Array objek
Elemen tingkat atas berupa array, dan setiap elemen dalam array tersebut merupakan objek JSON. Contohnya, [{"name": "Alice", "address": "XX Street"},{"name": "Bob"}].

Penting

Jangan mencampur ketiga pola ini. Data yang tidak valid dapat dilewati dan tidak diindeks.

Penggunaan

Bagian berikut menjelaskan cara membuat indeks dan menjalankan kueri SQL untuk ketiga pola: array elemen dasar, objek JSON, dan array objek. Contoh disediakan.

Array elemen dasar

Buat tabel lebar

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

Tulis 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

Kolom JSON c1 berupa array string. Saat membuat indeks pencarian untuk kolom ini, Anda harus menentukan mapping='{"type": "keyword"}'.

Kolom JSON c2 berupa array integer. Saat membuat indeks pencarian untuk kolom ini, Anda harus menentukan mapping='{"type": "integer"}'.

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

Metode kueri

Untuk array elemen dasar, lapisan SQL mendukung dua metode kueri berikut:

JSON_CONTAINS: Mengharuskan semua elemen kandidat memenuhi kondisi yang ditentukan.
JSON_CONTAINS_ANY: Mengembalikan hasil jika ada elemen kandidat yang memenuhi kondisi yang ditentukan.

Kueri `JSON_CONTAINS`

-- Kolom c1 tidak berisi "101" dan "999" secara bersamaan.
Lindorm> SELECT * FROM test_json_array WHERE JSON_CONTAINS(c1, '["101", "999"]');
Empty set (0.11 sec)

-- Kolom c1 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)

Kueri `JSON_CONTAINS_ANY`

-- json_contains_any mengembalikan hasil jika ada elemen yang cocok. Kolom c1 untuk id=1001 berisi "101", dan kolom c1 untuk id=1002 berisi "999". Oleh karena itu, 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

Buat tabel lebar

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

Tulis 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

Anda dapat memilih antara mengandalkan inferensi tipe objek internal secara otomatis atau menggunakan struktur sub-objek yang telah ditentukan sebelumnya.

Mengandalkan inferensi tipe otomatis untuk objek internal

Kolom JSON user_info berupa objek JSON. Saat membuat indeks pencarian, Anda dapat menentukan type=jsonobject. Sistem kemudian secara otomatis menginferensi tipe bidang berdasarkan nilai pertama yang ditulis. Misalnya, user_info.name diinferensikan sebagai tipe string, dan user_info.age diinferensikan sebagai tipe numerik. Jika data selanjutnya tidak sesuai dengan tipe yang diinferensikan, sistem akan melewatkan data yang tidak valid untuk memastikan konsistensi indeks.

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

Menentukan struktur sub-objek sebelumnya (Direkomendasikan)

Mengandalkan inferensi tipe otomatis dapat menghasilkan tipe yang tidak terduga:

Untuk menjalankan kueri bertokenisasi pada bidang internal, Anda harus secara eksplisit menentukan tipe bidang untuk tokenisasi.
Jika objek JSON kompleks berisi array objek bersarang, Anda harus secara eksplisit menentukan tipe bidang sebagai nested.

Anda juga dapat menggunakan mapping untuk menentukan struktur internal dan menetapkan tipe bidang yang diperlukan sebelumnya.

Catatan

Saat menggunakan mapping untuk menentukan struktur internal, tipe bidang internal kompatibel dengan sintaks Elasticsearch.

-- Menentukan tipe sub-bidang sebelumnya
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"
      }
    }
  }')
);

Jika Anda hanya ingin mengindeks beberapa bidang dalam objek JSON, Anda dapat menambahkan parameter "dynamic": "false" untuk mengabaikan bidang lainnya.

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"
      }
    }
  }')
);

Metode kueri

Sistem mendukung metode kueri berikut untuk objek JSON:

Kueri yang menggunakan keluarga fungsi JSON_EXTRACT.
Kueri yang menggunakan JSON_CONTAINS dan JSON_CONTAINS_ANY pada elemen array elemen dasar di dalam objek JSON.
Kueri gabungan yang menggunakan JSON_EXTRACT dan MATCH.

Perlu diperhatikan bahwa kueri match untuk objek lengkap yang menggunakan JSON_EXTRACT atau JSON_CONTAINS tidak didukung. Misalnya, kueri seperti WHERE JSON_EXTRACT(json_col, '$.user') = '{"name": "Alice", "age": 12}' tidak didukung. Fungsi JSON_EXTRACT dalam indeks pencarian saat ini hanya mendukung pencocokan elemen tunggal.

Kueri `JSON_EXTRACT`

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)

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)

Kueri gabungan `JSON_EXTRACT` dan `MATCH`

Anda harus menentukan tipe sub-bidang JSON sebagai text sebelumnya. Tipe yang diinferensikan secara default adalah keyword, yang tidak mendukung kueri bertokenisasi.

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)

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)

Jalankan kueri `JSON_CONTAINS` pada path tertentu

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

Buat tabel lebar

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

Tulis 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 metode yang tersedia adalah inferensi otomatis ketergantungan tipe objek internal dan struktur sub-objek yang telah ditentukan sebelumnya.

Mengandalkan inferensi tipe otomatis untuk objek internal

Kolom JSON user berupa array objek. Saat membuat indeks pencarian, Anda dapat menentukan type=jsonarray. Sistem kemudian secara otomatis menginferensi tipe bidang berdasarkan nilai pertama yang ditulis untuk setiap objek dalam array. Misalnya, user.name diinferensikan sebagai tipe string, dan user.age diinferensikan sebagai tipe numerik. Jika data selanjutnya tidak sesuai dengan tipe yang diinferensikan, sistem akan melewatkan data yang tidak valid untuk memastikan konsistensi indeks.

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

Menentukan struktur sub-objek sebelumnya (Direkomendasikan)

Mengandalkan inferensi tipe otomatis dapat menghasilkan tipe yang tidak terduga:

Untuk menjalankan kueri bertokenisasi pada bidang internal, Anda harus secara eksplisit menentukan tipe bidang untuk tokenisasi.
Jika objek JSON kompleks berisi array objek bersarang, Anda harus secara eksplisit menentukan tipe bidang sebagai nested.

Anda dapat menggunakan mapping untuk menentukan struktur internal dan menetapkan tipe bidang yang diperlukan sebelumnya.

Catatan

Saat menggunakan mapping untuk menentukan struktur internal, tipe bidang internal kompatibel dengan sintaks Elasticsearch.

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

Jika Anda hanya ingin mengindeks beberapa bidang dalam objek JSON, Anda dapat menambahkan parameter "dynamic": "false" untuk mengabaikan bidang lainnya.

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

Metode kueri

Anda hanya dapat mengkueri array objek menggunakan fungsi SEARCH_QUERY. Anda harus menyematkan sintaks DSL Elasticsearch ke dalam kueri SQL Anda, seperti pada contoh berikut.

-- Kueri untuk baris di mana user.name adalah Alice dan user.age lebih besar dari atau sama dengan 10.
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}]                            |
+------+-----------------------------------------------------------+

Prasyarat

Catatan penggunaan

Penggunaan

Array elemen dasar

Buat tabel lebar

Tulis data

Buat indeks pencarian

Metode kueri

Kueri JSON_CONTAINS

Kueri JSON_CONTAINS_ANY

Objek JSON

Buat tabel lebar

Tulis data

Buat indeks pencarian

Mengandalkan inferensi tipe otomatis untuk objek internal

Menentukan struktur sub-objek sebelumnya (Direkomendasikan)

Metode kueri

Kueri JSON_EXTRACT

Kueri gabungan JSON_EXTRACT dan MATCH

Jalankan kueri JSON_CONTAINS pada path tertentu

Array objek

Buat tabel lebar

Tulis data

Buat indeks pencarian

Mengandalkan inferensi tipe otomatis untuk objek internal

Menentukan struktur sub-objek sebelumnya (Direkomendasikan)

Metode kueri

Kueri `JSON_CONTAINS`

Kueri `JSON_CONTAINS_ANY`

Kueri `JSON_EXTRACT`

Kueri gabungan `JSON_EXTRACT` dan `MATCH`

Jalankan kueri `JSON_CONTAINS` pada path tertentu