Menangani Data Sejenis Array di MySQL dengan Efisien menggunakan Tipe JSON

1. Introduction

Kebutuhan untuk menangani data bergaya array di MySQL

Dalam basis data, data biasanya disimpan dalam desain relasional. Namun, tergantung pada kebutuhan aplikasi, mungkin berguna untuk menyimpan beberapa nilai dalam satu kolom. Dalam kasus seperti itu, struktur data seperti “array” dapat membantu.

Untuk contoh, pertimbangkan kasus berikut:

• Menyimpan beberapa tag yang dipilih oleh pengguna.
• Menyimpan beberapa URL gambar untuk produk.
• Mengkonsolidasikan sejarah atau log ke dalam satu bidang.

Keuntungan menggunakan tipe JSON

MySQL tidak menyediakan “tipe array” langsung, tetapi dengan menggunakan tipe JSON, Anda dapat menangani data bergaya array. Tipe JSON sangat fleksibel dan memberikan keuntungan berikut:

• Mendukung struktur data bersarang.
• Memungkinkan manipulasi data langsung di dalam kueri.
• Mengelola beberapa format data dalam satu bidang.

Dalam artikel ini, kami akan memperkenalkan cara menangani data bergaya array secara efisien di MySQL menggunakan tipe JSON.

2. Dasar menangani array dengan tipe JSON MySQL

Apa itu tipe JSON?

JSON (JavaScript Object Notation) adalah format pertukaran data ringan dan sederhana. MySQL mendukung tipe JSON asli mulai versi 5.7 ke atas, memungkinkan Anda menyimpan dan memanipulasi data berformat JSON langsung di basis data.

Contoh: data berikut dapat disimpan di bidang tipe JSON:

{
  "tags": ["PHP", "MySQL", "JSON"],
  "status": "published"
}

Manfaat dan kasus penggunaan tipe JSON

Keuntungan utama menggunakan tipe JSON adalah:

1. Struktur data fleksibel : Anda dapat menangani data panjang variabel tanpa mengubah skema relasional.
2. Manipulasi data efisien : Gunakan fungsi bawaan MySQL (misalnya JSON_EXTRACT, JSON_ARRAY) untuk dengan mudah memanipulasi data.
3. Desain tanpa skema : Anda tidak perlu sering mengubah skema saat spesifikasi aplikasi berubah.

Kasus penggunaan:

• Menetapkan beberapa kategori ke informasi produk.
• Menyimpan pengaturan pengguna kustom.
• Digunakan dalam aplikasi web yang memproses data JSON bersarang.

3. Operasi dasar pada array JSON

Membuat array JSON

Di MySQL, Anda dapat menggunakan fungsi JSON_ARRAY untuk dengan mudah membuat array berformat JSON. Array berguna ketika Anda ingin menyimpan banyak nilai dalam satu kolom.

Contoh penggunaan

Pada kueri berikut, kami membuat array JSON bernama tags.

SELECT JSON_ARRAY('PHP', 'MySQL', 'JavaScript') AS tags;

Hasil:

["PHP", "MySQL", "JavaScript"]

Contoh terapan

Berikut adalah contoh menyimpan array JSON ke dalam basis data melalui pernyataan INSERT.

CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tags JSON
);

INSERT INTO articles (tags) 
VALUES (JSON_ARRAY('PHP', 'MySQL', 'JavaScript'));

Menarik data dari array JSON

Untuk mengambil data yang disimpan dalam array JSON, Anda menggunakan fungsi JSON_EXTRACT. Ini memungkinkan Anda mengekstrak elemen tertentu di dalam array.

Contoh penggunaan

Dalam contoh berikut, kami mengambil elemen kedua (indeks 0-based) dari array.

SELECT JSON_EXTRACT('["PHP", "MySQL", "JavaScript"]', '$[1]') AS second_tag;

Hasil:

"MySQL"

Mengambil beberapa elemen

Anda juga dapat mengambil beberapa elemen sekaligus.

SELECT JSON_EXTRACT('["PHP", "MySQL", "JavaScript"]', '$[0]', '$[2]') AS extracted_values;

Menambahkan, memperbarui, dan menghapus data

Menambahkan data ke dalam array

Menggunakan fungsi JSON_ARRAY_APPEND, Anda dapat menambahkan data baru ke array yang sudah ada.

SET @tags = '["PHP", "MySQL"]';
SELECT JSON_ARRAY_APPEND(@tags, '$', 'JavaScript') AS updated_tags;

