Dapatkah Anda menyimpan json di mysql?

MySQL mendukung tipe data JSON asli yang mendukung validasi otomatis dan penyimpanan yang dioptimalkan serta akses dokumen JSON. Meskipun data JSON sebaiknya disimpan dalam database NoSQL seperti MongoDB, Anda mungkin masih menemukan tabel dengan data JSON dari waktu ke waktu. Di bagian pertama postingan ini, kami akan memperkenalkan cara mengekstrak data dari kolom JSON di MySQL dengan pernyataan sederhana. Dan di bagian kedua, kami akan memperkenalkan cara menggabungkan data dari tabel MySQL ke dalam array atau objek JSON yang kemudian dapat digunakan dengan nyaman di aplikasi Anda

Sistem yang akan disiapkan mirip dengan yang diperkenalkan di artikel sebelumnya tentang cara mengeksekusi kueri SQL dengan Python. Jika Anda telah mengatur sistem sesuai dengan petunjuk di artikel itu, Anda dapat melanjutkan ke bagian berikutnya. Jika tidak, Anda dapat mengikuti petunjuk yang disederhanakan di bawah ini untuk mengatur sistem Anda. Untuk penjelasan rinci tentang perintah dan opsi, silakan merujuk ke artikel sebelumnya

Pada dasarnya, kami akan memulai server MySQL lokal dalam wadah Docker

Anda dapat menjalankan kueri SQL di konsol yang dimulai di atas secara langsung. Alternatifnya, jika Anda lebih suka menggunakan antarmuka grafis, Anda dapat menginstal dan menggunakan DBeaver, yang merupakan manajer basis data grafis yang bagus untuk semua jenis basis data. Sangat layak dicoba jika Anda kesulitan dengan meja kerja MySQL. Untuk detail lebih lanjut tentang cara memasang dan menyiapkan DBeaver, artikel ini memiliki ringkasan singkat namun bermanfaat

Mari kita jelajahi fungsi dan operator umum MySQL yang dapat digunakan untuk mengekstrak data dari kolom JSON

Ada dua jenis utama nilai JSON di MySQL

• Larik JSON — Daftar nilai yang dipisahkan dengan koma dan dilampirkan dalam tanda kurung siku ([])
• Objek JSON — Sebuah kamus/hashmap/objek (namanya berbeda dalam bahasa pemrograman yang berbeda) dengan sekumpulan pasangan kunci-nilai yang dipisahkan dengan koma dan diapit dalam tanda kurung kurawal ({})

Array dan objek JSON dapat disarangkan satu sama lain, seperti yang akan kita lihat nanti

Kita dapat menggunakan fungsi JSON_EXTRACT_ untuk mengekstrak data dari bidang JSON. Sintaks dasarnya adalah

Untuk larik JSON, jalur ditentukan dengan $[index], dengan indeks dimulai dari 0

Untuk objek JSON, jalur ditentukan dengan $.key, di mana key adalah kunci objek

Jika hanya ada dua argumen untuk JSON_EXTRACT seperti yang digunakan di atas, kita dapat menggunakan operator -> yang merupakan alias untuk JSON_EXTRACT. Untuk mendemonstrasikan penggunaan operator ini, kita perlu memiliki tabel dengan field JSON. Harap salin kueri SQL berikut dan jalankan di konsol MySQL atau DBeaver

Terutama, MySQL menangani string yang digunakan dalam konteks JSON menggunakan rangkaian karakter mysql> SELECT JSON_EXTRACT('[10, 20, 30, 40]', '$[0]');
+------------------------------------------+
| JSON_EXTRACT('[10, 20, 30, 40]', '$[0]') |
+------------------------------------------+
| 10 |
+------------------------------------------+0 dan susunan mysql> SELECT JSON_EXTRACT('[10, 20, 30, 40]', '$[0]');
+------------------------------------------+
| JSON_EXTRACT('[10, 20, 30, 40]', '$[0]') |
+------------------------------------------+
| 10 |
+------------------------------------------+1. Himpunan karakter adalah himpunan simbol dan pengkodean, dan collation adalah himpunan aturan untuk membandingkan karakter dalam himpunan karakter. Lebih baik membuat tabel dengan bidang JSON menggunakan kumpulan karakter dan susunan yang sesuai

