Pencarian Teks dan JSONB

Data semi-terstruktur lazimnya tidak berdiri sendiri: dokumen JSONB hidup berdampingan dengan kolom teks biasa, dan pengguna tidak peduli di mana sebuah kata disimpan — mereka hanya ingin menemukannya. Chapter penutup Section Full-Text Search ini menyatukan kedua dunia tersebut: mencari di dalam field JSONB, menggabungkan kolom biasa dengan isi dokumen menjadi satu vektor pencarian, dan mengindeks semuanya dengan GIN agar query tetap cepat ketika tabel membesar.

Tabel Latihan: Teks di Dua Tempat #

Katalog produk Toko Maju Jaya menyimpan teks di dua tempat sekaligus: kolom biasa (nama, deskripsi) dan dokumen atribut berisi asal, keterangan, serta array ulasan pelanggan. Pola seperti ini persis yang paling sering dijumpai pada aplikasi nyata:

sql
buat-tabel.sql
-- Katalog produk Toko Maju Jaya: teks tersebar di kolom biasa DAN di dalam jsonb.
-- Array 'ulasan' berisi ulasan pelanggan yang juga perlu bisa dicari.
CREATE TABLE produk (
    id        integer PRIMARY KEY,
    nama      text NOT NULL,
    harga     integer NOT NULL,
    deskripsi text NOT NULL,
    atribut   jsonb NOT NULL
);

INSERT INTO produk VALUES
 (1, 'Kopi Toraja Arabika', 75000,
  'kopi arabika giling halus dari dataran tinggi toraja',
  '{"asal": "Sulawesi Selatan",
    "keterangan": "biji kopi 250 gram kemasan kedap udara",
    "ulasan": [{"isi": "kopinya enak dan aromanya kuat", "pengulas": "Budi"},
               {"isi": "kemasan rapi sampai tujuan", "pengulas": "Siti"}]}'),
 (2, 'Kopi Robusta Lampung', 60000,
  'kopi robusta dengan rasa pekat',
  '{"asal": "Lampung",
    "keterangan": "kopi bubuk 500 gram",
    "ulasan": [{"isi": "rasanya pekat pas untuk kopi susu", "pengulas": "Budi"}]}'),
 (3, 'Teh Melati Premium', 25000,
  'teh melati kering pilihan',
  '{"asal": "Jawa Barat",
    "keterangan": "teh celup 100 kantong",
    "ulasan": [{"isi": "tehnya wangi dan segar", "pengulas": "Siti"}]}'),
 (4, 'Teh Hijau Organik', 30000,
  'teh hijau tanpa pemanis',
  '{"asal": "Jawa Tengah",
    "keterangan": "daun teh kering 200 gram",
    "ulasan": []}'),
 (5, 'Gula Aren Cair', 45000,
  'gula aren cair untuk kopi dan teh',
  '{"asal": "Kalimantan Barat",
    "keterangan": "botol 500 ml",
    "ulasan": [{"isi": "manisnya pas untuk kopi", "pengulas": "Budi"}]}'),
 (6, 'Beras Melati Putih', 68000,
  'beras melati pulen untuk nasi gurih',
  '{"asal": "Jawa Timur",
    "keterangan": "karung 5 kilogram",
    "ulasan": [{"isi": "nasinya pulen dan wangi", "pengulas": "Siti"}]}');

SELECT id, nama, harga, atribut->>'asal' AS asal
FROM produk
ORDER BY id;

Output yang Dihasilkan:

text
CREATE TABLE
INSERT 0 6
 id |         nama         | harga |       asal       
----+----------------------+-------+------------------
  1 | Kopi Toraja Arabika  | 75000 | Sulawesi Selatan
  2 | Kopi Robusta Lampung | 60000 | Lampung
  3 | Teh Melati Premium   | 25000 | Jawa Barat
  4 | Teh Hijau Organik    | 30000 | Jawa Tengah
  5 | Gula Aren Cair       | 45000 | Kalimantan Barat
  6 | Beras Melati Putih   | 68000 | Jawa Timur
(6 rows)

