Fungsi JSON

json_extract dan keluarganya untuk menyimpan serta membaca kolom JSON.

Daftar isi

Fungsi JSON bawaan SQLite menyusun, membaca, dan melebur teks JSON langsung dari query, sehingga atribut yang bentuknya berubah-ubah dapat disimpan pada satu kolom tanpa memecah tabel. Topik ini membahas penyusunan JSON dengan json_object dan json_array, pembacaan dengan json_extract beserta operator -> dan ->>, peleburan array dengan json_each, pola kolom metadata JSON pada tabel buku, serta pemeriksaan validitas dengan json_valid. Semua keluaran diperoleh dari eksekusi nyata pada sqlite3 CLI yang dipakai di jalur ini.

Menyusun JSON dengan json_object dan json_array#

json_object menerima pasangan nama dan nilai secara bergantian lalu mengembalikan satu teks JSON berbentuk objek, sedangkan json_array menerima daftar nilai dan mengembalikan teks JSON berbentuk array. Kedua fungsi mengurus tanda kutip dan tanda baca, sehingga nilai dari kolom tabel dapat dilekatkan tanpa menyusun string secara manual. Query berikut menyusun satu objek buku dan satu array tag dari nilai literal.

sql Menyusun objek dan array JSON

SELECT json_object('judul', 'Dasar Algoritma', 'tahun', 2019);
SELECT json_array('sains', 'pemrograman');

text Keluaran

{"judul":"Dasar Algoritma","tahun":2019}
["sains","pemrograman"]

Keluaran berupa teks JSON yang angkanya tetap ditulis tanpa tanda kutip sedangkan teksnya dikutip, dan bentuk inilah yang bisa disimpan pada kolom TEXT biasa. Fungsi penyusun lain seperti json_insert, json_set, dan json_patch memodifikasi JSON yang sudah ada, tetapi untuk sebagian besar kebutuhan menyimpan atribut opsional, json_object dan json_array saja sudah cukup. Penyusunan manual dengan penggabungan string dibiarkan begitu saja karena satu tanda kutip yang luput langsung merusak keabsahan JSON-nya. Fungsi pendamping seperti json_quote dan json_concat melayani kasus yang lebih khusus, sedangkan pasangan nama dan nilai pada json_object mencakup kebutuhan penyusunan yang paling umum.

Membaca JSON dengan json_extract dan operator ->#

json_extract mengambil bagian dari teks JSON menurut jalur (path) yang selalu diawali tanda $, misalnya $.judul untuk anggota objek atau $.tag[0] untuk elemen array. Sejak SQLite 3.38, pembacaan yang sama dapat ditulis dengan operator -> dan ->>, dan perbedaan keduanya terletak pada bentuk hasilnya, yaitu -> mengembalikan representasi JSON sedangkan ->> mengembalikan nilai SQL biasa. Blok berikut menjalankan bentuk-bentuk pembacaan itu berurutan.

sql json_extract, ->, dan ->> pada JSON buku

SELECT json_extract('{"judul":"Kode dan Sandi","tag":["sains","kriptografi"]}', '$.judul');
SELECT json_extract('{"tag":["sains","kriptografi"]}', '$.tag[0]', '$.tag[1]');
SELECT '{"judul":"Kode dan Sandi"}' -> '$.judul';
SELECT '{"tag":["sains"]}' -> 'tag' ->> 0;
SELECT '{"harga":"85000"}' ->> 'harga', '{"harga":"85000"}' -> 'harga';

text Keluaran

Kode dan Sandi
["sains","kriptografi"]
"Kode dan Sandi"
sains
85000|"85000"

Baris ketiga memperlihatkan perbedaan yang paling sering menyesatkan. Operator -> mengembalikan "Kode dan Sandi" lengkap dengan tanda kutip karena hasilnya memang teks JSON, sedangkan ->> mengembalikan nilai SQL murni tanpa kutip. Perbedaan itu terlihat kembali pada baris kelima yang menampilkan harga 85000 tanpa kutip dari ->> dan dengan kutip dari ->. json_extract dengan dua jalur sekaligus mengembalikan array berisi kedua hasilnya. Operator juga boleh dirantai seperti pada -> 'tag' ->> 0 yang melompat ke dalam array lalu mengambil elemen pertamanya. Aturan praktisnya sederhana, yaitu ->> dipakai ketika hasil akan dibandingkan, diurutkan, atau ditampilkan, sedangkan -> dipakai ketika hasil akan disambung ke fungsi JSON lain. Perbedaan bentuk ini tidak memunculkan error saat keliru, tetapi membiarkan tanda kutip ikut terbandingkan pada syarat WHERE.

Kolom metadata pada tabel buku#

Pola yang umum adalah satu kolom TEXT berisi JSON yang menampung atribut opsional, misalnya tahun terbit dan daftar tag yang tidak semua buku memilikinya. Kolom seperti ini tidak memaksa tabel berubah setiap kali jenis atribut bertambah, dan pembacaannya tetap dilakukan lewat fungsi JSON. Blok berikut mengisi kolom metadata untuk tiga buku, lalu membaca tag dengan json_each serta tahun terbit dengan json_extract.

sql Kolom metadata JSON dan peleburan tag

CREATE TABLE buku (id INTEGER PRIMARY KEY, judul TEXT, metadata TEXT);
INSERT INTO buku VALUES
  (1, 'Dasar Algoritma', '{"tahun":2019,"tag":["teknologi","pemula"]}'),
  (2, 'Peta Langit', '{"tahun":2021,"tag":["sains","langit"]}'),
  (3, 'Kode dan Sandi', '{"tahun":2019,"tag":["kriptografi"]}');

