Cara Mengelola Data Array di MySQL dengan JSON (Panduan Lengkap dengan Contoh)

1. Pendahuluan

Kebutuhan untuk Menangani Data Array di MySQL

Database biasanya menyimpan data berdasarkan prinsip desain relasional. Namun, tergantung pada kebutuhan aplikasi, ada kasus di mana Anda ingin menyimpan beberapa nilai dalam satu kolom. Dalam situasi seperti itu, struktur data yang mirip dengan “array” menjadi berguna.

Sebagai contoh, pertimbangkan skenario berikut:

• Menyimpan beberapa tag yang dipilih oleh pengguna.
• Menyimpan beberapa URL gambar untuk sebuah produk.
• Menggabungkan riwayat atau log ke dalam satu bidang.

Manfaat Menggunakan Tipe Data JSON

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

• Mendukung struktur data bersarang.
• Memungkinkan manipulasi data yang mudah dalam kueri.
• Memungkinkan pengelolaan banyak format data dalam satu bidang.

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

2. Pengetahuan Dasar tentang Menangani Array dengan MySQL JSON

Apa Itu Tipe Data JSON?

JSON (JavaScript Object Notation) adalah format pertukaran data yang ringan dan sederhana. Di MySQL, dukungan JSON native diperkenalkan pada versi 5.7 dan selanjutnya, memungkinkan Anda menyimpan dan memanipulasi data berformat JSON secara langsung di dalam basis data.

Contoh: Di bawah ini adalah contoh data yang dapat disimpan dalam kolom JSON.

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

Keuntungan dan Kasus Penggunaan Tipe Data JSON

Manfaat utama menggunakan tipe JSON adalah sebagai berikut:

1. Struktur Data Fleksibel : Anda dapat menangani data dengan panjang variabel tanpa mengubah skema relasional.
2. Manipulasi Data Efisien : Anda dapat dengan mudah memanipulasi data menggunakan fungsi khusus MySQL (mis. JSON_EXTRACT, JSON_ARRAY).
3. Desain Tanpa Skema : Tidak perlu sering mengubah skema ketika kebutuhan aplikasi berubah.

Contoh kasus penggunaan:

• Menetapkan beberapa kategori pada informasi produk.
• Menyimpan pengaturan khusus pengguna.
• Menggunakan data JSON bersarang dalam aplikasi web.

3. Operasi Dasar JSON Array

Membuat JSON Array

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

Contoh

Kueri berikut membuat JSON array bernama tags.

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

Hasil:

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

Contoh Praktis

Contoh berikut menunjukkan cara menyimpan JSON array ke dalam basis data menggunakan pernyataan INSERT.

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

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

Mengekstrak Data dari JSON Array

Untuk mengambil data yang disimpan dalam JSON array, gunakan fungsi JSON_EXTRACT. Fungsi ini memungkinkan Anda mengekstrak elemen tertentu dari array dengan mudah.

Contoh

Contoh berikut mengambil elemen kedua dalam array (indeks dimulai dari 0).

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;

Menambah, Memperbarui, dan Menghapus Data

Menambah Data ke dalam Array

Anda dapat menggunakan fungsi JSON_ARRAY_APPEND untuk menambahkan data baru ke dalam array yang sudah ada.

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

Hasil:

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

Memperbarui Data dalam Array

Anda dapat memperbarui elemen tertentu dalam array menggunakan fungsi JSON_SET.

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

Hasil:

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

Menghapus Data dari Array

Anda dapat menghapus elemen tertentu dari array menggunakan fungsi JSON_REMOVE.

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

Hasil:

["PHP", "JavaScript"]

4. Mencari dan Menyaring Array JSON

Mencari Array yang Mengandung Data Spesifik

Untuk memeriksa apakah sebuah array JSON berisi data tertentu, gunakan fungsi JSON_CONTAINS. Fungsi ini menentukan apakah nilai yang ditentukan ada di dalam array JSON.

Contoh

Contoh berikut memeriksa apakah array JSON berisi “MySQL.”

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

Hasil:

1  (if present)
0  (if not present)

Contoh Praktis: Pencarian Bersyarat

Untuk mencari baris yang berisi nilai tertentu dalam array JSON pada tabel basis data, gunakan JSON_CONTAINS di klausa WHERE.

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

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

Mendapatkan Panjang Array

