Oracle - JSON_TABLE

Pendahuluan

Struktur format data JSON (JavaScript Object Notation) secara de facto merupakan struktur format data yang dijadikan DTO (Data Transfer Object) diantara aplikasi.

Baik antara aplikasi yang terpisah, atau antara microservice di dalam sebuah aplikasi terdistribusi.

Strukturnya yang sederhana, sudah ada parsernya, sudah ada writernya, sudah ada library mappingnya ke object membuatnya menjadi pilihan utama dalam integrasi aplikasi.

Struktur data JSON ini juga biasanya disimpan di database, sehingga kita tidak perlu terlalu banyak membuat kolom-kolom terpisah untuk sekumpulan data yang banyak.

Cukup semuanya dalam satu kolom database, isinya data DTO kita dalam bentuk JSON.

Misalnya ketika kita ingin menyimpan data pengguna dari aplikasi kita:

Daripada menyimpan data alamat, pekerjaan dan access dalam kolom-kolom terpisah, kita bisa saja menyimpannya dalam sekelompok data gelondongan.

Mungkin hanya ada 3 kolom, yaitu id_pengguna, data_pengguna, dan is_active.

Data_pengguna misalnya kita simpan dalam bentuk JSON sbb :

{
  "nama": "Anonim",
  "alamat": {
    "namaJalan": "Jl. Patriot X No. 5",
    "kelurahan": "Anoa 1",
    "kecamatan": "Nabati",
    "kota": {
      "key": "OM",
      "value": "Omnivora"  
    }
  },
  "pekerjaan": "Pegawai Swasta",
  "access": [
    "read-data",
    "modify-data"
  ]
}

Format data diatas juga biasanya kita temui di database bertipe No SQL, yang memang tidak mementingkan normal form yang biasanya kita temui di database relasional.

Tipe data key:value dan salah satu contohnya dalam bentuk JSON juga bisa dipakai dalam database No SQL ini.

Jadi secara konsep, struktur format data JSON ini sangat mudah untuk disimpan sewaktu kita melakukan insert/update data.

Tetapi bagaimana dengan membaca data yang ada di JSON ?

Ok, katakanlah kita menyimpan data JSON dalam sebuah tabel di database kita.

Dalam kasus ini kita memakai Database Relasional Oracle.

Lalu ada kebutuhan untuk mendapatkan ekstraksi data dari JSON tersebut.

Misalnya struktur data seperti contoh kita diatas.

Data tentang alamat seseorang dan hak aksesnya ke aplikasi.

Di database misalnya kita cuma punya id user, data user, dan status aktif user dalam bentuk JSON dibawah ini, dengan tujuan penyederhanaan data yang kita simpan di database.

{
  "nama": "Anonim",
  "alamat": {
    "namaJalan": "Jl. Patriot X No. 5",
    "kelurahan": "Anoa 1",
    "kecamatan": "Nabati",
    "kota": {
      "key": "OM",
      "value": "Omnivora"  
    }
  },
  "pekerjaan": "Pegawai Swasta",
  "access": [
    "read-data",
    "modify-data"
  ]
}

Suatu hari ada kebutuhan dari tim bisnis untuk mendapatkan data nama, pekerjaan, dan nama jalan saja dari semua data diatas.

Mereka tidak mau semua data dalam bentuk JSON, maunya dalam bentuk table di .csv atau excel sehingga mudah di urutkan, filter, dan diapa-apain.

Cara yang biasanya kita lakukan adalah :

1. Membuat sebuah aplikasi Java untuk melakukan ekstraksi

Yaa, tentu saja, karena kita mempunyai library JSON parser, JSON writer, dan juga library mapper dari JSON ke object POJO di Java.

Dengan mudah kita bisa membuat sebuah program sederhana Java, yang isinya :

• Connect ke database relasional Oracle, pakai JPA/Hibernate.
• Mapping data JSON dengan object DTO yang kita definisikan, dan masukkan dalam List.
• Filter data dalam List, untuk mendapatkan data yang sesuai dengan kebutuhan diatas.
• Pakai library CVSUtil atau POI untuk memasukkannya dalam sebuah file .csv atau file .xlsx.
• Save filenya ke directory output.

Dan voilaaa., akhirnya kita mendapatkan data yang kita inginkan dalam bentuk .csv atau excel.

Hmmm…, tapi sepertinya terlalu rumit ya..

Untuk mendapatkan data aggregasi dari JSON sepertinya kok terlalu sulit dan banyak yang harus kita lakukan hanya untuk melakukan filtering.?

Apa tidak bisa langsung melakukan filtering di databasenya ?

Bisa ternyata..

Tetapi harus menggunakan Database Oracle 12c Release 1 (12.1.0.2) keatas.

Coba kita lihat penggunaannya di point ke 2 dibawah ini :

2. Menggunakan perintah JSON_TABLE

Mengutip dari halaman offisialnya JSON_TABLE,

JSON_TABLE membuat tampilan relasional dari JSON. Jadi JSON_TABLE akan melakukan ekstraksi data JSON ke dalam kolom dan baris seperti layaknya Table Relasional di database.

Kita coba langsung contohnya untuk JSON yang kita contohkan diatas :

{
  "nama": "Anonim",
  "alamat": {
    "namaJalan": "Jl. Patriot X No. 5",
    "kelurahan": "Anoa 1",
    "kecamatan": "Nabati",
    "kota": {
      "key": "OM",
      "value": "Omnivora"  
    }
  },
  "pekerjaan": "Pegawai Swasta",
  "access": [
    "read-data",
    "modify-data"
  ]
}

Kembali lagi dengan kebutuhan dari tim bisnis seperti diatas.

Yaitu kebutuhan untuk mendapatkan data nama, pekerjaan, dan nama jalan saja dari semua data diatas.

Misalnya kita punya table T_USER_DATA :

CREATE TABLE T_USER_DATA(
    PERSON_ID VARCHAR(1) NOT NULL,
    USER_DATA_JSON CLOB NOT NULL,
    IS_ACTIVE CAHAR(1) DEFAULT '1' NOT NULL,
    PRIMARY KEY(PERSON_ID)
);

Dan kita melakukan insert data sbb :

INSERT INTO T_USER_DATA
(PERSON_ID, USER_DATA_JSON, IS_ACTIVE)
VALUES
(1, '{"nama":"Anonim","alamat":{"namaJalan":"Jl. Patriot X No. 5","kelurahan":"Anoa 1","kecamatan":"Nabati","kota":{"key":"OM","value":"Omnivora"}},"pekerjaan":"Pegawai Swasta","access":["read-data","modify-data"]}', '1' );
);

Maka kita bisa melakukan query sbb :

SELECT jt.* FROM T_USER_DATA,
JSON_TABLE(USER_DATA_JSON, '$'
  COLUMNS
  (  nama varchar(30) PATH '$.nama',
     pekerjaan varchar(100) PATH '$.pekerjaan',
     alamat varchar(500) PATH '$.alamat.namajalan'
  )
) AS jt;

dan hasilnya :

Mantabb kaan, akhirnya kita bisa mendapatkan data seperti layaknya table di database relasional, tanpa harus mempunyai struktur relasional di database, cukup pakai format data JSON saja.

Bisa kita export ke .csv atau file excel sehingga mudah dianalisa oleh tim lain yang berkepentingan.