Memproses JSON dalam SQLite

Setiap produk punya atribut berbeda: kopi mencatat asal daerah dan profil rasa, teh menyimpan suhu seduh ideal, gula punya tingkat kepadatan. Memaksa semua variasi itu ke kolom-kolom tetap menghasilkan tabel bertabur NULL. SQLite menawarkan jalan tengah: kolom terstruktur tetap menampung data inti, sementara satu kolom tambahan menyimpan atribut lentur sebagai dokumen JSON. Sejak SQLite 3.38, keluarga fungsi JSON sudah terpasang bawaan di pustaka inti (sebelumnya ekstensi opsional bernama JSON1), sehingga kita bisa memfilter, memodifikasi, bahkan mengurai JSON menjadi baris tabel tanpa alat bantu apa pun.

Data Contoh #

Tabel produk menggabungkan kolom terstruktur dengan kolom metadata bertipe TEXT yang berisi JSON:

CREATE TABLE produk (
  id INTEGER PRIMARY KEY,
  nama TEXT NOT NULL,
  harga REAL NOT NULL,
  metadata TEXT
);

INSERT INTO produk (id, nama, harga, metadata) VALUES
  (1, 'Kopi Gayo', 45000,
   '{"asal": "Aceh", "berat_g": 250, "tag": ["arabika", "single origin"]}'),
  (2, 'Teh Melati', 18000,
   '{"asal": "Bandung", "berat_g": 100, "tag": ["teh", "aroma"]}'),
  (3, 'Gula Aren', 25000, NULL);

Validasi dan Format dengan json() dan json_valid() #

json_valid() memeriksa apakah sebuah teks adalah JSON yang sah — hasil 1 bila valid, 0 bila tidak:

SELECT json_valid('{"a": 1}'), json_valid('{a: 1}');

Output yang Dihasilkan:

1|0

json() memvalidasi masukan sekaligus menormalkannya: spasi berlebih dibuang dan setiap nilai disusun ulang dalam bentuk paling ringkas. Urutan key asli dipertahankan:

SELECT json('{"b": [2, 3], "a": 1}');

Output yang Dihasilkan:

{"b":[2,3],"a":1}

Membaca Nilai: json_extract dan Operator -> serta -» #

json_extract() menambat satu nilai dari dokumen lewat path expression. Tanda $ berarti akar dokumen, $.asal berarti key asal, dan $.tag[0] berarti elemen pertama dari array tag:

SELECT id, nama, json_extract(metadata, '$.asal') AS asal,
       json_extract(metadata, '$.tag[0]') AS tag_pertama
FROM produk WHERE id <= 2 ORDER BY id;

Output yang Dihasilkan:

1|Kopi Gayo|Aceh|arabika
2|Teh Melati|Bandung|teh

Sejak SQLite 3.38 tersedia dua operator yang lebih ringkas. Operator -> mengembalikan representasi JSON, sedangkan ->> mengembalikan nilai SQL. Perbedaannya paling terlihat pada string: -> menyertakan tanda kutip, ->> tidak:

SELECT '{"nama": "Budi"}' -> '$.nama', '{"nama": "Budi"}' ->> '$.nama';

Output yang Dihasilkan:

"Budi"|Budi

Untuk angka keduanya sama-sama menghasilkan nilai numerik:

SELECT metadata -> '$.berat_g', metadata ->> '$.berat_g' FROM produk WHERE id = 1;

Output yang Dihasilkan:

250|250

Dua fungsi pembantu melengkapi pembacaan: json_type() mengembalikan tipe sebuah elemen (object, array, text, integer, dan seterusnya), sedangkan json_array_length() menghitung isi array:

SELECT json_array_length(metadata, '$.tag') AS jumlah_tag, json_type(metadata, '$.berat_g') AS tipe_berat
FROM produk WHERE id = 2;

Output yang Dihasilkan:

2|integer

Membangun dan Memodifikasi JSON #