Hasil:

["PHP", "MySQL", "JavaScript"]

Memperbarui data di dalam array

Anda dapat memperbarui elemen tertentu di dalam array menggunakan JSON_SET.

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_SET(@tags, '$[1]', 'Python') AS updated_tags;

Hasil:

["PHP", "Python", "JavaScript"]

Menghapus data dari array

Gunakan JSON_REMOVE untuk menghapus elemen tertentu di dalam array.

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_REMOVE(@tags, '$[1]') AS updated_tags;

Hasil:

["PHP", "JavaScript"]

4. Mencari dan memfilter array JSON

Mencari array yang mengandung data tertentu

Untuk menentukan apakah sebuah array JSON mengandung nilai tertentu Anda menggunakan fungsi JSON_CONTAINS. Fungsi ini mengembalikan apakah array JSON yang ditentukan mengandung nilai yang diberikan.

Contoh penggunaan

Dalam contoh berikut kami memeriksa apakah array JSON mengandung “MySQL”.

SELECT JSON_CONTAINS('["PHP", "MySQL", "JavaScript"]', '"MySQL"') AS is_present;

Hasil:

1  (if present)
0  (if not present)

Contoh terapan: pencarian bersyarat

Jika Anda ingin menanyakan baris di database yang memegang array JSON yang mengandung nilai tertentu, Anda dapat menggunakan JSON_CONTAINS di klausa WHERE.

SELECT * 
FROM articles
WHERE JSON_CONTAINS(tags, '"MySQL"');

Kueri ini mengambil baris di mana kolom tags mencakup “MySQL”.

Mendapatkan panjang array

Untuk mendapatkan jumlah elemen dalam array JSON gunakan fungsi JSON_LENGTH. Ini berguna untuk analisis data atau penyaringan bersyarat.

Contoh penggunaan

Dalam contoh berikut kami mendapatkan jumlah elemen dalam array.

SELECT JSON_LENGTH('["PHP", "MySQL", "JavaScript"]') AS array_length;

Hasil:

3

Contoh praktis: mengekstrak baris yang memenuhi kondisi

Untuk mengekstrak baris di mana jumlah elemen sama dengan atau lebih besar dari nilai tertentu gunakan JSON_LENGTH di klausa WHERE.

SELECT * 
FROM articles
WHERE JSON_LENGTH(tags) >= 2;

Kueri ini memilih baris di mana kolom tags berisi dua atau lebih elemen.

Contoh praktis dengan kueri bersyarat

Anda dapat menggabungkan beberapa kondisi untuk pencarian yang lebih maju. Kueri berikut menemukan baris di mana array tags mengandung “JavaScript” dan memiliki tiga atau lebih elemen.

SELECT * 
FROM articles
WHERE JSON_CONTAINS(tags, '"JavaScript"') 
  AND JSON_LENGTH(tags) >= 3;

5. Kasus penggunaan praktis: belajar melalui skenario nyata untuk array JSON

Cara menyimpan kategori produk sebagai array JSON

Di situs e‑commerce produk dapat masuk ke beberapa kategori. Dalam kasus ini menggunakan array JSON untuk menyimpan informasi kategori dapat menjadi efisien.

Contoh: menyimpan data kategori produk

Berikut contoh pembuatan tabel dengan kolom JSON bernama categories dan menyimpan beberapa kategori.

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    categories JSON
);

INSERT INTO products (name, categories) 
VALUES ('Laptop', JSON_ARRAY('Electronics', 'Computers')),
       ('Smartphone', JSON_ARRAY('Electronics', 'Mobile Devices'));

Struktur data ini memungkinkan penyimpanan yang ringkas meskipun produk masuk ke beberapa kategori.

Mengeksekusi kueri produk yang masuk ke kategori tertentu

Dengan memanfaatkan tipe JSON, Anda dapat dengan mudah mencari produk yang masuk ke kategori tertentu.

Contoh kueri

Kueri berikut menemukan semua produk di kategori “Electronics”.

SELECT name 
FROM products
WHERE JSON_CONTAINS(categories, '"Electronics"');

Hasil:

Laptop
Smartphone

Kueri ini memungkinkan Anda mengambil daftar produk secara fleksibel berdasarkan kategori.

Contoh: penyaringan berdasarkan kisaran harga

Mari kita lihat bagaimana menyimpan informasi harga dalam JSON dan mengeksekusi kueri produk berdasarkan kisaran harga.