SELECT b.judul, j.value AS tag
FROM buku b, json_each(b.metadata, '$.tag') j
ORDER BY b.id, j.key;
SELECT judul, json_extract(metadata, '$.tahun') AS tahun FROM buku ORDER BY id;

text Keluaran

Dasar Algoritma|teknologi
Dasar Algoritma|pemula
Peta Langit|sains
Peta Langit|langit
Kode dan Sandi|kriptografi
Dasar Algoritma|2019
Peta Langit|2021
Kode dan Sandi|2019

json_each adalah table-valued function, yaitu fungsi yang mengembalikan sekumpulan baris sehingga dapat dijadikan sumber dalam FROM, dan satu baris hasilnya mewakili satu elemen array dengan kolom key, value, serta type. Kolom type itu menandai jenis elemennya, misalnya text atau integer, sehingga array yang isinya bercampur jenis tetap dapat dibedakan. Peleburan array tag menghasilkan lima baris tag dari tiga buku, sedangkan query kedua menarik tahun terbit yang tersimpan di dalam objek tanpa kolom khusus. Pola ini menjaga tabel utama tetap ramping, dan tag yang baru tidak menuntut ALTER TABLE sama sekali.

Validitas dan kapan kolom JSON tepat digunakan#

SQLite tidak memiliki tipe data JSON tersendiri, sehingga teks JSON tersimpan sebagai TEXT dan keabsahannya tidak diperiksa secara otomatis. Fungsi json_valid mengembalikan 1 untuk teks JSON yang sah dan 0 untuk yang tidak, dan pemeriksaan itu dapat dipasang sebagai constraint CHECK agar baris rusak tertolak sejak masuk. Blok berikut menguji dua teks, lalu mencoba memasukkan teks yang bukan JSON pada tabel yang telah dipasangi CHECK.

sql json_valid dan CHECK pada kolom metadata

CREATE TABLE buku (
  id INTEGER PRIMARY KEY,
  judul TEXT,
  metadata TEXT CHECK (json_valid(metadata))
);
INSERT INTO buku VALUES (1, 'Dasar Algoritma', '{"tahun":2019,"tag":["teknologi"]}');

SELECT json_valid('{"a":1}'), json_valid('{a:1}');
INSERT INTO buku VALUES (2, 'Jejak Angin', 'bukan-json');

text Keluaran

1|0
Error near line 9: CHECK constraint failed: json_valid(metadata)

Pembandingan 1|0 menegaskan bahwa objek berkurung kurawal dengan anggota yang dikutip adalah JSON sah, sedangkan teks tanpa kutip ditolak oleh fungsi tersebut. INSERT kedua kemudian gagal karena CHECK memanggil json_valid pada nilai yang masuk, sehingga kolom metadata hanya berisi JSON yang dapat dibaca fungsi JSON lainnya. Kolom JSON tepat digunakan untuk atribut yang jarang difilter dan bentuknya berubah, sedangkan atribut yang selalu menjadi syarat pencarian lebih baik diangkat menjadi kolom biasa yang dapat diindeks.

Kesalahan umum#

Menulis jalur tanpa awalan tanda dolar. Pembacaan seperti json_extract(metadata, 'tahun') ditolak dengan pesan error bad JSON path, dan perbaikannya adalah menuliskan '$.tahun' dengan tanda dolar di depan. Jalur untuk elemen array memakai indeks berbasis nol, sehingga elemen pertama dibaca $.tag[0]. Pesan errornya menyebut jalur yang salah, sehingga letak kesalahannya langsung terlihat.

Memakai operator -> lalu membandingkan hasilnya dengan teks tanpa tanda kutip. Hasil -> tetap berupa teks JSON, sehingga pembandingan -> 'harga' = '85000' tidak pernah cocok dengan nilai yang tersimpan sebagai JSON string. Gunakan ->> untuk setiap hasil yang dijadikan operand pembandingan atau syarat WHERE.

Mengira SQLite menyimpan JSON sebagai tipe khusus. Kolomnya tetap TEXT, dan fungsi JSON mengurai teks itu setiap kali dibaca, sehingga kolom JSON yang sangat besar dan sering diurai menjadi beban pembacaan. Pindahkan atribut yang ditanya setiap hari ke kolom biasa dan biarkan sisanya pada metadata. Ukur pula ukuran metadata pada data nyata sebelum memilih pola ini, karena teks JSON yang berulang di setiap baris cepat membesar.

Ringkasan#

json_object dan json_array menyusun teks JSON dari pasangan nama dan nilai atau dari daftar nilai tanpa penggabungan string manual.
json_extract beserta operator -> dan ->> membaca JSON lewat jalur berawalan $, dengan -> mengembalikan representasi JSON dan ->> mengembalikan nilai SQL.
json_each melebur array menjadi baris melalui FROM, sehingga tag pada kolom metadata dapat dihitung, diurutkan, dan digabung seperti kolom biasa.
SQLite menyimpan JSON sebagai TEXT tanpa pemeriksaan otomatis, dan constraint CHECK (json_valid(kolom)) menahan baris yang JSON-nya rusak.

Referensi#

JSON Functions and Operators — SQLite — Dokumentasi resmi keluarga fungsi JSON, jalur, operator -> dan -», serta json_each.
SQLite Query Language — SQLite — Indeks pernyataan SQL SQLite yang memuat aturan CHECK dan CREATE TABLE pada contoh.