Enam produk ini sengaja menaruh kata kunci di tempat yang berbeda-beda: “kedap” hanya ada di field keterangan produk 1, “pulen” hanya di isi ulasan produk 6, dan “wangi” tersebar di ulasan dua produk. Katanya sama pentingnya bagi pencarian — lokasinya tidak.

to_tsvector Menerima jsonb Secara Langsung #

Fakta yang jarang disadari: to_tsvector() bisa menerima nilai jsonb tanpa ekstraksi apa pun. Semua nilai string di dalam dokumen — pada kedalaman berapa pun — ikut diindeks, sementara angka dan boolean dilewati:

sql
cari-dalam-jsonb.sql
-- to_tsvector menerima jsonb langsung: semua nilai STRING dalam dokumen diindeks.
SELECT to_tsvector('simple', atribut) AS vektor_dokumen
FROM produk
WHERE id = 1;

-- Pola jalur: ekstrak satu field dengan ->> lalu proses seperti teks biasa.
SELECT id, nama
FROM produk
WHERE to_tsvector('simple', atribut->>'keterangan') @@ to_tsquery('simple', 'bubuk')
ORDER BY id;

-- Vektor seluruh dokumen menembus struktur: 'wangi' ditemukan di DALAM array ulasan.
SELECT id, nama
FROM produk
WHERE to_tsvector('simple', atribut) @@ to_tsquery('simple', 'wangi')
ORDER BY id;

Output yang Dihasilkan:

text
                                                                                                 vektor_dokumen                                                                                                 
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 '250':21 'aromanya':7 'biji':19 'budi':10 'dan':6 'enak':5 'gram':22 'kedap':24 'kemasan':12,23 'kopi':20 'kopinya':4 'kuat':8 'rapi':13 'sampai':14 'selatan':2 'siti':17 'sulawesi':1 'tujuan':15 'udara':25
(1 row)

 id |         nama         
----+----------------------
  2 | Kopi Robusta Lampung
(1 row)

 id |        nama        
----+--------------------
  3 | Teh Melati Premium
  6 | Beras Melati Putih
(2 rows)

Mari kita bedah ketiga pendekatannya:

Pernyataan pertama memperlihatkan wujud vektor dari seluruh dokumen produk 1: teks dari asal, keterangan, hingga isi tiap ulasan menyatu menjadi satu daftar lexeme — termasuk nama pengulas. Struktur JSON-nya lenyap; yang tersisa kata-katanya.
Pernyataan kedua memakai pola jalur (path extraction): atribut->>'keterangan' mengambil satu field sebagai teks, lalu diproses seperti kolom biasa. Query “bubuk” menemukan produk 2 karena kata itu ada di keterangannya. Pola ini presisi: hanya field itu yang ikut dicari.
Pernyataan ketiga memakai vektor seluruh dokumen — dan justru di sinilah kekuatannya: kata “wangi” tidak ada di kolom mana pun, hanya di dalam array ulasan, tetap ditemukan pada produk 3 dan 6.

Pilihan antara keduanya adalah trade-off klasik: ekstraksi jalur memberi kendali presisi (dan bisa diindeks per field), vektor seluruh dokumen memberi jangkauan total.

Menggabungkan Kolom Biasa dan Field JSONB #

Selanjutnya: satu vektor pencarian yang mencakup kolom biasa dan isi dokumen. Triknya sederhana — gabungkan semuanya menjadi satu teks sebelum diproses:

sql
gabungan-kolom.sql
-- Gabungkan kolom biasa dan field jsonb menjadi satu vektor pencarian.
-- Tanda kurung pada (atribut->>'keterangan') wajib: tanpa itu operator || akan
-- mencoba menggabungkan teks dengan seluruh kolom atribut dan gagal.
SELECT id, nama,
       to_tsvector('simple', nama || '. ' || deskripsi || '. ' || (atribut->>'keterangan')) AS vektor_gabungan
FROM produk
WHERE id = 1;

-- 'kilogram' hanya hidup di field keterangan produk 6.
SELECT id, nama
FROM produk
WHERE to_tsvector('simple', nama || '. ' || deskripsi || '. ' || (atribut->>'keterangan')) @@ to_tsquery('simple', 'kilogram')
ORDER BY id;