Contoh data

Berikut kami menyimpan informasi harga produk per item menggunakan tipe JSON.

CREATE TABLE products_with_prices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    details JSON
);

INSERT INTO products_with_prices (name, details)
VALUES ('Laptop', '{"price": 150000, "categories": ["Electronics", "Computers"]}'),
       ('Smartphone', '{"price": 80000, "categories": ["Electronics", "Mobile Devices"]}');

Contoh kueri

Untuk mencari produk dengan harga 100.000 atau lebih, Anda menggunakan JSON_EXTRACT.

SELECT name 
FROM products_with_prices
WHERE JSON_EXTRACT(details, '$.price') >= 100000;

Hasil:

Laptop

Menggunakan JSON_TABLE untuk ekspansi dan contoh query

Saat Anda ingin mengquery data JSON dalam bentuk relasional, Anda dapat menggunakan JSON_TABLE. Fungsi ini memungkinkan Anda mengekspansi array JSON sebagai tabel virtual.

Penggunaan contoh

Berikut adalah contoh di mana array JSON diekspansi dan setiap kategori ditampilkan sebagai baris terpisah.

SELECT * 
FROM JSON_TABLE(
    '["Electronics", "Computers", "Mobile Devices"]',
    '$[*]' COLUMNS(
        category_name VARCHAR(100) PATH '$'
    )
) AS categories_table;

Hasil:

category_name
--------------
Electronics
Computers
Mobile Devices

6. Peringatan saat menggunakan tipe JSON

Titik optimisasi kinerja

Meskipun tipe JSON sangat fleksibel, tanpa desain yang tepat dapat berdampak negatif pada kinerja database. Berikut adalah poin-poin penting untuk optimisasi kinerja.

1. Penggunaan indeks

Di MySQL, Anda tidak dapat secara langsung menetapkan indeks pada kolom JSON itu sendiri, tetapi Anda dapat membuat kolom virtual dan menambahkan indeks pada kunci tertentu.

Contoh: membuat indeks melalui kolom virtual

Dalam contoh ini kita menambahkan indeks pada kunci price di dalam data JSON.

ALTER TABLE products_with_prices
ADD COLUMN price INT AS (JSON_EXTRACT(details, '$.price')) STORED,
ADD INDEX idx_price (price);

Dengan menggunakan kolom virtual, Anda dapat meningkatkan kinerja pencarian pada data tipe JSON secara signifikan.

2. Hindari struktur JSON yang terlalu kompleks

Struktur JSON yang dalam memengaruhi keterbacaan query dan kinerja. Saat merancang data, gunakan struktur JSON yang sesederhana mungkin.

Contoh baik:

{
  "categories": ["Electronics", "Computers"],
  "price": 150000
}

Struktur yang harus dihindari:

{
  "product": {
    "details": {
      "price": 150000,
      "categories": ["Electronics", "Computers"]
    }
  }
}

Cara menggunakan indeks secara efektif

Saat menggunakan kolom virtual untuk pembuatan indeks, poin-poin berikut harus dipertimbangkan:

1. Kolom virtual harus STORED.
2. Gunakan JSON_EXTRACT untuk mengekstrak kunci tertentu ke kolom virtual.

Misalnya, untuk mengekstrak nilai kunci categories dan menambahkan indeks:

ALTER TABLE products
ADD COLUMN main_category VARCHAR(255) AS (JSON_EXTRACT(categories, '$[0]')) STORED,
ADD INDEX idx_main_category (main_category);

Pentingnya validasi data

Meskipun data tipe JSON fleksibel, juga berisiko menyimpan format yang salah. Untuk menjaga integritas data, gunakan pendekatan berikut.

1. Gunakan batasan CHECK

Mulai MySQL 8.0, Anda dapat menggunakan batasan CHECK untuk memvalidasi struktur atau konten data JSON.

ALTER TABLE products_with_prices
ADD CONSTRAINT check_price CHECK (JSON_EXTRACT(details, '$.price') >= 0);

2. Validasi di tingkat aplikasi

Saat memasukkan data, Anda harus memvalidasi format JSON di tingkat aplikasi. Di bahasa pemrograman seperti PHP atau Python, Anda dapat menggunakan perpustakaan standar untuk memvalidasi JSON.

7. Pertanyaan yang Sering Diajukan tentang penggunaan data tipe array di MySQL

P1: Apakah MySQL menyediakan tipe array?

