Apa itu json_unquote 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

Table of Contents Show

Apa itu Json_unquote () di MySQL?
Bagaimana cara mengekstrak nilai dari JSON di MySQL?
Bagaimana cara menanyakan data JSON di MySQL?
Bagaimana cara mengubah string menjadi JSON di MySQL?

Gambar dari Pixabay

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 bidang 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

JSON_EXTRACT(json_doc, path)

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

mysql> SELECT JSON_EXTRACT('[10, 20, 30, 40]', '$[0]');
+------------------------------------------+
| JSON_EXTRACT('[10, 20, 30, 40]', '$[0]') |
+------------------------------------------+
| 10                                       |
+------------------------------------------+

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

mysql> SELECT JSON_EXTRACT('{"name": "John", "age": 30}', '$.name');
+-------------------------------------------------------+
| JSON_EXTRACT('{"name": "John", "age": 30}', '$.name') |
+-------------------------------------------------------+
| "John"                                                |
+-------------------------------------------------------+

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                                       |
+------------------------------------------+

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

Apa itu Json_unquote () di MySQL?

JSON_UNQUOTE() – Hapus Kutipan dari Dokumen JSON di MySQL . Anda memberikan dokumen JSON sebagai argumen, dan fungsi akan melakukan sisanya. the JSON_UNQUOTE() function “unquotes” a JSON document and returns the result as a utf8mb4 string. You provide the JSON document as an argument, and the function will do the rest.

Bagaimana cara mengekstrak nilai dari JSON di MySQL?

Di atas kami telah memperkenalkan cara mengekstrak nilai dari bidang JSON di MySQL. .

Gunakan $. .

Gunakan $[indeks] untuk mengekstrak nilai elemen dari larik JSON

Gunakan -> sebagai jalan pintas untuk JSON_EXTRACT jika nilainya bukan string

Bagaimana cara menanyakan data JSON di MySQL?

Contoh data

Pilih bidang JSON. Hapus tanda kutip ganda dari hasil seleksi

Gunakan notasi titik di jalur pemilihan

Gunakan bidang yang dipilih sebagai syarat

Tamat

Bagaimana cara mengubah string menjadi JSON di MySQL?

Mengekspor data MySQL ke JSON menggunakan fungsi CONCAT() dan GROUP_CONCAT() . Menggunakan kombinasi fungsi CONCAT() dan GROUP_CONCAT(), data dari string SQL dapat diubah menjadi format JSON.