-- Trik baris utuh: bungkus seluruh baris menjadi jsonb, lalu to_tsvector.
-- Semua nilai string ikut (nama, deskripsi, seluruh atribut); harga numerik dilewati.
SELECT id, nama,
       to_tsvector('simple', to_jsonb(p)) AS vektor_baris
FROM produk AS p
WHERE id = 1;

SELECT id, nama
FROM produk AS p
WHERE to_tsvector('simple', to_jsonb(p)) @@ to_tsquery('simple', 'arabika')
ORDER BY id;

Output yang Dihasilkan:

text
 id |        nama         |                                                                         vektor_gabungan                                                                         
----+---------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------
  1 | Kopi Toraja Arabika | '250':14 'arabika':3,5 'biji':12 'dari':8 'dataran':9 'giling':6 'gram':15 'halus':7 'kedap':17 'kemasan':16 'kopi':1,4,13 'tinggi':10 'toraja':2,11 'udara':18
(1 row)

 id |        nama        
----+--------------------
  6 | Beras Melati Putih
(1 row)

 id |        nama         |                                                                                                                                                 vektor_baris                                                                                                                                                  
----+---------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  1 | Kopi Toraja Arabika | '250':25 'arabika':3,32 'aromanya':11 'biji':23 'budi':14 'dan':10 'dari':35 'dataran':36 'enak':9 'giling':33 'gram':26 'halus':34 'kedap':28 'kemasan':16,27 'kopi':1,24,31 'kopinya':8 'kuat':12 'rapi':17 'sampai':18 'selatan':6 'siti':21 'sulawesi':5 'tinggi':37 'toraja':2,38 'tujuan':19 'udara':29
(1 row)

 id |        nama         
----+---------------------
  1 | Kopi Toraja Arabika
(1 row)

Empat pengamatan dari hasil di atas:

Vektor gabungan produk 1 memuat nama, deskripsi, dan keterangan sekaligus — kata “kedap” yang tadinya tersembunyi di jsonb kini setara dengan kata di kolom mana pun.
Query “kilogram” hanya menemukan produk 6, karena kata itu memang hanya hidup di field keterangan produk tersebut — bukti gabungan benar-benar menjangkau ke dalam dokumen.
Trik to_jsonb(p) membungkus seluruh baris menjadi satu dokumen jsonb — kolom biasa ikut terkonversi. Vektor baris produk 1 di hasilnya mencakup semua teks baris tersebut; kolom harga yang numerik otomatis dilewati oleh to_tsvector.
Tanda kurung pada (atribut->>'keterangan') bukan gaya penulisan: tanpa kurung, operator || akan mencoba menggabungkan teks dengan seluruh kolom jsonb dan gagal dengan error tipe. Ini gotcha sintaks paling sering menagih waktu di pola gabungan.

Membongkar Array Ulasan Baris demi Baris #

Ketika hasil pencarian perlu menunjukkan ulasan mana yang cocok (bukan hanya produknya), array ulasan dibongkar dengan jsonb_array_elements, lalu setiap elemen dicari satu per satu:

sql
ulasan-lateral.sql
-- Bongkar array ulasan dengan jsonb_array_elements, lalu cari di tiap elemennya.
-- 'pulen' hanya ada di isi ulasan produk 6.
SELECT p.id, p.nama, u->>'isi' AS isi_ulasan
FROM produk p,
     jsonb_array_elements(p.atribut->'ulasan') AS u
WHERE to_tsvector('simple', u->>'isi') @@ to_tsquery('simple', 'pulen')
ORDER BY p.id;

-- Perkata dalam ulasan pun bisa dicari: 'wangi' muncul di dua produk berbeda.
SELECT p.id, p.nama, u->>'isi' AS isi_ulasan
FROM produk p,
     jsonb_array_elements(p.atribut->'ulasan') AS u
WHERE to_tsvector('simple', u->>'isi') @@ to_tsquery('simple', 'wangi')
ORDER BY p.id;