J1: MySQL tidak memiliki tipe “array” secara langsung. Namun Anda dapat menangani data tipe array dengan menggunakan tipe JSON. Dengan menggunakan tipe JSON, Anda dapat menyimpan banyak nilai di satu kolom dan memanipulasinya melalui query.

Contoh:

SELECT JSON_ARRAY('value1', 'value2', 'value3') AS example_array;

Hasil:

["value1", "value2", "value3"]

P2: Bisakah Anda menetapkan indeks pada data tipe JSON?

J2: Anda tidak dapat langsung menambahkan indeks pada tipe JSON itu sendiri. Namun Anda dapat mengekstrak kunci atau nilai tertentu sebagai kolom virtual dan kemudian menambahkan indeks pada kolom tersebut.

Contoh:

ALTER TABLE products_with_prices
ADD COLUMN price INT AS (JSON_EXTRACT(details, '$.price')) STORED,
ADD INDEX idx_price (price);

Ini memungkinkan pencarian yang efisien untuk nilai di dalam data JSON.

Q3: Apakah ada batas ukuran untuk data JSON?

A3: Tipe JSON MySQL dapat menyimpan hingga 4 GB data. Namun menggunakan data JSON sangat besar dapat menurunkan performa, sehingga desain data yang tepat sangat penting.

Rekomendasi

• Simpan hanya data minimum yang diperlukan.
• Hindari struktur JSON yang terlalu bersarang.

Q4: Bagaimana cara memperbarui elemen tertentu dalam array JSON?

A4: Anda memperbarui elemen tertentu di dalam array JSON menggunakan fungsi JSON_SET.

Contoh:

SET @tags = '["PHP", "MySQL", "JavaScript"]';
SELECT JSON_SET(@tags, '$[1]', 'Python') AS updated_tags;

Hasil:

["PHP", "Python", "JavaScript"]

Q5: Perbandingan antara tipe JSON dan desain tabel tradisional

A5: Tipe JSON menawarkan fleksibilitas tinggi namun juga berbeda sifatnya dari desain relasional tradisional.

8. Ringkasan

Manfaat menangani data berbentuk array dengan tipe JSON di MySQL

Artikel ini menjelaskan tipe JSON untuk menangani data berbentuk array di MySQL. Poin-poin utama yang dibahas di sini adalah:

1. Mengapa menggunakan tipe JSON
   MySQL tidak memiliki tipe array langsung, tetapi dengan menggunakan tipe JSON Anda dapat menyimpan banyak nilai di satu kolom dan melakukan operasi data yang fleksibel.

2. Operasi JSON dasar

• Belajar bagaimana membuat array JSON, mengekstrak data, memperbarui dan menghapus nilai.
• Menggunakan fungsi seperti JSON_ARRAY, JSON_EXTRACT, dan JSON_SET untuk memanipulasi data berbentuk array secara efisien.

3. Pencarian dan penyaringan

• Menggunakan JSON_CONTAINS untuk mencari nilai tertentu di dalam array JSON.
• Menggunakan JSON_LENGTH untuk mendapatkan panjang array dan melakukan penyaringan bersyarat.

4. Kasus penggunaan praktis
   Kita belajar skenario aplikasi konkret seperti mengelola kategori produk dan penyaringan berdasarkan harga.

5. Peringatan dan optimasi

• Membahas cara mengindeks data JSON melalui kolom virtual dan pentingnya memvalidasi data JSON.

Langkah selanjutnya untuk memanfaatkan tipe JSON

Dengan menggunakan tipe JSON di MySQL Anda dapat mengaktifkan manajemen data yang lebih fleksibel dibandingkan desain database relasional tradisional. Namun desain yang tepat dan pertimbangan performa sangat penting.

Topik yang harus dipelajari selanjutnya:

• Menggunakan indeks komposit
  Mendesain indeks yang menggabungkan kolom tipe JSON dengan kolom biasa.

• Fungsi JSON tingkat lanjut
  Menggunakan JSON_MERGE, JSON_OBJECT, dan fungsi lainnya untuk operasi yang lebih kompleks.

• Manipulasi data tingkat aplikasi
  Manipulasi data JSON MySQL secara efisien menggunakan bahasa seperti PHP atau Python.

Ringkasan akhir

Melalui artikel ini Anda telah belajar bagaimana menangani data berbentuk array secara efisien menggunakan tipe JSON di MySQL. Dengan menerapkan pengetahuan ini Anda dapat merancang sistem database yang lebih fleksibel dan skalabel.