Untuk mendapatkan jumlah elemen dalam array JSON, gunakan fungsi JSON_LENGTH. Fungsi ini mengembalikan jumlah elemen dalam array dan berguna untuk analisis data serta logika bersyarat.

Contoh

Contoh berikut mengambil jumlah elemen dalam array.

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

Hasil:

3

Contoh Praktis: Mengekstrak Baris yang Memenuhi Kondisi Tertentu

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

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

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

Contoh Kueri Bersyarat Lanjutan

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

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

5. Contoh Praktis: Menggunakan Array JSON dalam Kasus Penggunaan Nyata

Cara Menyimpan Kategori Produk sebagai Array JSON

Di situs e-commerce dan sistem serupa, sebuah produk dapat termasuk dalam beberapa kategori. Dalam kasus tersebut, Anda dapat menyimpan informasi kategori secara efisien menggunakan array JSON.

Contoh: Menyimpan Data Kategori Produk

Berikut adalah contoh pembuatan kolom JSON bernama categories dalam tabel produk 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 ini menjaga data tetap ringkas meskipun sebuah produk termasuk dalam banyak kategori.

Kueri untuk Mengekstrak Produk dalam Kategori Tertentu

Dengan memanfaatkan tipe data JSON, Anda dapat dengan mudah mencari produk yang termasuk dalam kategori tertentu.

Contoh Kueri

Kueri berikut mengambil semua produk dalam kategori “Electronics”.

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

Hasil:

Laptop
Smartphone

Kueri ini memudahkan pengambilan daftar produk berdasarkan kategori secara fleksibel.

Contoh: Menyaring Berdasarkan Rentang Harga

Mari kita lihat cara menyimpan data JSON yang mencakup informasi harga dan kemudian mencari produk berdasarkan rentang harga.

Data Contoh

Contoh berikut menyimpan informasi harga per produk 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 Query

Untuk mencari produk dengan harga 100.000 atau lebih, gunakan fungsi JSON_EXTRACT.

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

Hasil:

Laptop

Memperluas JSON dengan JSON_TABLE dan Contoh Query

Jika Anda ingin menanyakan data JSON dalam format relasional, fungsi JSON_TABLE sangat berguna. Fungsi ini memungkinkan Anda memperluas array JSON menjadi tabel virtual.

Contoh

Contoh berikut memperluas array JSON dan menampilkan setiap kategori 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. Pertimbangan Penting Saat Menggunakan Tipe Data JSON

Tips Optimasi Kinerja

Meskipun tipe JSON fleksibel, desain yang buruk dapat berdampak negatif pada kinerja basis data. Berikut adalah poin-poin optimasi utama.

1. Menggunakan Indeks

Di MySQL, Anda tidak dapat membuat indeks langsung pada kolom JSON itu sendiri, tetapi Anda dapat membuat kolom terhasil (generated column) dan mengindeks kunci tertentu.

Contoh: Membuat Indeks Menggunakan Kolom Terhasil

Dalam contoh berikut, kunci price dalam data JSON digunakan sebagai target indeks.

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

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

2. Hindari Struktur JSON yang Terlalu Kompleks

Struktur JSON yang sangat bersarang dapat mengurangi keterbacaan query dan kinerja. Saat merancang data, pilih struktur JSON yang paling sederhana.

Contoh yang baik:

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

Contoh yang harus dihindari:

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

Cara Memanfaatkan Indeks

Saat mengindeks menggunakan kolom terhasil, perhatikan poin-poin berikut:

1. Kolom terhasil harus STORED.
2. Gunakan fungsi JSON_EXTRACT untuk mengekstrak kunci tertentu sebagai kolom terhasil.

Sebagai contoh, untuk mengekstrak elemen pertama dari kunci categories dan membuat indeks, lakukan hal berikut.

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

Karena data JSON fleksibel, lebih mudah pula menyimpan data dalam format yang salah. Untuk menjaga integritas data, gunakan pendekatan berikut.

1. Gunakan Constraint CHECK

Di MySQL 8.0 dan setelahnya, Anda dapat memvalidasi struktur dan konten JSON menggunakan constraint CHECK.

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

2. Validasi pada Tingkat Aplikasi

Saat memasukkan data, disarankan untuk memvalidasi format JSON di sisi aplikasi. Bahasa pemrograman seperti PHP dan Python dapat memvalidasi JSON menggunakan pustaka standar mereka.

