Lindorm:Fungsi JSON

json_object

Membuat objek JSON dari pasangan kunci-nilai.

Sintaksis

json_object(key1, value1, key2, value2, ...)

Parameter

Contoh

Buat objek sederhana:

SELECT json_object('name', 'Alice', 'age', 25);
-- Result: {"name": "Alice", "age": 25}

Gunakan dalam pernyataan UPSERT:

UPSERT INTO users (id, data) VALUES (1, json_object('name', 'Charlie', 'city', 'Beijing'));

json_array

Membuat array JSON dari daftar nilai.

Sintaksis

json_array(value1, value2, value3, ...)

Parameter

Contoh

Buat array sederhana:

SELECT json_array('apple', 'banana', 'orange');
-- Result: ["apple", "banana", "orange"]

Gunakan dalam pernyataan UPSERT:

UPSERT INTO products (id, tags) VALUES (1, json_array('electronics', 'mobile', 'smartphone'));

json_extract

Mengekstraksi nilai dari dokumen JSON pada path yang ditentukan dan mengembalikannya dalam tipe JSON aslinya.

Sintaksis

json_extract(json_column, 'path')

Parameter

Contoh

Ekstraksi bidang tingkat atas:

SELECT json_extract('{"name": "Alice", "age": 25}', '$.name');
-- Result: "Alice"

Ekstraksi bidang bersarang:

SELECT json_extract('{"user": {"name": "Bob", "age": 30}}', '$.user.name');
-- Result: "Bob"

Ekstraksi elemen array:

SELECT json_extract('{"skills": ["Java", "Python"]}', '$.skills[0]');
-- Result: "Java"

Ekstraksi seluruh array:

SELECT json_extract('{"skills": ["Java", "Python"]}', '$.skills');
-- Result: ["Java", "Python"]

Gunakan dalam klausa WHERE dengan perbandingan numerik:

SELECT * FROM tb WHERE json_extract(c2, '$.k3.k4') > 5;

Gunakan dalam klausa WHERE dengan perbandingan string (gunakan json_extract_string untuk type safety):

SELECT * FROM tb WHERE json_extract_string(c2, '$.k2') = '1';

json_extract_string

Mengekstraksi nilai pada path yang ditentukan dan mengembalikannya sebagai VARCHAR. Jika nilai pada path bukan string, fungsi mengembalikan NULL.

Contoh

Nilai pada $.name adalah string, sehingga mengembalikan VARCHAR:

SELECT json_extract_string('{"name": "Alice"}', '$.name');
-- Result: Alice (VARCHAR)

Nilai pada $.number adalah angka, bukan string, sehingga fungsi mengembalikan NULL:

SELECT json_extract_string('{"number": 30}', '$.number');
-- Result: NULL

json_extract_long

Mengekstraksi nilai pada path yang ditentukan dan mengembalikannya sebagai LONG. Jika nilai pada path bukan angka, Java Database Connectivity (JDBC) melemparkan error.

Contoh

Nilainya adalah integer, sehingga mengembalikan LONG:

SELECT json_extract_long('{"id": 123456789}', '$.id');
-- Result: 123456789 (LONG)

Nilainya adalah bilangan titik mengambang — JDBC melemparkan error:

SELECT json_extract_long('{"id": 123456.789}', '$.id');
-- Throws an error

Nilainya adalah string numerik — JDBC melemparkan error:

SELECT json_extract_long('{"id": "123456.789"}', '$.id');
-- Throws an error

json_extract_double

Mengekstraksi nilai pada path yang ditentukan dan mengembalikannya sebagai Double. Jika nilai pada path bukan bilangan titik mengambang, JDBC melemparkan error.

Contoh

Nilainya adalah bilangan titik mengambang, sehingga mengembalikan Double:

SELECT json_extract_double('{"id": 12345.56}', '$.id');
-- Result: 12345.56 (Double)

Nilainya adalah integer — fungsi mengembalikannya sebagai Double:

SELECT json_extract_double('{"id": 12345}', '$.id');
-- Result: 12345.0 (Double)

Nilainya adalah string numerik — JDBC melemparkan error:

SELECT json_extract_double('{"id": "123456.789"}', '$.id');
-- Throws an error

json_contains

Mengembalikan 1 jika target_json berisi semua elemen dalam candidate_json, dan 0 jika tidak. Secara opsional, tentukan path untuk memeriksa keberadaan pada lokasi tertentu dalam dokumen.

