JSON SQLite: json_extract, json_set, dan json_each

SQLite Tidak Punya Tipe JSON, dan Itu Tidak Masalah

SQLite tidak punya tipe kolom khusus untuk JSON. JSON dimasukkan ke kolom TEXT biasa, dan sekumpulan fungsi bawaan, yang secara keseluruhan disebut ekstensi JSON1, tahu cara mem-parse, meng-query, dan mengubahnya. JSON1 sudah ada di setiap build SQLite modern, jadi tidak ada yang perlu diinstal.

Model berpikirnya: simpan dokumen sebagai teks, lalu pakai fungsi untuk melihat isinya.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    data TEXT
);

INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com", "tags": ["admin", "beta"]}'),
    ('{"name": "Boris", "email": "boris@example.com", "tags": ["beta"]}');

SELECT id, data FROM users;

Dua baris, masing-masing menyimpan dokumen JSON di kolom teks biasa. Sekarang kita butuh cara untuk menjangkau isi dokumen-dokumen itu.

Mengambil Field dengan json_extract dan ->>

json_extract(column, path) mengambil sebuah nilai dari dokumen JSON. Path diawali $ (root) dan memakai .field untuk key objek serta [i] untuk indeks array.

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com", "tags": ["admin", "beta"]}'),
    ('{"name": "Boris", "email": "boris@example.com", "tags": ["beta"]}');

SELECT
    json_extract(data, '$.name')     AS name,
    json_extract(data, '$.tags[0]')  AS first_tag
FROM users;

Menulis json_extract(data, '$.name') di mana-mana cepat terasa melelahkan, jadi SQLite memberimu dua operator:

• -> mengembalikan nilai dalam bentuk JSON (string kembali beserta tanda kutipnya).
• ->> mengembalikan nilai SQL (teks atau angka, tanpa tanda kutip).

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "tags": ["admin", "beta"]}');

SELECT
    data -> '$.name'      AS name_json,
    data ->> '$.name'     AS name_text,
    data ->> '$.tags[0]'  AS first_tag
FROM users;

name_json kembali sebagai "Ada" (masih JSON), sedangkan name_text sebagai Ada. Pakai ->> jika kamu ingin nilai untuk dibandingkan atau ditampilkan. Pakai -> jika hasilnya akan kamu masukkan ke fungsi JSON lain.

Memfilter Berdasarkan Field JSON

Begitu kamu bisa mengambil nilai, kamu bisa memfilter. Ekspresinya ditaruh di klausa WHERE seperti biasa:

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com", "active": 1}'),
    ('{"name": "Boris", "email": "boris@example.com", "active": 0}'),
    ('{"name": "Cleo", "email": "cleo@example.com", "active": 1}');

SELECT data ->> '$.name' AS name
FROM users
WHERE data ->> '$.active' = 1;

Ini berfungsi, tapi untuk tabel yang ukurannya lumayan, query ini lambat, karena setiap baris harus di-parse untuk menghitung kondisinya. Sebentar lagi kita akan memperbaikinya dengan index.

Membangun JSON: json_object dan json_array

Ke arah sebaliknya, kamu bisa menyusun JSON di dalam query:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, email TEXT);
INSERT INTO users (name, email) VALUES
    ('Ada', 'ada@example.com'),
    ('Boris', 'boris@example.com');

SELECT json_object(
    'id', id,
    'name', name,
    'email', email
) AS user_json
FROM users;

json_object('k1', v1, 'k2', v2, ...) membangun sebuah objek. json_array(v1, v2, ...) membangun sebuah array. Keduanya praktis untuk merakit respons API langsung di SQL, dan keduanya bisa disarangkan tanpa masalah:

SELECT json_object(
    'user', json_object('name', 'Ada', 'id', 1),
    'roles', json_array('admin', 'beta')
) AS payload;

Memperbarui JSON: json_set, json_insert, json_replace

Tiga fungsi yang mirip satu sama lain mengubah dokumen JSON dan mengembalikan versi barunya:

• json_set(doc, path, value): mengisi path itu, membuatnya jika belum ada, dan menimpanya jika sudah ada.
• json_insert(doc, path, value): hanya menambahkan jika path itu belum ada.
• json_replace(doc, path, value): hanya memperbarui jika path itu sudah ada.

Fungsi-fungsi ini tidak mengubah dokumen di tempat. Mereka mengembalikan dokumen baru, yang biasanya kamu tulis kembali dengan UPDATE:

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "email": "ada@example.com"}');

UPDATE users
SET data = json_set(data, '$.email', 'ada@new.com', '$.verified', 1)
WHERE id = 1;

SELECT data FROM users;

Perhatikan bahwa json_set menerima beberapa pasangan path/nilai dalam satu panggilan. Untuk menghapus sebuah key, pakai json_remove(doc, path).

Memecah Array dengan json_each

json_each adalah fungsi bernilai tabel: fungsi ini menerima array (atau objek) JSON dan mengembalikan satu baris untuk setiap elemennya. Dengan begitu, "cari pengguna yang punya tag admin", yang canggung di SQL biasa, berubah menjadi join biasa:

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);
INSERT INTO users (data) VALUES
    ('{"name": "Ada", "tags": ["admin", "beta"]}'),
    ('{"name": "Boris", "tags": ["beta"]}'),
    ('{"name": "Cleo", "tags": ["admin"]}');