json_array() dan json_object() merakit dokumen dari nilai SQL; keduanya bisa saling bersarang:

SELECT json_array(1, 'dua', json_array(3, 4));
SELECT json_object('nama', 'Budi', 'aktif', 1, 'hobi', json_array('membaca'));

Output yang Dihasilkan:

[1,"dua",[3,4]]
{"nama":"Budi","aktif":1,"hobi":["membaca"]}

Untuk mengubah dokumen, SQLite menyediakan tiga fungsi yang tampak mirip namun berbeda nasib bagi key yang sudah ada atau belum ada:

SELECT json_set('{"a": 1}', '$.b', 2);
SELECT json_insert('{"a": 1}', '$.a', 100, '$.b', 2);
SELECT json_replace('{"a": 1}', '$.a', 100, '$.b', 2);

Output yang Dihasilkan:

{"a":1,"b":2}
{"a":1,"b":2}
{"a":100}

json_patch() menerapkan sematan format RFC-7396: key bernilai null dihapus, key lain diganti atau ditambahkan:

SELECT json_patch('{"a": 1, "b": 2}', '{"b": null, "c": 3}');

Output yang Dihasilkan:

{"a":1,"c":3}

Menelusuri JSON dengan json_each dan json_tree #

json_each() adalah table-valued function: ia menghasilkan tabel sementara berisi satu baris per elemen. Array memberi kolom key berisi indeks, sedangkan object memberi kolom key berisi nama field:

SELECT key, value, type FROM json_each('["arabika", "single origin"]');
SELECT key, value FROM json_each('{"a": 1, "b": 2}');

Output yang Dihasilkan:

0|arabika|text
1|single origin|text
a|1
b|2

Karena berperilaku seperti tabel, json_each() bisa ikut JOIN — inilah cara meledakkan array tag menjadi baris-baris data:

SELECT p.nama, j.value AS tag
FROM produk p, json_each(p.metadata, '$.tag') j
ORDER BY p.id, j.key;

Output yang Dihasilkan:

Kopi Gayo|arabika
Kopi Gayo|single origin
Teh Melati|teh
Teh Melati|aroma

Mari kita bedah query di atas baris per baris:

1. Baris 2 (FROM produk p, ...): Tanda koma adalah cross join antara tabel produk dan hasil json_each(); fungsi ini dipanggil sekali per baris produk dengan parameter dari baris tersebut.
2. Baris 2 (json_each(p.metadata, '$.tag')): Setiap elemen array $.tag menjadi satu baris; produk dengan metadata NULL, seperti Gula Aren, menghasilkan nol baris sehingga otomatis hilang dari hasil.
3. Baris 3 (j.value): Kolom value memuat isi elemen — tag yang ingin kita daftarkan.
4. Baris 3 (ORDER BY p.id, j.key): Urutan produk mengikuti id dan urutan tag mengikuti key (indeks array), sehingga output deterministik.

json_tree() adalah versi rekursif yang menjelajah seluruh kedalaman dokumen. Kolom fullkey menunjukkan lintasan menuju setiap nilai daun:

SELECT fullkey, value FROM json_tree('{"a": 1, "b": [2, 3]}')
WHERE type NOT IN ('object', 'array');

Output yang Dihasilkan:

$.a|1
$.b[0]|2
$.b[1]|3

Studi Kasus: Memperbarui Metadata #

Fungsi modifikasi bekerja pada nilai, jadi pola pembaruan kolom adalah menugaskan kembali hasilnya ke kolom yang sama:

UPDATE produk SET metadata = json_set(metadata, '$.berat_g', 300) WHERE id = 1;
SELECT nama, metadata ->> '$.berat_g' AS berat_baru FROM produk WHERE id = 1;

Output yang Dihasilkan:

Kopi Gayo|300

JSON menuntaskan kebutuhan data lentur. Masalah berikutnya berpindah ke penulisan: bagaimana bila INSERT menabrak data yang sudah ada — dijawab oleh Chapter UPSERT dan Penanganan Konflik.