Mengembalikan NULL jika path yang ditentukan tidak ada atau jika ada parameter yang bernilai NULL.

Sintaksis

json_contains(target_json, candidate_json[, path])

Parameter

Cara memformat `candidate_json`

candidate_json harus berupa string JSON yang valid yang merepresentasikan elemen yang akan diperiksa:

Klasifikasi tipe JSON

• Skalar: String, Angka (Integer/Double), Boolean, Null

• Tipe kompleks: Array, Objek

Aturan Pembatasan

• Skalar hanya dapat berisi skalar lain, bukan array.

• Array dapat berisi elemen (skalar) atau subset array.

Contoh

Persiapkan tabel contoh yang digunakan dalam kueri berikut:

CREATE TABLE test_table (id INT, c1 VARCHAR, data JSON, PRIMARY KEY(id));
UPSERT INTO test_table (id, data) VALUES (1, NULL);
UPSERT INTO test_table (id, data) VALUES (2, '{"skills": ["Java", "Python"]}');
UPSERT INTO test_table (id, data) VALUES (3, '{"skills": ["Go", "C"]}');
UPSERT INTO test_table (id, data) VALUES (4, '{"technical_skills": ["Java", "Go", "Rust"]}');
UPSERT INTO test_table (id, data) VALUES (5, '["Java","C++", "JavaScript"]');
UPSERT INTO test_table (id, data) VALUES (6, '{"skills": "Java"}');
UPSERT INTO test_table (id, data) VALUES (7, '["Java", "C#"]');
UPSERT INTO test_table (id, data) VALUES (8, '{"skills": ["Go", "Rust"]}');

Bentuk dua parameter memeriksa seluruh dokumen (path akar $). Ini menemukan baris di mana dokumen tingkat atas adalah array yang berisi "Java":

SELECT id, data FROM test_table WHERE json_contains(data, '["Java"]') AND id > 0 AND id < 10;

+------+------------------------------+
| id   | data                         |
+------+------------------------------+
|    5 | ["Java","C++", "JavaScript"] |
|    7 | ["Java", "C#"]               |
+------+------------------------------+

Bentuk tiga parameter memeriksa path tertentu. Ini menemukan baris di mana $.skills adalah array yang berisi "Java":

SELECT id, data FROM test_table WHERE json_contains(data, '["Java"]', '$.skills') AND id > 0 AND id < 10;

+------+--------------------------------+
| id   | data                           |
+------+--------------------------------+
|    2 | {"skills": ["Java", "Python"]} |
+------+--------------------------------+

json_contains mengharuskan semua elemen candidate_json hadir. Kueri ini tidak menemukan baris karena tidak ada array $.skills yang berisi kedua "Java" dan "Go":

SELECT id, data FROM test_table WHERE json_contains(data, '["Java","Go"]', '$.skills') AND id > 0 AND id < 10;
-- Empty set

Menggunakan skalar "Java" sebagai kandidat cocok dengan array yang berisi "Java" dan skalar yang sama dengan "Java":

SELECT id, data FROM test_table WHERE json_contains(data, '"Java"', '$.skills') AND id > 0 AND id < 10;

+------+--------------------------------+
| id   | data                           |
+------+--------------------------------+
|    2 | {"skills": ["Java", "Python"]} |
|    6 | {"skills": "Java"}             |
+------+--------------------------------+

Periksa apakah objek berisi pasangan kunci-nilai:

SELECT json_contains('{"a": 1, "b": 2}', '{"a": 1}');
-- Result: 1

Periksa apakah path tertentu berisi nilai tertentu:

SELECT json_contains('{"a": 1, "b": 2}', '1', '$.a');
-- Result: 1

SELECT json_contains('{"a": 1, "b": 2}', '1', '$.b');
-- Result: 0

json_contains_any

Mengembalikan 1 jika target_json berisi setidaknya satu elemen dari candidate_json. Menggunakan sintaksis dan parameter yang sama seperti json_contains.

Contoh

Tanpa path, memeriksa dokumen akar. Dokumen tingkat atas adalah objek, bukan array yang secara langsung berisi "Java" atau "Go":

SELECT json_contains_any('{"skills": ["Java", "Python"]}', '["Java", "Go"]');
-- Result: 0

Dengan path, memeriksa $.skills. Array pada $.skills berisi "Java", yang cocok dengan salah satu elemen kandidat:

SELECT json_contains_any('{"skills": ["Java", "Python"]}', '["Java", "Go"]', '$.skills');
-- Result: 1

