Mengolah data JSON di MySQL

Bermain dengan data JSON di MySQL, dibilang susah ya ngga, dibilang gampang juga ngga.

7 min readDec 19, 2018

Halo developer! Kali ini gue mau berbagi ilmu tentang menggunakan tipe data JSON di MySQL. Untuk yang pernah main JSON tentu aja pasti nggak asing sama tipe data yang satu ini, selain enak dibaca, mudah juga untuk dioprek. Pemilihan tipe data JSON ini juga bisa dibilang cukup bijaksana sesuai kebutuhan kok. Okedeh langsung aja ya.

Artikel ini ditujukan untuk developer yang sudah memahami JSON, serta kasus-kasus yang berhubungan dengan JSON Encode, JSON parse, bermain dengan array dan sejenisnya. Apabila kamu adalah seorang developer newbie, artikel ini kurang cocok dikarenakan bisa saja kamu bingung membaca kontennya.

Di tutorial kali ini, kita akan membuat sebuah database sosial media seperti wordpress. Lalu kita hanya akan membuat dua table, yaitu:

pengguna
artikel

Namun, fitur yang akan kita berikan adalah, pengguna dapat membaca artikel dari pengguna lainnya, memberikan likes, dan melakukan bookmark pada sebuah artikel.

Mungkin yang ada difikiran agan-agan sekalian ini pasti agan akan membuat dua table baru yaitu table bookmark dan likes. Namun pada tutorial kali ini kita akan mengurangi jumlah table dan memanfaatkan tipe data JSON.

Banyak sekali debat kusir antar developer mengenai performa JSON vs Row di MySQL, disini saya tidak akan melakukan komparasi terkait hal tersebut. Namun perlu diperhatikan, sebagai developer kita mempunyai tugas tidak hanya membuat aplikasi saja. Namun, kita juga perlu memperhatikan performa aplikasi yang akan kita buat di masa mendatang. Sehingga ada baiknya kita perlu melakukan pemetaan sebelum membuat sebuah struktur database. Pemilihan JSON vs Row kemungkinan akan saya bahas di artikel selanjutnya (apabila saya mood hehehe). Untuk saat ini fokus saya adalah berbagi insight mengenai tipe data JSON.

Daftar debat kusir:

MySQL: Embedded JSON vs table

I'm designing the database schema for a video production project management app and struggling with how to persist some…

stackoverflow.com

Oke, langsung saja. Pertama kali saya akan membuat database bernama media_saya.

Kedua, kita akan membuat dua table saja. Masukkan script SQL dibawah ini yang telah saya siapkan.

-- MySQL dump 10.13  Distrib 5.7.23, for Linux (x86_64)
--
-- Host: 192.168.10.10    Database: media_saya
-- ------------------------------------------------------
-- Server version	5.7.22-0ubuntu18.04.1

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `article`
--

DROP TABLE IF EXISTS `article`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `title` varchar(200) NOT NULL,
  `content` varchar(45) NOT NULL,
  `bookmark` json DEFAULT NULL,
  `likes` json DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_owner_idx` (`user_id`),
  CONSTRAINT `fk_owner` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `article`
--

LOCK TABLES `article` WRITE;
/*!40000 ALTER TABLE `article` DISABLE KEYS */;
INSERT INTO `article` VALUES (1,1,'Matahari Terbit','Lorem Ipsum','[]','[]'),(2,2,'Bersinar Kembali','Ipsum Lorem','[]','[]');
/*!40000 ALTER TABLE `article` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `users`
--

DROP TABLE IF EXISTS `users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `email` varchar(200) NOT NULL,
  `password` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `users`
--

