PostgreSQL JSONB Sekali Lagi Menyelesaikan Masalah Data ​

Kembali lagi ke topik pemrograman, kebanyakan pekerjaan saya di akhir-akhir ini adalah data dan json, dimana ada puluhan ribu data agar bisa di proses dengan cepat oleh sistem dan akan berlipat ganda di setiap tahunnya.

JSONB pada PostgreSQL sudah saya gunakan dari kantor lama saya, mungkin di sekitar tahun 2019, hanya saja sering kali di keluhkan karena lebih sering menimbulkan masalah baru seperti json string yang tidak valid dimana-mana, array dan object yang bergabung, dll.

Sekilas saja, selain dari end user yang support penggunaan aplikasi dengan baik, sistem atau aplikasi juga perlu selalu memenuhi kriteria yang dibutuhkan user lapangan, kebanyakan darinya adalah penyempurnaan yang berupa kelengkapan atribut pada tabel di database dan entitas input dan output pada aplikasi. Hanya saja alter table bukanlah hal yang simpel, migration adalah metode paling mudah untuk mengatasi masalah tersebut, tapi jika perombakan yang masif?

Memang ada beberapa standar yang saya lewati dalam pengembangan hanya untuk menyajikan fitur dengan cepat sebagai seorang diri, seperti validasi input, migration, unit test, dan yang paling sering adalah masuk langsung ke production server.

Mari kembali ke topik utama yang kita bahas, sebelumnya saya sudah memiliki tabel master guru dengan bentuk kurang lebih seperti berikut,

Seperti yang saya bilang ada penambahan untuk kebutuhan user lapangan untuk memisahkan beberapa data jadi 3 kriteria bisa disebutkan sebagai tpg, tkg, dan tamsil, dan dalam satu orang bisa juga mendapatkan dua kriteria tersebut. Solusi? membuat tabel baru kriteria guru? atau membuat kolom enum? kita buat cara mudah.

Penambahan kriteria ini kurang lebih cukup menambahkan kolom enum (Y/T) pada setiap kriteria seperti, is_tpg, is_tkg, dan is_tamsil, hanya saja aturan saya adalah tidak melibatkan alter tabel, maka itulah kenapa saya menyiapkan kolom info jsonb pada tabel. Cukup hanya memasukan kolom tambahan ke dalam bentuk json dan memasukan ke dalam kolom jsonb.

Jika dalam penampilan aplikasi kurang lebih adalah input checkbox sebagai berikut,

Karena dalam hal ini saya seperti biasa menggunakan laravel, maka kode untuk input ke dalam query builder adalah seperti berikut begitu juga dengan kolom lainnya,

....
'catatan' => request()->catatan,
'info' => json_encode([
    'alamat' => request()->alamat,
    'norek' => request()->norek,
    'nuptk' => request()->nuptk,
    'penerima_tpg' => request()->penerima_tpg,
    'penerima_tkg' => request()->penerima_tkg,
    'penerima_tamsil' => request()->penerima_tamsil,
]),
....

Tidak hanya ketika insert dan update tapi select juga perlu di sesuaikan agar bentuk jsonb dianggap menjadi kolom yang valid yang menjadikan select dengan sedikit kriteria manual,

....
->select([
    'g.*',
    DB::raw("g.info->>'alamat' AS alamat"),
    DB::raw("g.info->>'norek' AS norek"),
    DB::raw("g.info->>'nuptk' AS nuptk"),
    DB::raw("g.info->>'penerima_tpg' AS penerima_tpg"),
    DB::raw("g.info->>'penerima_tkg' AS penerima_tkg"),
    DB::raw("g.info->>'penerima_tamsil' AS penerima_tamsil"),
    's.uraiskpd',
    'su.uraisubunit'
])
....

Ini baru pemanasan, karena hal yang mudah hanya untuk melakukan operasi CRUD saja, tapi kita akan lanjut ke bentuk pengelolaan data berikutnya,

Terdapat empat atau bahkan 5 raw query yang saya merasa agak janggal, yaitu ketika menampilkan data tersebut menjadi kelompok data, diri saya yang lampau sudah membuat query tersebut dengan mudah yaitu memfilter masing-masing penerima dan melakukan union untuk menggabungkannya kembali, potongan kode tersebut kurang lebih seperti berikut,

select concat(g.guru_asn, '-', 'tpg') as key,
    g.id, g.tahun, g.subunit_id, g.mutasi, g.nip, g.nama, bt.nilai, bt.potpph, bt.potjkn