-- Produk tanpa ulasan (array kosong) ikut terbongkar: jumlah elemennya nol.
SELECT p.id, p.nama, count(u) AS jumlah_ulasan
FROM produk p
LEFT JOIN jsonb_array_elements(p.atribut->'ulasan') AS u ON true
GROUP BY p.id, p.nama
ORDER BY p.id;

Output yang Dihasilkan:

text
 id |        nama        |       isi_ulasan        
----+--------------------+-------------------------
  6 | Beras Melati Putih | nasinya pulen dan wangi
(1 row)

 id |        nama        |       isi_ulasan        
----+--------------------+-------------------------
  3 | Teh Melati Premium | tehnya wangi dan segar
  6 | Beras Melati Putih | nasinya pulen dan wangi
(2 rows)

 id |         nama         | jumlah_ulasan 
----+----------------------+---------------
  1 | Kopi Toraja Arabika  |             2
  2 | Kopi Robusta Lampung |             1
  3 | Teh Melati Premium   |             1
  4 | Teh Hijau Organik    |             0
  5 | Gula Aren Cair       |             1
  6 | Beras Melati Putih   |             1
(6 rows)

Pola FROM tabel, jsonb_array_elements(...) ini adalah lateral join implisit yang sudah kita kenal di chapter LATERAL Join — setiap baris produk menghasilkan baris-baris ulasan. Query “pulen” kini menampilkan isi ulasannya secara utuh, dan query terakhir memperlihatkan produk 4 dengan nol ulasan tetap hadir berkat LEFT JOIN ... ON true.

Mengindeks Teks di Dalam JSONB dengan GIN #

Semua pola di atas memindai baris satu per satu. Untuk tabel besar, index expression GIN mengubahnya menjadi pencarian index — dengan syarat query menulis ekspresi yang persis sama dengan definisi index:

sql
index-jsonb.sql
-- Index expression pada hasil ekstraksi jsonb: GIN atas teks di dalam dokumen.
CREATE INDEX idx_produk_keterangan_ts
    ON produk USING gin (to_tsvector('simple', atribut->>'keterangan'));

-- Query harus menulis expression yang persis sama dengan definisi index.
SELECT id, nama, atribut->>'keterangan' AS keterangan
FROM produk
WHERE to_tsvector('simple', atribut->>'keterangan') @@ to_tsquery('simple', 'kedap')
ORDER BY id;

Output yang Dihasilkan:

text
CREATE INDEX
 id |        nama         |               keterangan               
----+---------------------+----------------------------------------
  1 | Kopi Toraja Arabika | biji kopi 250 gram kemasan kedap udara
(1 row)

Rencana eksekusinya bisa dibuktikan dengan EXPLAIN. Karena tabel contoh hanya enam baris, planner memilih Seq Scan secara default — file berikut mematikannya sementara untuk memperlihatkan rencana berbasis index (teknik diagnosa yang juga kita pakai di Section Indexes), dengan atribut manual="true" karena angka biaya bervariasi antar lingkungan:

sql
explain-index-jsonb.sql
-- Bukti index dipakai: matikan Seq Scan karena tabel contoh hanya 6 baris.
SET enable_seqscan = off;

EXPLAIN SELECT id, nama
FROM produk
WHERE to_tsvector('simple', atribut->>'keterangan') @@ to_tsquery('simple', 'kedap');

RESET enable_seqscan;

Output yang Dihasilkan:

text
SET
                                                    QUERY PLAN                                                    
------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on produk  (cost=8.53..12.79 rows=1 width=36)
   Recheck Cond: (to_tsvector('simple'::regconfig, (atribut ->> 'keterangan'::text)) @@ '''kedap'''::tsquery)
   ->  Bitmap Index Scan on idx_produk_keterangan_ts  (cost=0.00..8.53 rows=1 width=0)
         Index Cond: (to_tsvector('simple'::regconfig, (atribut ->> 'keterangan'::text)) @@ '''kedap'''::tsquery)
(4 rows)

RESET