LOCK TABLES `users` WRITE;
/*!40000 ALTER TABLE `users` DISABLE KEYS */;
INSERT INTO `users` VALUES (1,'Kiddy','kiddykeren@gmail.com','123456'),(2,'Andi','andi@gmail.com','1234567'),(3,'Budi','budi@gmail.com','0987654321'),(4,'Cindy','cindy@gmail.com','098765432');
/*!40000 ALTER TABLE `users` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping events for database 'media_saya'
--

--
-- Dumping routines for database 'media_saya'
--
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2018-12-18 21:10:59

Catatan: Bermain dengan JSON sangatlah TRICKY. Mereka cukup sensitif kaya cewe, apa maksudnya? JSON tidak boleh memiliki data default, oleh karena itu ketika kita melakukan INSERT, perlu menambahkan sebuah array ([]) pada kolom bookmark dan likes seperti yang saya lakukan diatas. Gunanya adalah agar array JSON sudah siap ketika kita akan melakukan penambahan data.

Ketiga, kita akan coba melakukan bookmark.

Kiddy sebagai user ID nomor satu akan melakukan Bookmark pada artikel milik Andi.

Script yang akan kita jalankan adalah:

UPDATE article SET bookmark = JSON_MERGE(bookmark, "1") WHERE id = 2;

Sekarang Kiddy telah melakukan bookmark pada artikel dengan ID 2.

Sekarang kita masuk pada kasus nomor dua, biasanya pada frontend kita akan memberi tanda bookmark untuk artikel yang di bookmark. Dengan memasukkan ID kedalam array bookmark, kita dapat dengan mudah mengetahui apakah si empunya ID telah melakukan bookmark pada artikel saat melakukan SELECT * FROM article. Umumnya atribut ini diberi nama JSON “is_bookmark” dimana saat user melihat artikel yang di load pada halaman web, frontend developer dapat dengan mudah memberi tanda bookmark pada artikel yang telah di bookmark.

Backend Developer harus bermain dengan logika disini, ketika Foreach dilakukan untuk memasukkan data “is_like”, lakukan Decode JSON atau buat agar value bookmark [1] menjadi sebuah array, sehingga kamu dapat dengan mudah melakukan fungsi check apakah user id yang merequest ada didalam array tersebut (in_array). Jika iya maka beri tanda 1 pada JSON “is_like”, jika tidak ada maka beri tanda 0 pada JSON “is_like”.

Kelemahan JSON

Setiap yang punya kelebihan pasti punya kekurangan, nah kelemahannya ini lebih saat kita memodifikasi value dari kolom JSON kita, yaitu:

Sulit untuk menghapus array berisi value integer pada JSON, hal ini karena JSON_SEARCH di MySQL hanya mencari value di array yang bertipe string. (Referensi https://bugs.mysql.com/bug.php?id=79316)
Apabila array yang kita cari tidak ditemukan saat update maka akan membuat kolom menjadi null.

Dari dua masalah diatas pasti akan ada seseorang atau mungkin saja agan akan berkata “ah lo searchingnya kurang jago” “ah ini gua ketemu”. Saya udah ngulik ini sekitar 2–3 minggu dan hasilnya nihil. Saya test dengan berbagai kasus lho gak cuma satu kasus jadi saya bilang nihil ini bukan isapan jempol, mulai dari ARRAY JSON, OBJECT JSON. Ada beberapa case yang memang bisa diselesaikan dengan Query SQL, tapi ada juga beberapa case yang tidak bisa diselesaikan, contohnya:

Object JSON bertipe Array yang memiliki value Integer
Array Object yang memiliki lebih dari satu object, contohnya
[{“ip_address” : “192.168.1.1”, “agent” : “Mozilla”, “visit_time” : “12387123127”}, {“ip_address” : “292.118.1.1”, “agent” : “Mozilla”, “visit_time” : “3434343437”}, {“ip_address” : “592.128.1.1”, “agent” : “Mozilla”, “visit_time” : “125454”}]. Data seperti ini sulit diubah apabila kita ingin mengubah visit time dari salah satu IP.

Yah kenyataannya, there are no best practice untuk melakukan Update value array pada MySQL ini. Caranya sangat ribet untuk dilakukan via query karena kita harus tau posisi dari array tersebut misalnya.

Solusi

Secara manusiawi, tentu aja kita pasti nyari best practice dari kelemahan diatas.

Tidak semudah itu ferguso, mengatasi dengan query MySQL menurut saya bukanlah solusi yang tepat.

Solusi yang tepat adalah bermain dengan fungsi JSON dan Array pada tiap bahasa yang digunakan.

Sebagai contoh, kita mengambil kolom bookmark bertipe JSON yang ada di table article yang berisi nilai sebagai berikut

[1, 4, 5, 6, 7, 9]

Kolom ini berisi pengguna yang melakukan bookmark pada artikel tersebut. Maka apabila seorang user dengan id = 1 ingin menghapus bookmark kita dapat mencarinya dengan library array seperti Lodash JS atau Collection Laravel yang dapat mencari id = 1. Apabila telah ditemukan pada index ke 0, kita dapat melakukan fungsi JSON_REMOVE dengan contoh query:

UPDATE article set bookmark = JSON_REMOVE(bookmark, '$[?]') where id = 2

Simbol ? (tanda tanya) akan diisi index dari si ID. Maka output yang akan ada di database sebagai berikut:

[4, 5, 6, 7, 9]

Apabila kita mau menambahkan ID tersebut cukup menggunakan fungsi JSON_MERGE.

UPDATE article SET bookmark = JSON_MERGE(bookmark, ?) WHERE id = 2

Dimana kita akan melakukan push kedalam kolom bookmark yang menghasilkan output di kolom bookmark sebagai berikut:

[4, 5, 6, 7, 9, 1]

Ribet ya? Bukan main emang kalau kita bicara masalah performa. Memang issue performance ini tidak terasa apabila kamu hanya mengerjakan aplikasi yang diakses tidak setiap hari dengan orang yang banyak.

Apabila kamu sudah bertemu dengan isu performa, salah satu solusi yang patut kita lakukan sebagai developer adalah memeriksa kembali apakah struktur kita sudah baik dan benar? Apabila belum tentu saja kita perlu memperbaikinya, beda cerita kalau perusahaan tempat kamu bekerja adalah sultan, gampang, tingkatkan saja kapasitas servernya. 😆

Tiap developer ditantang untuk punya best practice. Jika kamu dapat menciptakan best practice untuk kasus-mu sendiri, tentunya kamu telah menjadi developer yang baik karena mempunyai rumus dalam memecahkan masalahnya sendiri.

Apabila kamu memiliki insight mengenai JSON atau punya solusi yang ingin dibagikan, jangan ragu untuk komen di artikel ini ya gan ^^

Semoga bermanfaat, dan Happy Coding!