JSON di SQL: Cara Query Data Semi Terstruktur

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.

Kenapa ada kolom JSON di database?

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.

Gimana cara ambil satu field dari JSON?

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.

Gimana cara turun ke JSON yang bersarang?

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.

Gimana cara ngurai array JSON jadi baris?

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.

Contoh kasus: nyari kanal paling untung

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
whatsapp	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.

Sintaksnya di MySQL dan SQL Server

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.

Gimana bikin query JSON gak lemot?

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.

Kesalahan umum

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.

FAQ

Kapan sebaiknya simpan data sebagai JSON di database?

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.

Bedanya JSON sama JSONB di PostgreSQL apa?

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.

Kenapa hasil query JSON-ku selalu bertipe teks?

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.

Gimana cara query JSON di MySQL dan SQL Server?

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.

Query JSON-ku lemot banget, gimana benerinnya?

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.

Penutup

Yang perlu kamu bawa pulang:

• -> buat turun, ->> buat ambil. Hasilnya selalu teks, jadi cast dulu sebelum dihitung.
• Array diurai pakai jsonb_array_elements (atau JSON_TABLE / OPENJSON). Habis itu, dia jadi tabel biasa.
• Hati-hati duplikasi field level atas setelah unnest. 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.