KAL
Menu

Query JSONB

Menelusuri dokumen JSONB dengan operator ->, ->>, containment, dan jsonpath.

Daftar isi

Topik ini membahas cara menelusuri dokumen JSONB, mulai dari operator navigasi -> dan -», operator containment @> yang menjadi jantung penyaringan, sampai bahasa jsonpath untuk syarat yang lebih kaya. Dataset profil pengguna dibangun ulang di awal setiap topik, dan penyaringannya diuji langsung pada data yang sama.

Blok penyiapan dataset#

bash 
# dataset profil pengguna aplikasi catatan untuk modul ini
PGOPTIONS='-c client_min_messages=warning' psql -q -U postgres -d perpustakaan <<'SQL'
DROP TABLE IF EXISTS profil, artikel CASCADE;
CREATE TABLE profil (
    id   SERIAL PRIMARY KEY,
    nama TEXT NOT NULL,
    data JSONB NOT NULL
);
INSERT INTO profil (nama, data) VALUES
    ('Ali Rahman', '{"nama_tampil": "Ali", "umur": 30, "kota": "Bandung", "minat": ["basis data", "membaca"]}'),
    ('Budi Santoso', '{"nama_tampil": "Budi", "umur": 24, "kota": "Yogyakarta", "minat": ["sejarah"]}'),
    ('Citra Kirana', '{"nama_tampil": "Citra", "umur": 28, "kota": "Bandung", "minat": ["botani", "memasak", "basis data"]}');
SQL

Navigasi dengan -> dan -»#

Operator -> mengambil kunci atau elemen dan mengembalikan jsonb, sedangkan -» mengembalikan teks, dan perbedaan itu menentukan bentuk perbandingan yang sah sesudahnya. Blok berikut menelusuri satu kunci dan satu elemen array.

bash 
# navigasi kunci dan elemen array
psql -U postgres -d perpustakaan -c "SELECT nama, data->'kota' AS kota_jsonb, data->>'kota' AS kota_teks, data->'minat'->>0 AS minat_pertama FROM profil ORDER BY id;"
text Keluaran 
     nama     |  kota_jsonb  | kota_teks  | minat_pertama
--------------+--------------+------------+---------------
 Ali Rahman   | "Bandung"    | Bandung    | basis data
 Budi Santoso | "Yogyakarta" | Yogyakarta | sejarah
 Citra Kirana | "Bandung"    | Bandung    | botani
(3 rows)

Kolom kota_jsonb tampil dengan tanda kutip karena tetap bertipe jsonb, sedangkan kota_teks murni teks, dan indeks array dimulai dari nol pada penelusuran jsonb. Jalur bertingkat diringkas dengan operator #> beserta jalurnya, misalnya data#>'{minat,0}', dan pemilihan bentuk kembalian mengikuti perbandingan yang menyusulnya, yaitu teks untuk perbandingan nilai dan jsonb untuk penelusuran lanjutan.

Penyaringan nilai dengan cast#

Perbandingan angka pada hasil -» membutuhkan cast eksplisit, karena kembaliannya teks, dan kelalaian cast menghasilkan perbandingan teks yang salah urutan. Blok berikut menyaring profil dewasa dengan perbandingan numerik yang benar.

bash 
# perbandingan numerik pada nilai yang di-cast
psql -U postgres -d perpustakaan -c "SELECT nama, (data->>'umur')::INTEGER AS umur FROM profil WHERE (data->>'umur')::INTEGER >= 28 ORDER BY umur DESC, nama;"
text Keluaran 
     nama     | umur
--------------+------
 Ali Rahman   |   30
 Citra Kirana |   28
(2 rows)

Cast ke integer memastikan 28 dan 30 dibandingkan sebagai bilangan, bukan teks, dan pola ini muncul di setiap query yang menyaring nilai angka di dalam dokumen. Penyaringan teks tidak butuh cast, dan kesalahan urutan leksikografis menjadi gejala paling umum dari cast yang lupa.

Containment sebagai penyaring utama#