Bentuk dua parameter menemukan baris di mana dokumen tingkat atas adalah array dengan elemen apa pun dari ["Java", "Go"]:

SELECT id, data FROM test_table WHERE json_contains_any(data, '["Java", "Go"]') LIMIT 10;

+------+------------------------------+
| id   | data                         |
+------+------------------------------+
|    5 | ["Java","C++", "JavaScript"] |
|    7 | ["Java", "C#"]               |
+------+------------------------------+

Bentuk tiga parameter menemukan baris di mana $.skills berisi elemen apa pun dari daftar kandidat:

SELECT id, data FROM test_table WHERE json_contains_any(data, '["Java", "Go", "Rust"]', '$.skills') LIMIT 10;

+------+--------------------------------+
| id   | data                           |
+------+--------------------------------+
|    2 | {"skills": ["Java", "Python"]} |
|    3 | {"skills": ["Go", "C"]}        |
|    6 | {"skills": "Java"}             |
|    8 | {"skills": ["Go", "Rust"]}     |
+------+--------------------------------+

Periksa apakah path tertentu berisi nilai apa pun dari array kandidat:

SELECT json_contains_any('{"departments": ["Engineering", "Sales"]}', '["Marketing", "Engineering"]', '$.departments');
-- Result: 1

Memahami keberadaan skalar vs. array pada path

Pertimbangkan json_contains_any(data, '["Java"]', '$.technical_skills'). Perilakunya bergantung pada tipe JSON pada $.technical_skills:

• Jika nilainya adalah {"technical_skills": "Java"} (string skalar), fungsi mengembalikan false. Subset dari skalar tidak bisa berupa daftar.

• Jika nilainya adalah {"technical_skills": ["Java"]} atau {"technical_skills": ["Java", "Go"]} (array), fungsi mengembalikan true.

Pertimbangkan json_contains_any(data, '"Java"', '$.technical_skills'). Menggunakan kandidat skalar:

• Jika nilainya adalah {"technical_skills": "Java"} (string skalar yang sama dengan "Java"), fungsi mengembalikan true.

• Jika nilainya adalah {"technical_skills": ["Java"]} atau {"technical_skills": ["Java", "Go"]} (array yang berisi "Java"), fungsi mengembalikan true.

json_set

Menyisipkan atau memperbarui nilai pada path yang ditentukan. Jika path sudah ada, nilai diperbarui. Jika path belum ada, nilai disisipkan. Jika kolom JSON bernilai NULL, hasilnya adalah NULL — objek baru tidak dibuat.

Setara dengan json_insert + json_replace.

Sintaksis

json_set(json_column, 'path', new_value)

Contoh

Perbarui bidang yang sudah ada:

UPSERT INTO test_table (id, data) VALUES (3, '{"name": "Charlie", "age": 30}');
UPDATE test_table SET data = json_set(data, '$.age', 31) WHERE id = 3;
-- Result: {"name": "Charlie", "age": 31}

Sisipkan bidang baru:

UPDATE test_table SET data = json_set(data, '$.department', 'Engineering') WHERE id = 3;
-- Result: {"name": "Charlie", "age": 31, "department": "Engineering"}

Jika kolom JSON bernilai NULL, hasilnya tetap NULL:

UPSERT INTO test_table (id, c1) VALUES (4, 'test');
UPDATE test_table SET data = json_set(data, '$.name', 'David') WHERE id = 4;
-- Result: NULL

json_insert

Menyisipkan nilai pada path yang ditentukan hanya jika path tersebut belum ada. Jika path sudah ada, fungsi tidak melakukan apa-apa.

json_insert tidak menimpa nilai yang sudah ada. Upaya menyisipkan ke bidang yang sudah ada diam-diam tidak berpengaruh — nilai asli dipertahankan.

Sintaksis

json_insert(json_column, 'path', new_value)

Contoh

Sisipkan bidang baru:

UPSERT INTO test_table (id, data) VALUES (5, '{"name": "Eve"}');
UPDATE test_table SET data = json_insert(data, '$.age', 28) WHERE id = 5;
-- Result: {"name": "Eve", "age": 28}

Mencoba menyisipkan ke bidang yang sudah ada tidak berpengaruh — bidang name tetap tidak berubah:

UPDATE test_table SET data = json_insert(data, '$.name', 'New Name') WHERE id = 5;
-- Result: {"name": "Eve", "age": 28}

json_replace

Memperbarui nilai pada path yang ditentukan hanya jika path tersebut sudah ada. Jika path belum ada, fungsi tidak melakukan apa-apa.