Baris Bitmap Index Scan on idx_produk_keterangan_ts adalah buktinya: ekspresi to_tsvector('simple', atribut->>'keterangan') di klausa WHERE cocok persis dengan definisi index, sehingga GIN dipakai untuk menemukan kandidat sebelum baris heap diperiksa ulang (Recheck Cond).

Peta Ringkas Pencarian pada Data Semi-Terstruktur #

Kebutuhan	Pola	Index pendukung
Cari di satu field dokumen	`to_tsvector(cfg, kolom->>'field') @@ query`	GIN expression pada field itu
Cari di seluruh dokumen	`to_tsvector(cfg, kolom_jsonb) @@ query`	GIN expression pada seluruh kolom
Cari lintas kolom + field	Gabung `
Cari seluruh baris	`to_tsvector(cfg, to_jsonb(baris))`	GIN expression (pertimbangkan Stored Generated Column)
Tampilkan elemen yang cocok	`jsonb_array_elements` + filter per elemen	Pindai lateral (kecil) atau index per field

Satu catatan praktis menutup tabel ini: bila vektor gabungan dipakai berkali-kali, pertimbangkan generated column tsvector GENERATED ALWAYS AS (...) STORED — materi chapter Generated Columns — supaya ekspresi panjang itu hidup sekali di skema, bukan diulang di setiap query.

Dengan itu, perjalanan Section Full-Text Search tuntas: dari konsep lexeme sampai pencarian di dalam dokumen JSONB. Berikutnya kita masuk ke wilayah tempat logika bertemu data secara permanen — function, stored procedure, dan blok PL/pgSQL — diawali chapter Pengenalan PL/pgSQL.

Referensi #

Berkas Sumber

Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.

sql
-- Katalog produk Toko Maju Jaya: teks tersebar di kolom biasa DAN di dalam jsonb.
-- Array 'ulasan' berisi ulasan pelanggan yang juga perlu bisa dicari.
CREATE TABLE produk (
    id        integer PRIMARY KEY,
    nama      text NOT NULL,
    harga     integer NOT NULL,
    deskripsi text NOT NULL,
    atribut   jsonb NOT NULL
);

INSERT INTO produk VALUES
 (1, 'Kopi Toraja Arabika', 75000,
  'kopi arabika giling halus dari dataran tinggi toraja',
  '{"asal": "Sulawesi Selatan",
    "keterangan": "biji kopi 250 gram kemasan kedap udara",
    "ulasan": [{"isi": "kopinya enak dan aromanya kuat", "pengulas": "Budi"},
               {"isi": "kemasan rapi sampai tujuan", "pengulas": "Siti"}]}'),
 (2, 'Kopi Robusta Lampung', 60000,
  'kopi robusta dengan rasa pekat',
  '{"asal": "Lampung",
    "keterangan": "kopi bubuk 500 gram",
    "ulasan": [{"isi": "rasanya pekat pas untuk kopi susu", "pengulas": "Budi"}]}'),
 (3, 'Teh Melati Premium', 25000,
  'teh melati kering pilihan',
  '{"asal": "Jawa Barat",
    "keterangan": "teh celup 100 kantong",
    "ulasan": [{"isi": "tehnya wangi dan segar", "pengulas": "Siti"}]}'),
 (4, 'Teh Hijau Organik', 30000,
  'teh hijau tanpa pemanis',
  '{"asal": "Jawa Tengah",
    "keterangan": "daun teh kering 200 gram",
    "ulasan": []}'),
 (5, 'Gula Aren Cair', 45000,
  'gula aren cair untuk kopi dan teh',
  '{"asal": "Kalimantan Barat",
    "keterangan": "botol 500 ml",
    "ulasan": [{"isi": "manisnya pas untuk kopi", "pengulas": "Budi"}]}'),
 (6, 'Beras Melati Putih', 68000,
  'beras melati pulen untuk nasi gurih',
  '{"asal": "Jawa Timur",
    "keterangan": "karung 5 kilogram",
    "ulasan": [{"isi": "nasinya pulen dan wangi", "pengulas": "Siti"}]}');

SELECT id, nama, harga, atribut->>'asal' AS asal
FROM produk
ORDER BY id;

sql
-- to_tsvector menerima jsonb langsung: semua nilai STRING dalam dokumen diindeks.
SELECT to_tsvector('simple', atribut) AS vektor_dokumen
FROM produk
WHERE id = 1;

-- Pola jalur: ekstrak satu field dengan ->> lalu proses seperti teks biasa.
SELECT id, nama
FROM produk
WHERE to_tsvector('simple', atribut->>'keterangan') @@ to_tsquery('simple', 'bubuk')
ORDER BY id;

-- Vektor seluruh dokumen menembus struktur: 'wangi' ditemukan di DALAM array ulasan.
SELECT id, nama
FROM produk
WHERE to_tsvector('simple', atribut) @@ to_tsquery('simple', 'wangi')
ORDER BY id;

sql
-- Bukti index dipakai: matikan Seq Scan karena tabel contoh hanya 6 baris.
SET enable_seqscan = off;

EXPLAIN SELECT id, nama
FROM produk
WHERE to_tsvector('simple', atribut->>'keterangan') @@ to_tsquery('simple', 'kedap');

RESET enable_seqscan;

sql
-- Gabungkan kolom biasa dan field jsonb menjadi satu vektor pencarian.
-- Tanda kurung pada (atribut->>'keterangan') wajib: tanpa itu operator || akan
-- mencoba menggabungkan teks dengan seluruh kolom atribut dan gagal.
SELECT id, nama,
       to_tsvector('simple', nama || '. ' || deskripsi || '. ' || (atribut->>'keterangan')) AS vektor_gabungan
FROM produk
WHERE id = 1;

-- 'kilogram' hanya hidup di field keterangan produk 6.
SELECT id, nama
FROM produk
WHERE to_tsvector('simple', nama || '. ' || deskripsi || '. ' || (atribut->>'keterangan')) @@ to_tsquery('simple', 'kilogram')
ORDER BY id;

-- Trik baris utuh: bungkus seluruh baris menjadi jsonb, lalu to_tsvector.
-- Semua nilai string ikut (nama, deskripsi, seluruh atribut); harga numerik dilewati.
SELECT id, nama,
       to_tsvector('simple', to_jsonb(p)) AS vektor_baris
FROM produk AS p
WHERE id = 1;

SELECT id, nama
FROM produk AS p
WHERE to_tsvector('simple', to_jsonb(p)) @@ to_tsquery('simple', 'arabika')
ORDER BY id;

sql
-- Index expression pada hasil ekstraksi jsonb: GIN atas teks di dalam dokumen.
CREATE INDEX idx_produk_keterangan_ts
    ON produk USING gin (to_tsvector('simple', atribut->>'keterangan'));

-- Query harus menulis expression yang persis sama dengan definisi index.
SELECT id, nama, atribut->>'keterangan' AS keterangan
FROM produk
WHERE to_tsvector('simple', atribut->>'keterangan') @@ to_tsquery('simple', 'kedap')
ORDER BY id;

sql
-- Bongkar array ulasan dengan jsonb_array_elements, lalu cari di tiap elemennya.
-- 'pulen' hanya ada di isi ulasan produk 6.
SELECT p.id, p.nama, u->>'isi' AS isi_ulasan
FROM produk p,
     jsonb_array_elements(p.atribut->'ulasan') AS u
WHERE to_tsvector('simple', u->>'isi') @@ to_tsquery('simple', 'pulen')
ORDER BY p.id;

-- Perkata dalam ulasan pun bisa dicari: 'wangi' muncul di dua produk berbeda.
SELECT p.id, p.nama, u->>'isi' AS isi_ulasan
FROM produk p,
     jsonb_array_elements(p.atribut->'ulasan') AS u
WHERE to_tsvector('simple', u->>'isi') @@ to_tsquery('simple', 'wangi')
ORDER BY p.id;

-- Produk tanpa ulasan (array kosong) ikut terbongkar: jumlah elemennya nol.
SELECT p.id, p.nama, count(u) AS jumlah_ulasan
FROM produk p
LEFT JOIN jsonb_array_elements(p.atribut->'ulasan') AS u ON true
GROUP BY p.id, p.nama
ORDER BY p.id;