from sitna.guru g
join sitna.belanja_tunj bt
    on bt.guru_id = g.id and bt.info->>'tunjangkey' = concat(g.guru_asn, '-', 'tpg')
where g.info->>'penerima_tpg' = 'Y'
union
select concat(g.guru_asn, '-', 'tkg') as key,
    g.id, g.tahun, g.subunit_id, g.mutasi, g.nip, g.nama, bt.nilai, bt.potpph, bt.potjkn
from sitna.guru g
join sitna.belanja_tunj bt
    on bt.guru_id = g.id and bt.info->>'tunjangkey' = concat(g.guru_asn, '-', 'tkg')
where g.info->>'penerima_tkg' = 'Y'
union
select concat(g.guru_asn, '-', 'tamsil') as key,
    g.id, g.tahun, g.subunit_id, g.mutasi, g.nip, g.nama, bt.nilai, bt.potpph, bt.potjkn
from sitna.guru g
join sitna.belanja_tunj bt
    on bt.guru_id = g.id and bt.info->>'tunjangkey' = concat(g.guru_asn, '-', 'tamsil')
where g.info->>'penerima_tamsil' = 'Y'

Dari total 1172 data pada tabel guru, maka dengan join dan union diatas menjadikan data tertampil 2k dari 2853 record dalam hasil run 0.173s, sebenarnya sudah cukup cepat tidak ada masalah, tapi apakah union penyelesaian masalah kita? bagaiman jika ada klasifikasi baru selain dari 3 kelompok data tersebut? apakah menambahkan query dan union lagi?

Ada banyak pintasan jsonb yang disediakan oleh PostgreSQL, oh iya dalam hal ini saya menggunakan versi postgres 13.11 pada lokal saya dan lebih baru pada versi di server, harusnya dalam versi yang lebih baru memiliki fungsi yang lebih canggih. Pintasan atau fungsi bawaan seperti jsonb_array_elements, jsonb_each_text, jsonb_build_object dan masih banyak lagi, untuk masalah diatas saya akan menggunakan jsonb_object_keys, dalam ujicoba jsonb_object_keys akan menampilkan keys dari object json yang kita buat yang menjadikan data akan tampil seperti berikut,

Seperti yang saya butuhkan untuk menampilkan data sesuai dengan klasifikasi yang dibutuhkan, tapi kita belum selesai, dengan sedikit ramuan query maka akan menghasilkan data yang kita inginkan kurang lebih sebagai berikut,

Tepat seperti yang saya inginkan, kemudian langkah terakhir adalah mencoba untuk melakukan join ke data raw query yang sebelumnya diri saya pernah buat, yang menjadikan produk akhir dari query yang saya buat adalah sebagai berikut,

select concat(g.guru_asn, '-', replace(pen, 'penerima_', '')) as key,
	g.id, g.tahun, g.subunit_id, g.mutasi, g.nip, g.nama, bt.nilai, bt.potpph, bt.potjkn
from sitna.guru g
join jsonb_object_keys(g.info) pen on left(pen, 9) = 'penerima_' and g.info->>pen = 'Y'
join sitna.belanja_tunj bt
	on bt.guru_id = g.id and bt.info->>'tunjangkey' = concat(g.guru_asn, '-', replace(pen, 'penerima_', ''))

Sepertinya jadi kurang enak dipandang, tapi lihat query panjang yang saya buat dengan menggabungkan 3 query menjadi satu dengan union sudah tidak diperlukan lagi, kedua data yang tampil 2k dari 2853 record yang masih sama, dengan improvisasi run query menjadi 0.102s, ini adalah 1.70x lebih cepat bisa jadi 41.04% lebih baik? semoga iya,

Kenapa di judul sekali lagi, karena memang ada banyak masalah yang bisa di selesaikan dengan base tools basis data ini, seperti PostGIS, PostgRest, PGMQ, Supabase, dll. Saya hanya berharap diri saya di masa depan bangga dan tidak menambah beban pekerjaan karena sistem yang kewalahan menangani data.

Jika ditanya saya tidak all in di postgres dalam setiap masalah, kebanyakan dari third party aplikasi seperti s3 mungkin sebelumnya menggunakan minio sekarang dengan garagehq dan meta data pada sqlite menjadikan saya harus pemberbaiki database yang corrupt karena listrik padam, dengan 83152 object storage, dan 12GB file sqlite mungkin saya akan membuat artikel tentang ini kedepan,

Semoga membantu.