Sintaksis

json_replace(json_column, 'path', new_value)

Contoh

Perbarui bidang yang sudah ada:

UPSERT INTO test_table (id, data) VALUES (6, '{"name": "Frank", "age": 35}');
UPDATE test_table SET data = json_replace(data, '$.age', 36) WHERE id = 6;
-- Result: {"name": "Frank", "age": 36}

Mencoba memperbarui bidang yang tidak ada tidak berpengaruh — bidang city tidak ditambahkan:

UPDATE test_table SET data = json_replace(data, '$.city', 'Shanghai') WHERE id = 6;
-- Result: {"name": "Frank", "age": 36}

json_remove

Menghapus nilai pada path yang ditentukan dan mengembalikan dokumen yang telah dimodifikasi. Jika path tidak ada, operasi berhasil tanpa error.

Sintaksis

json_remove(json_column, 'path')

Contoh

Hapus bidang:

UPSERT INTO test_table (id, data) VALUES (7, '{"name": "Grace", "temp_field": "to_remove"}');
UPDATE test_table SET data = json_remove(data, '$.temp_field') WHERE id = 7;
-- Result: {"name": "Grace"}

Menghapus bidang yang tidak ada tidak berpengaruh dan mengembalikan dokumen tanpa perubahan:

UPDATE test_table SET data = json_remove(data, '$.nonexistent') WHERE id = 7;
-- Result: {"name": "Grace"}

json_upsert

Menyisipkan atau memperbarui nilai pada path yang ditentukan. Berperilaku seperti json_set, dengan satu perbedaan utama: jika kolom JSON bernilai NULL, json_upsert membuat objek JSON baru alih-alih mengembalikan NULL.

Sintaksis

json_upsert(json_column, 'path', new_value)

Contoh

Ketika kolom JSON bernilai NULL, json_upsert membuat objek baru:

CREATE TABLE test_table (id INT, c1 VARCHAR, data JSON, PRIMARY KEY(id));
UPSERT INTO test_table (id, c1) VALUES (1, 'test');
UPDATE test_table SET data = json_upsert(data, '$.name', 'Alice') WHERE id = 1;
-- Result: {"name": "Alice"}

Perbarui bidang yang sudah ada:

UPSERT INTO test_table (id, data) VALUES (2, '{"name": "Bob", "age": 25}');
UPDATE test_table SET data = json_upsert(data, '$.age', 26) WHERE id = 2;
-- Result: {"name": "Bob", "age": 26}

Sisipkan bidang baru:

UPDATE test_table SET data = json_upsert(data, '$.city', 'Beijing') WHERE id = 2;
-- Result: {"name": "Bob", "age": 26, "city": "Beijing"}

Fungsi pemeriksaan keterkandungan

Fungsi pembaruan

Sintaksis

CREATE INDEX [index_name]
    ON table_name (json_extract_type(column, json_path))
  [INCLUDE (column_name1, ..., column_namen)]
  [ASYNC]
  [index_options]

Parameter

Fungsi yang didukung

Fungsi berikut mendukung pembuatan indeks berbasis fungsi:

• json_extract_string(json_column, 'path') — mengindeks nilai string

• json_extract_long(json_column, 'path') — mengindeks nilai integer panjang

• json_extract_double(json_column, 'path') — mengindeks nilai titik mengambang

• json_contains(json_column, 'value', 'path') — mengindeks pemeriksaan keberadaan; parameter value dan path harus berupa nilai tetap (statis)

Contoh

Buat indeks untuk mempercepat kueri json_contains pada $.roles:

CREATE INDEX idx_user_role ON test_table (json_contains(data, '"admin"', '$.roles')) INCLUDE(data) SYNC;

Buat indeks pada bidang string di $.address.city:

CREATE INDEX idx_user_city ON test_table (json_extract_string(data, '$.address.city')) INCLUDE(data) SYNC;

Buat indeks pada bidang string tanpa menyertakan kolom tambahan:

CREATE INDEX idx_user_city ON test_table (json_extract_string(data, '$.address.city'));

Buat indeks pada bidang numerik di $.age:

CREATE INDEX idx_user_age ON test_table (json_extract_long(data, '$.age')) INCLUDE(data) SYNC;

Verifikasi bahwa indeks telah dibuat:

SHOW INDEX FROM test_table;

Untuk dukungan indeks pencarian JSON, lihat Gunakan indeks pencarian untuk tipe JSON Lindorm.