Karena mysql> SELECT JSON_EXTRACT('[10, 20, 30, 40]', '$[0]');
+------------------------------------------+
| JSON_EXTRACT('[10, 20, 30, 40]', '$[0]') |
+------------------------------------------+
| 10 |
+------------------------------------------+_1 adalah susunan biner, kuncinya peka terhadap huruf besar-kecil dan kita perlu menentukannya dengan huruf besar-kecil yang benar

Sekarang kita dapat menggunakan operator ->_ untuk mengekstrak data dari bidang JSON

Seperti yang kita lihat, -> hanyalah jalan pintas atau alias untuk JSON_EXTRACT

Menariknya, kutipan masih ada untuk mysql> SELECT JSON_EXTRACT('[10, 20, 30, 40]', '$[0]');
+------------------------------------------+
| JSON_EXTRACT('[10, 20, 30, 40]', '$[0]') |
+------------------------------------------+
| 10 |
+------------------------------------------+6 dan mysql> SELECT JSON_EXTRACT('[10, 20, 30, 40]', '$[0]');
+------------------------------------------+
| JSON_EXTRACT('[10, 20, 30, 40]', '$[0]') |
+------------------------------------------+
| 10 |
+------------------------------------------+7. Ini bukan yang kita inginkan. Kami ingin tanda kutip dihapus, mirip dengan bidang mysql> SELECT JSON_EXTRACT('[10, 20, 30, 40]', '$[0]');
+------------------------------------------+
| JSON_EXTRACT('[10, 20, 30, 40]', '$[0]') |
+------------------------------------------+
| 10 |
+------------------------------------------+8

Untuk menghapus tanda kutip dari nilai yang diekstrak, kita perlu menggunakan fungsi mysql> SELECT JSON_EXTRACT('[10, 20, 30, 40]', '$[0]');
+------------------------------------------+
| JSON_EXTRACT('[10, 20, 30, 40]', '$[0]') |
+------------------------------------------+
| 10 |
+------------------------------------------+9. Karena mysql> SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');
+-------------------------------------------------------+
| JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') |
+-------------------------------------------------------+
| "John" |
+-------------------------------------------------------+_0 sangat umum digunakan, ada juga operator pintasan untuk kombinasi ini, yaitu mysql> SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');
+-------------------------------------------------------+
| JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') |
+-------------------------------------------------------+
| "John" |
+-------------------------------------------------------+1. Mari kita lihat dalam praktiknya

Ini membuktikan bahwa mysql> SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');
+-------------------------------------------------------+
| JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') |
+-------------------------------------------------------+
| "John" |
+-------------------------------------------------------+1 dan mysql> SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');
+-------------------------------------------------------+
| JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') |
+-------------------------------------------------------+
| "John" |
+-------------------------------------------------------+3 memiliki hasil yang sama. Karena mysql> SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');
+-------------------------------------------------------+
| JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') |
+-------------------------------------------------------+
| "John" |
+-------------------------------------------------------+1 jauh lebih sedikit untuk diketik, ini lebih disukai dalam banyak kasus

Namun, jika Anda ingin mengekstrak data dari array JSON bersarang atau objek JSON, Anda tidak dapat menggunakan rantai -> atau mysql> SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');
+-------------------------------------------------------+
| JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') |
+-------------------------------------------------------+
| "John" |
+-------------------------------------------------------+1. Anda hanya dapat menggunakan -> dan mysql> SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');
+-------------------------------------------------------+
| JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') |
+-------------------------------------------------------+
| "John" |
+-------------------------------------------------------+1 untuk tingkat atas dan perlu menggunakan JSON_EXTRACT untuk tingkat bersarang. Mari kita ekstrak skor untuk setiap siswa

Bersulang. Ini berfungsi seperti yang diharapkan

Pengambilan kunci untuk mengekstraksi data dari bidang JSON di MySQL