SELECT u.data ->> '$.name' AS name, t.value AS tag
FROM users u, json_each(u.data, '$.tags') t
WHERE t.value = 'admin';

Setiap baris dari users di-join dengan elemen-elemen array tags miliknya. json_each menyediakan kolom-kolom yang berguna, di antaranya key, value, type, dan fullkey. Saudaranya, json_tree, menelusuri seluruh dokumen secara rekursif, termasuk setiap node yang bersarang. Ini praktis untuk mencari di dokumen yang bentuknya tidak diketahui.

Memberi Index pada Field JSON

Query WHERE data ->> '$.active' = 1 di atas berfungsi, tapi SQLite harus mem-parse setiap baris untuk menghitung kondisinya. Untuk field yang sering kamu query, buat expression index:

CREATE TABLE users (id INTEGER PRIMARY KEY, data TEXT);

CREATE INDEX idx_users_email
    ON users(json_extract(data, '$.email'));

INSERT INTO users (data) VALUES
    ('{"email": "ada@example.com"}'),
    ('{"email": "boris@example.com"}');

EXPLAIN QUERY PLAN
SELECT * FROM users WHERE json_extract(data, '$.email') = 'ada@example.com';

Index harus memakai ekspresi yang persis sama dengan query-mu. Mencampur json_extract(data, '$.email') di index dengan data ->> '$.email' di query tidak akan cocok, dan index itu akan menganggur. Pilih satu bentuk dan konsisten memakainya.

Untuk field yang terus-menerus kamu query, generated column lebih enak dibaca:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    data TEXT,
    email TEXT GENERATED ALWAYS AS (data ->> '$.email') VIRTUAL
);

CREATE INDEX idx_users_email ON users(email);

INSERT INTO users (data) VALUES ('{"email": "ada@example.com"}');

SELECT email FROM users WHERE email = 'ada@example.com';

Bagi penulis query, email terlihat seperti kolom biasa, tapi nilainya tetap sinkron dengan JSON secara otomatis.

Memvalidasi JSON

json_valid(text) mengembalikan 1 jika teksnya bisa di-parse sebagai JSON, dan 0 jika tidak. Pasangkan dengan constraint CHECK untuk menolak data yang salah saat ditulis:

CREATE TABLE events (
    id INTEGER PRIMARY KEY,
    payload TEXT CHECK (json_valid(payload))
);

INSERT INTO events (payload) VALUES ('{"type": "click"}');
INSERT INTO events (payload) VALUES ('not json');

Insert pertama berhasil; yang kedua gagal dengan error constraint. Tanpa pemeriksaan itu, JSON yang rusak akan diam-diam tersimpan di tabel sampai suatu panggilan json_extract meledak berbulan-bulan kemudian.

JSON vs JSONB

Sejak SQLite 3.45, ada representasi biner yang disebut JSONB: data yang sama, tapi sudah di-parse ke bentuk biner yang ringkas sehingga fungsi tidak perlu mem-parse ulang di setiap panggilan. Keluarga fungsi jsonb_* (jsonb_extract, jsonb_set, jsonb_object, ...) mengembalikan JSONB, bukan teks, dan kolom JSONB bisa di-query dengan operator yang sama.

CREATE TABLE docs (id INTEGER PRIMARY KEY, body BLOB);

INSERT INTO docs (body) VALUES
    (jsonb('{"name": "Ada", "score": 99}'));

SELECT body ->> '$.name' AS name FROM docs;

Pakai JSON biasa (teks) jika kamu ingin dokumennya mudah dibaca manusia di hasil dump dan mudah diperiksa. Pakai JSONB jika tabelnya besar, sering di-query, dan beban parsing-nya memang terlihat saat profiling. Jangan beralih begitu saja: keterbacaan JSON biasa sangat berharga saat debugging.

Kapan JSON Adalah Pilihan yang Tepat

Kolom JSON unggul jika:

• Bentuknya berbeda-beda antarbaris (misalnya payload event, log audit, webhook integrasi).
• Kamu menyimpan cache respons API eksternal dan ingin menyimpannya utuh.
• Sebuah field jarang di-query dan hampir tidak pernah dipakai untuk memfilter.

Kolom JSON tidak cocok jika:

• Kamu memakai JSON untuk menghindari merancang skema. Jika setiap baris punya field yang sama, field itu seharusnya menjadi kolom.
• Kamu sering perlu memfilter atau join berdasarkan sebuah nilai. Kolom sungguhan dengan index akan selalu lebih cepat daripada pencarian lewat path JSON.
• Kamu sebenarnya butuh foreign key. JSON tidak punya integritas relasional.

Titik idealnya adalah menggabungkan keduanya: kolom skalar untuk field yang menentukan query dan constraint, ditambah satu kolom JSON di sampingnya untuk sisa data yang bervariasi.

Selanjutnya: Full-Text Search

JSON memberimu fleksibilitas di sisi penyimpanan. Halaman berikutnya membahas FTS5, mesin full-text search milik SQLite, yang memberimu pencarian teks sungguhan dengan peringkat dan penyorotan, jauh melampaui kemampuan LIKE.