7. Pertanyaan yang Sering Diajukan tentang Penggunaan Array di MySQL

Q1: Apakah MySQL memiliki tipe data array?

A1: MySQL tidak memiliki “tipe data array” secara langsung. Namun, dengan menggunakan tipe JSON, Anda dapat menangani struktur data mirip array. Dengan tipe JSON, Anda dapat menyimpan beberapa nilai dalam satu kolom dan memanipulasinya melalui kueri.

Example:

SELECT JSON_ARRAY('Value 1', 'Value 2', 'Value 3') AS array_example;

Hasil:

["Value 1", "Value 2", "Value 3"]

Q2: Bisakah Anda membuat indeks pada data JSON?

A2: Anda tidak dapat membuat indeks secara langsung pada tipe JSON itu sendiri. Namun, Anda dapat mengekstrak kunci atau nilai tertentu ke dalam kolom yang dihasilkan dan membuat indeks pada kolom yang dihasilkan tersebut.

Example:

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

Ini memungkinkan Anda mencari nilai di dalam data JSON secara efisien.

Q3: Apakah ada batas ukuran untuk data JSON?

A3: Tipe JSON MySQL dapat menyimpan hingga 4 GB data. Namun, menggunakan dokumen JSON yang sangat besar dapat menurunkan kinerja, jadi Anda harus merancang data Anda dengan hati-hati.

Rekomendasi:

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

Q4: Bagaimana cara memperbarui elemen tertentu di dalam array JSON?

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

Example:

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

Hasil:

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

Q5: Membandingkan Tipe JSON vs. Desain Tabel Normal

A5: Meskipun tipe JSON sangat fleksibel, ia memiliki karakteristik yang berbeda dibandingkan dengan desain basis data relasional tradisional.

8. Ringkasan

Manfaat Menggunakan Tipe Data JSON untuk Operasi Array di MySQL

Dalam artikel ini, kami menjelaskan tipe data JSON, yang berguna saat bekerja dengan data mirip array di MySQL. Berikut adalah ringkasan poin-poin utama yang dibahas:

1. Mengapa Menggunakan Tipe JSON MySQL tidak memiliki tipe array langsung, tetapi dengan menggunakan tipe JSON, Anda dapat menyimpan beberapa nilai dalam satu kolom dan mencapai manipulasi data yang fleksibel.
2. Operasi JSON Dasar

• Kami membahas cara membuat array JSON, mengekstrak data, memperbarui data, dan menghapus data.
• Dengan menggunakan fungsi-fungsi praktis seperti JSON_ARRAY, JSON_EXTRACT, dan JSON_SET, Anda dapat memanipulasi data array secara efisien.

1. Pencarian dan Penyaringan

• Cara mencari data yang berisi nilai tertentu menggunakan JSON_CONTAINS.
• Cara mengambil jumlah elemen dengan JSON_LENGTH dan melakukan penyaringan bersyarat.

1. Contoh Praktis Melalui contoh penggunaan dunia nyata seperti mengelola kategori produk dan menyaring berdasarkan harga, kami mempelajari cara konkret menerapkan array JSON dalam aplikasi.
2. Pertimbangan dan Optimasi

• Kami menjelaskan cara menyiapkan indeks menggunakan kolom yang dihasilkan dan menekankan pentingnya memvalidasi data JSON.

Langkah Selanjutnya Saat Menggunakan Tipe Data JSON

Dengan menggunakan tipe JSON di MySQL, Anda dapat mengelola data lebih fleksibel dibandingkan desain basis data relasional tradisional. Namun, pertimbangan desain yang baik dan kinerja sangat penting.

Topik yang Akan Dipelajari Selanjutnya:

• Menggunakan Indeks Komposit Desain indeks yang menggabungkan data JSON dengan kolom normal.
• Menggunakan Fungsi JSON Lanjutan Melakukan operasi yang lebih kompleks dengan fungsi seperti JSON_MERGE dan JSON_OBJECT.
• Penanganan Data di Tingkat Aplikasi Cara memanipulasi data JSON MySQL secara efisien menggunakan PHP atau Python.

Ringkasan

Melalui artikel ini, Anda sekarang seharusnya memahami cara menangani data mirip array secara efisien menggunakan tipe data JSON MySQL. Dengan menerapkan pengetahuan ini, Anda dapat merancang basis data yang lebih fleksibel dan skalabel.