• Gunakan $.key untuk mengekstrak nilai kunci dari objek JSON
• Gunakan $[index] untuk mengekstrak nilai elemen dari larik JSON
• Gunakan -> sebagai jalan pintas untuk JSON_EXTRACT jika nilainya bukan string
• Gunakan mysql> SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');
  +-------------------------------------------------------+
  | JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') |
  +-------------------------------------------------------+
  | "John" |
  +-------------------------------------------------------+_1 sebagai jalan pintas untuk mysql> SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');
  +-------------------------------------------------------+
  | JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') |
  +-------------------------------------------------------+
  | "John" |
  +-------------------------------------------------------+3 jika nilainya adalah string dan Anda ingin menghapus tanda kutip untuk string yang diekstraksi
• Jika Anda ingin mengekstrak data dari array JSON bersarang atau objek JSON, Anda tidak dapat menggunakan rantai -> atau mysql> SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');
  +-------------------------------------------------------+
  | JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') |
  +-------------------------------------------------------+
  | "John" |
  +-------------------------------------------------------+1. Anda hanya dapat menggunakan -> dan mysql> SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');
  +-------------------------------------------------------+
  | JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') |
  +-------------------------------------------------------+
  | "John" |
  +-------------------------------------------------------+1 untuk tingkat atas dan perlu menggunakan JSON_EXTRACT untuk tingkat bersarang

Ada cukup banyak fungsi lain untuk bekerja dengan data JSON di MySQL. Namun, jika Anda perlu menggunakan fungsi ini untuk memvalidasi/mencari bidang JSON Anda atau melakukan operasi CRUD di atasnya, Anda harus mempertimbangkan dengan serius untuk menggunakan MongoDB untuk menyimpan bidang JSON. MongoDB jauh lebih profesional dan nyaman dalam menangani data (dokumen) yang tidak terstruktur

Di atas kami telah memperkenalkan cara mengekstrak nilai dari bidang JSON di MySQL. Sekarang kita akan mempelajari kebalikannya dan mengeksplorasi cara memilih data JSON dari tabel MySQL. Untuk melanjutkan bagian ini, kami memerlukan beberapa data dummy untuk dimainkan. Silakan salin kueri SQL berikut dan jalankan di konsol MySQL atau DBeaver

Untuk tabel ini, karakter default dan susun digunakan. Dengan dua kueri ini, kami membuat tabel yang menyimpan data yang diekstrak dari bagian pertama. Ini adalah tugas umum untuk pemipaan dan analisis data, yaitu melakukan beberapa analisis data setelah pembersihan data. Dalam praktiknya, Anda ingin menyimpan skor dalam tabel terpisah sehingga tabel lebih ternormalisasi. Namun, di sini data diletakkan dalam tabel yang sama untuk memudahkan demonstrasi

Kita sekarang dapat menggabungkan data ke dalam larik JSON dengan fungsi $[index]1

Kita juga dapat mengagregasi data menjadi objek JSON menggunakan fungsi $[index]2

Data agregat kemudian dapat digunakan dalam aplikasi Anda secara langsung. $[index]_1 dan $[index]2 dapat menghemat upaya Anda untuk menggabungkan data dalam aplikasi Anda dan terkadang berguna. Misalnya, Anda kemudian dapat menggunakan metode $[index]5 untuk mengonversi string JSON menjadi larik atau kamus dengan Python

Jika Anda perlu menjalankan kueri SQL biasa dari $[index]1 dan $[index]2 dengan Python, Anda dapat menggunakan paket SQLAlchemy seperti yang ditunjukkan dalam posting ini

Pada artikel ini, kami telah memperkenalkan cara bekerja dengan data JSON di MySQL. Di bagian pertama, fungsi dan operator yang digunakan untuk mengekstrak data dari kolom JSON dibahas dengan contoh sederhana. Dan di bagian kedua, kami melakukan kebalikannya dan mengagregasi data yang dinormalisasi ke dalam array atau objek JSON yang kemudian dapat digunakan langsung di program Anda. Biasanya kita harus menghindari penyimpanan data (dokumen) non-terstruktur di MySQL. Namun, jika tidak bisa dihindari, pengetahuan dalam artikel ini seharusnya bisa membantu pekerjaan Anda

Bagaimana cara menambahkan JSON di MySQL?

Apakah menyimpan JSON dalam SQL adalah ide yang bagus?

Bisakah kita menyimpan JSON di database?

Bagaimana cara mendapatkan data JSON di MySQL?