Operator @> menguji apakah dokumen kiri memuat dokumen kanan, dan bentuknya menjadi cara baku menyaring dokumen karena maknanya langsung dan dapat dilayani index GIN. Blok berikut mencari profil yang memuat kota Bandung dan yang minatnya memuat basis data.

bash 
# penyaringan containment pada objek dan array
psql -U postgres -d perpustakaan <<'SQL'
SELECT nama FROM profil WHERE data @> '{"kota": "Bandung"}' ORDER BY nama;
SELECT nama FROM profil WHERE data->'minat' @> '"basis data"' ORDER BY nama;
SQL
text Keluaran 
     nama
--------------
 Ali Rahman
 Citra Kirana
(2 rows)

     nama
--------------
 Ali Rahman
 Citra Kirana
(2 rows)

Containment pada objek memeriksa kecocokan kunci dan nilainya, sedangkan containment pada array memeriksa keberadaan elemen, dan bentuk "basis data" yang bertanda kutip adalah dokumen jsonb tunggal. Operator kebalikannya <@ menyatakan dimuat oleh, dan pasangan ini melayani mayoritas pertanyaan pencarian dokumen tanpa bahasa yang lebih panjang.

jsonpath untuk syarat yang dinilai#

Bahasa jsonpath menilai ekspresi di dalam dokumen, dan fungsi jsonb_path_exists serta jsonb_path_query menjalankannya. Blok berikut mencari profil dengan syarat umur di atas dua puluh lima lewat ekspresi jalurnya.

bash 
# syarat numerik lewat jsonpath
psql -U postgres -d perpustakaan -c "SELECT nama FROM profil WHERE jsonb_path_exists(data, '$.umur > 25') ORDER BY nama;"
text Keluaran 
     nama
--------------
 Ali Rahman
 Budi Santoso
 Citra Kirana
(3 rows)

Ekspresi $.umur > 25 dinilai terhadap setiap dokumen, dan bentuknya memadukan navigasi serta perbandingan dalam satu jalur. Jsonpath menjadi pilihan ketika syaratnya bersarang atau melibatkan perhitungan, sedangkan containment tetap menjadi pilihan tercepat untuk kecocokan struktur yang langsung.

Kesalahan umum#

Kesalahan pertama adalah membandingkan hasil -» tanpa cast untuk nilai angka, yang menyaring dengan urutan teks dan meloloskan nilai yang salah. Kesalahan kedua adalah memakai operator navigasi untuk penyaringan yang seharusnya containment, sehingga query tidak dapat dilayani index GIN dan selalu memindai penuh.

Kesalahan ketiga adalah mengira kunci jsonb peka kapital secara konsisten dengan kolom, karena kunci dokumen dibandingkan persis termasuk kapitalisasinya, dan penyelarasan bentuk kunci dilakukan saat penulisan. Kesalahan keempat adalah mengharapkan elemen pertama array pada indeks satu, padahal penelusurannya berbasis nol, perbedaan yang terlihat langsung pada keluaran navigasi di atas.

Selain containment, keluarga operator keberadaan menanyakan keberadaan kunci tanpa memedulikan nilainya, yaitu ? untuk satu kunci, ?| untuk salah satu, dan ?& untuk seluruhnya, bentuk ringkas untuk pertanyaan seperti dokumen yang memuat kunci telepon. Keberadaan kunci dibedakan dari nilai yang NULL, karena kunci dengan nilai NULL tetap ada, dan pemilihan operator mengikuti pertanyaannya, bukan kebiasaan. Keluarga ini dilayani GIN bentuk bawaan, pertimbangan yang menyambung ke topik pengindeksannya.

Ringkasan#

  • -> mengembalikan jsonb dan -» mengembalikan teks, dengan #> merangkum jalur bertingkat.
  • Perbandingan angka pada hasil -» membutuhkan cast eksplisit agar tidak menjadi perbandingan teks.
  • @> dan <@ menguji containment pada objek maupun array, dan dapat dilayani index GIN.
  • jsonpath menilai ekspresi bersyarat dalam dokumen melalui jsonb_path_exists dan kawan-kawannya.

Referensi#