TL;DR
Query JSON di SQL artinya ngambil nilai dari kolom yang isinya dokumen JSON, pakai operator kayak ->> di PostgreSQL atau fungsi JSON_VALUE di MySQL dan SQL Server. Kamu bisa ambil field tunggal, turun ke struktur bersarang, dan ngurai array jadi baris terpisah pakai JSON_TABLE atau jsonb_array_elements. Kuncinya: hasil ekstraksi JSON itu teks, jadi harus di-cast ke angka atau tanggal sebelum dihitung.
Query JSON di SQL artinya ngambil nilai dari kolom yang isinya dokumen JSON, tanpa perlu ngeluarin datanya dulu ke Python.
Skenarionya kayak gini. Kamu buka tabel pesanan_umkm, dan ada satu kolom namanya detail yang isinya begini:
{
"channel": "whatsapp",
"ongkir": 12000,
"alamat": { "kota": "Bandung", "provinsi": "Jawa Barat" },
"items": [
{ "nama": "Keripik Singkong", "qty": 3, "harga": 18000 },
{ "nama": "Sambal Roa", "qty": 1, "harga": 45000 }
]
}Reaksi pertama kebanyakan analis: "waduh, ini harus minta tolong backend."
Gak perlu. SQL modern udah bisa baca isi JSON langsung, dan sintaksnya lebih gampang dari yang kamu kira.
Di tutorial ini aku pakai PostgreSQL sebagai contoh utama, plus padanan sintaksnya di MySQL dan SQL Server. Datanya: tabel pesanan_umkm berisi 3.200 pesanan dari toko oleh-oleh online selama 6 bulan.
Karena gak semua data punya bentuk yang sama tiap barisnya.
Toko UMKM tadi jualan di WhatsApp, Shopee, dan Tokopedia. Tiap kanal ngirim metadata yang beda: Shopee ngasih voucher_id, WhatsApp gak punya itu tapi punya nomor_pengirim.
Kalau tiap field dibikin kolom sendiri, tabelnya bakal punya 40 kolom yang 30-nya NULL terus. Jadi orang milih nyimpen semuanya di satu kolom JSON.
Trade-off-nya: fleksibel di sisi nyimpen, ribet di sisi baca. Bagian ribetnya itu yang mau kita selesaikan.
Aturan praktisnya: kalau kamu sering filter atau agregasi suatu field, pindahin dia ke kolom biasa. JSON buat yang jarang dipakai dan bentuknya berubah-ubah.
Di PostgreSQL, ada dua operator yang harus kamu hafalin:
-> balikin JSON (buat lanjut turun ke dalam)->> balikin teks (buat berhenti dan ambil nilainya)Gampangnya: pakai -> di tengah jalan, ->> di ujung.
SELECT
id_order,
detail ->> 'channel' AS channel,
(detail ->> 'ongkir')::numeric AS ongkir
FROM pesanan_umkm
WHERE detail ->> 'channel' = 'whatsapp';Perhatiin ::numeric itu. Ini bagian yang paling sering bikin orang kepeleset.
Hasil ->> selalu teks. Kalau kamu langsung SUM(detail ->> 'ongkir'), PostgreSQL bakal ngeluh. Dan di database yang lebih longgar, dia mungkin gak ngeluh, tapi ngasih hasil yang salah.
Cast dulu, baru hitung. Tiap kali.
Kalau kamu belum familiar sama casting tipe data, cek fungsi CAST dulu.
Field alamat tadi isinya objek lagi. Buat ambil kotanya, kamu turun satu level pakai ->, terus ambil pakai ->>:
SELECT
id_order,
detail -> 'alamat' ->> 'kota' AS kota,
detail -> 'alamat' ->> 'provinsi' AS provinsi
FROM pesanan_umkm;Kalau nested-nya dalam banget, ada jalan pintas pakai #>> dengan path array:
SELECT
detail #>> '{alamat,kota}' AS kota
FROM pesanan_umkm;Hasilnya sama. Pilih yang mana aja yang kamu ngerti pas baca ulang 3 bulan lagi.
Ini bagian yang paling berguna, dan paling bikin orang stuck.
Field items isinya array. Satu pesanan bisa punya 1 item atau 8 item. Buat ngitung produk mana yang paling laku, kamu butuh tiap item jadi barisnya sendiri.
PostgreSQL punya jsonb_array_elements:
SELECT
p.id_order,
item ->> 'nama' AS produk,
(item ->> 'qty')::int AS qty,
(item ->> 'harga')::numeric AS harga
FROM pesanan_umkm p,
jsonb_array_elements(p.detail -> 'items') AS item;Satu pesanan dengan 2 item jadi 2 baris. Pesanan dengan 5 item jadi 5 baris.
Habis diurai, datanya jadi tabel biasa. Agregasi jalan normal:
SELECT
item ->> 'nama' AS produk,
SUM((item ->> 'qty')::int) AS total_qty,
SUM((item ->> 'qty')::int * (item ->> 'harga')::numeric) AS omzet
FROM pesanan_umkm p,
jsonb_array_elements(p.detail -> 'items') AS item
GROUP BY 1
ORDER BY omzet DESC
LIMIT 10;Satu catatan penting. Kalau ada baris yang items-nya kosong atau NULL, baris itu bakal hilang dari hasil. Ini perilaku join implisit.
Kalau kamu butuh baris itu tetap muncul, pakai LEFT JOIN LATERAL:
SELECT p.id_order, item ->> 'nama' AS produk
FROM pesanan_umkm p
LEFT JOIN LATERAL jsonb_array_elements(p.detail -> 'items') AS item ON true;Aku pernah kehilangan 47 pesanan dari laporan gara-gara lupa ini. Angkanya jadi meleset 1,5% dan baru ketahuan seminggu kemudian.
Dari 3.200 pesanan toko oleh-oleh tadi, pemiliknya nanya: kanal mana yang paling menguntungkan?
Pertanyaan itu belum bisa dijawab langsung, karena "menguntungkan" bisa berarti omzet total atau nilai per pesanan. Aku ambil dua-duanya:
WITH item_flat AS (
SELECT
p.id_order,
p.detail ->> 'channel' AS channel,
(p.detail ->> 'ongkir')::numeric AS ongkir,
(item ->> 'qty')::int * (item ->> 'harga')::numeric AS nilai_item
FROM pesanan_umkm p,
jsonb_array_elements(p.detail -> 'items') AS item
),
per_order AS (
SELECT
id_order,
channel,
MAX(ongkir) AS ongkir,
SUM(nilai_item) AS nilai_order
FROM item_flat
GROUP BY id_order, channel
)
SELECT
channel,
COUNT(*) AS jumlah_order,
SUM(nilai_order) AS omzet_total,
ROUND(AVG(nilai_order)) AS rata_per_order,
ROUND(AVG(ongkir)) AS rata_ongkir
FROM per_order
GROUP BY channel
ORDER BY omzet_total DESC;Perhatiin CTE per_order. Aku pakai MAX(ongkir) bukan SUM(ongkir), karena setelah array diurai, nilai ongkir keduplikat di tiap baris item. Kalau di-SUM, ongkir pesanan dengan 5 item bakal kehitung 5 kali.
Ini jebakan klasik pas kerja sama JSON array. Field di level atas selalu keduplikat setelah unnest.
Hasilnya:
| Channel | Jumlah order | Omzet total | Rata per order |
|---|---|---|---|
| shopee | 1.684 | Rp 142.100.000 | Rp 84.400 |
| 892 | Rp 118.700.000 | Rp 133.100 | |
| tokopedia | 624 | Rp 51.300.000 | Rp 82.200 |
Shopee menang di omzet total. Tapi WhatsApp punya nilai per pesanan 58% lebih tinggi: Rp 133.100 versus Rp 84.400.
Waktu aku urai lebih jauh, ketahuan kenapa: pesanan WhatsApp rata-rata isinya 3,8 item, sementara Shopee cuma 1,9 item. Orang yang chat langsung cenderung nanya-nanya dulu terus nambah barang.
Keputusan yang keluar dari sini: pemiliknya nambah 1 admin WhatsApp, bukan naikin budget iklan Shopee.
Angka itu gak akan pernah kelihatan kalau kolom JSON-nya cuma dianggurin.
Konsepnya identik. Yang beda cuma nama fungsinya.
| Tugas | PostgreSQL | MySQL | SQL Server |
|---|---|---|---|
| Ambil field (teks) | detail ->> 'channel' | detail ->> '$.channel' | JSON_VALUE(detail, '$.channel') |
| Field bersarang | detail #>> '{alamat,kota}' | detail ->> '$.alamat.kota' | JSON_VALUE(detail, '$.alamat.kota') |
| Urai array | jsonb_array_elements() | JSON_TABLE() | OPENJSON() |
| Cek key ada | detail ? 'ongkir' | JSON_CONTAINS_PATH() | JSON_PATH_EXISTS() |
Detail lengkap sintaks PostgreSQL ada di dokumentasi resmi fungsi JSON PostgreSQL.
JSON itu mahal buat dibaca. Ada tiga hal yang bikin bedanya kerasa.
Pakai JSONB, bukan JSON. Tipe json nyimpen teks mentah, jadi database harus parse ulang tiap kali dibaca. Tipe jsonb udah disimpen dalam bentuk terurai. Buat tabel yang sering di-query, jsonb selalu menang.
Index GIN cuma jalan buat operator containment. Ini yang bikin banyak orang bingung:
CREATE INDEX idx_pesanan_detail ON pesanan_umkm USING GIN (detail);
-- Index KEPAKAI
SELECT * FROM pesanan_umkm WHERE detail @> '{"channel": "whatsapp"}';
-- Index GAK kepakai (tetap full scan)
SELECT * FROM pesanan_umkm WHERE detail ->> 'channel' = 'whatsapp';Dua query itu ngasih hasil yang sama. Tapi cuma yang pertama yang pakai index.
Kalau kamu tetap mau pakai ->> dan tetep cepat, bikin expression index:
CREATE INDEX idx_channel ON pesanan_umkm ((detail ->> 'channel'));Jangan unnest kalau gak perlu. Kalau kamu cuma butuh channel dan ongkir, jangan ikut ngurai items. Tiap unnest ngalikan jumlah baris yang harus diproses.
Cek selalu pakai EXPLAIN ANALYZE sebelum nyalahin JSON-nya.
Lupa cast tipe data. SUM(detail ->> 'ongkir') itu error atau salah hasil. Selalu SUM((detail ->> 'ongkir')::numeric).
Salah pakai -> vs ->>. Kalau hasilmu keluar dengan tanda kutip ganda ("whatsapp" bukan whatsapp), berarti kamu pakai -> padahal harusnya ->>.
Nge-SUM field level atas setelah unnest. Ongkir keduplikat per item. Pakai MAX atau agregasi dulu per order sebelum dijumlah.
Nganggap key selalu ada. Kalau 200 baris gak punya key ongkir, hasilnya NULL, dan NULL diem-diem bikin rata-ratamu meleset. Pakai COALESCE(detail ->> 'ongkir', '0') kalau memang nol itu masuk akal.
Nyimpen semuanya di JSON. Kalau tiap query kamu selalu filter pakai channel, keluarin dia jadi kolom beneran. JSON bukan alasan buat males bikin skema.
Simpan sebagai JSON kalau strukturnya beda-beda per baris dan sering berubah. Contohnya metadata pesanan dari beberapa marketplace yang formatnya gak seragam. Kalau semua baris punya field yang sama dan kamu sering filter atau agregasi field itu, bikin kolom biasa. Kolom biasa lebih cepat, lebih ketat tipenya, dan lebih gampang dibaca orang lain.
Tipe JSON nyimpen teks aslinya persis, termasuk spasi dan urutan key. Tipe JSONB nyimpen dalam bentuk biner yang udah diurai, jadi urutan key hilang tapi query-nya jauh lebih cepat. JSONB juga bisa diindex pakai GIN. Buat hampir semua kasus analisis, pakai JSONB. JSON biasa cuma masuk akal kalau kamu butuh nyimpen dokumen persis apa adanya.
Karena operator ->> di PostgreSQL dan fungsi JSON_VALUE memang didesain balikin teks. Kalau kamu langsung jumlahin hasilnya, kamu bakal dapat error atau hasil yang salah karena teks digabung, bukan dijumlah. Selalu cast dulu: pakai ::numeric atau ::int di PostgreSQL, atau CAST(... AS DECIMAL) di MySQL. Ini kesalahan nomor satu waktu mulai kerja sama JSON.
MySQL pakai JSON_EXTRACT atau operator ->> yang mirip PostgreSQL, plus JSON_TABLE buat ngurai array jadi baris. SQL Server pakai JSON_VALUE buat field tunggal dan OPENJSON buat array. Konsepnya sama persis: ambil field, cast tipenya, urai array kalau perlu. Yang beda cuma nama fungsinya.
Tiga penyebab paling umum. Pertama, kamu filter pakai ->> tapi index GIN cuma jalan buat operator containment @>. Kedua, kamu ngurai array besar di tiap baris padahal cuma butuh satu field. Ketiga, kolomnya bertipe JSON bukan JSONB, jadi database harus parse ulang teksnya tiap kali dibaca. Cek dulu dengan EXPLAIN ANALYZE sebelum nebak.
Dua operator yang perlu kamu apal: -> buat turun, ->> buat ambil nilainya. Hasilnya selalu teks, jadi cast dulu sebelum dihitung. Array diurai pakai jsonb_array_elements (atau JSON_TABLE / OPENJSON). Habis itu, dia jadi tabel biasa. Yang paling gampang kelewatan: field level atas keduplikat setelah unnest, dan itu penyebab angka meleset paling senyap.
Kolom JSON bukan alasan buat nyerah dan minta tolong backend. Lima operator tadi udah cukup buat 90% kerjaan.
Mau latihan query kayak gini di data beneran? Mulai dari COALESCE buat nanganin key yang kosong, terus lanjut ke CTE di SQL. Query JSON yang panjang jauh lebih kebaca kalau dipecah pakai CTE.
Mau praktek langsung? Mulai latihan SQL gratis
Latihan interaktif, langsung di browser.
Analisa per kuartal, hari kerja, atau musim jadi ribet kalau tiap query ngitung ulang atribut tanggal. Tabel kalender nyimpen semua atribut itu sekali, biar tinggal di-JOIN.
Laporan penjualan harian sering bolong di hari tanpa transaksi. Ini cara bikin deret tanggal lengkap di SQL biar tiap hari muncul, walau nilainya nol.
Struktur organisasi tersimpan sebagai kolom id_atasan yang saling nunjuk. Ini cara narik rantai jabatan, hitung total bawahan, dan span of control cuma pakai SQL.