Cara memasukkan data json bersarang ke mysql

Saat kami melakukan survei pasar, kami melihat kebutuhan akan solusi yang dapat melakukan kueri SQL cepat pada data JSON yang lancar, termasuk array dan objek bersarang

Table of Contents Show

Bagaimana cara menyimpan JSON bersarang di database MySQL?
Bagaimana cara menyimpan JSON bersarang di basis data?
Bagaimana cara memasukkan data JSON di MySQL?
Bagaimana cara memasukkan data JSON ke MySQL menggunakan Python?

Arsitektur terbaik untuk mengonversi JSON ke SQL?
Apa cara untuk menjalankan SQL pada data JSON tanpa skema yang telah ditentukan sebelumnya?
Saya perlu database untuk mengambil JSON dan menjalankan SQL. Apa pilihan saya?

Tantangan SQL di JSON

Beberapa bentuk ETL untuk mengubah JSON menjadi tabel dalam database SQL mungkin dapat diterapkan untuk data JSON dasar dengan bidang tetap yang diketahui sebelumnya. Namun, JSON dengan objek bersarang atau bidang baru yang "dapat muncul setiap 2–4 minggu", seperti yang dikatakan oleh poster Stack Overflow asli, tidak mungkin ditangani dengan cara yang kaku.

Database relasional menawarkan pendekatan alternatif untuk mengakomodasi data JSON yang lebih kompleks. SQL Server menyimpan JSON dalam kolom varchar, sedangkan Postgres dan MySQL memiliki tipe data JSON. Dalam skenario ini, pengguna dapat menyerap data JSON tanpa konversi ke kolom SQL, tetapi mendapatkan kinerja yang tinggi saat membuat kueri data karena kolom ini mendukung pengindeksan minimal.

SQL pada JSON Bersarang Menggunakan Rockset

Dengan banyak bidang yang berubah, ditambahkan/dihapus, dll, mungkin agak merepotkan untuk memelihara jalur pipa ETL. Rockset dirancang untuk membantu mengatasi masalah ini — dengan mengindeks semua kolom dalam dokumen JSON, bersama dengan semua jenis informasi, dan memaparkan SQL API di atasnya

Misalnya, dengan koleksi Rockset bernama new_collection, saya bisa mulai dengan menambahkan satu dokumen ke koleksi kosong yang terlihat seperti

{
    "my-field": "doc1",
    "my-other-field": "some text"
}

.. dan kemudian menanyakannya

Sekarang, jika dokumen JSON baru hadir dengan beberapa bidang baru — mungkin dengan beberapa larik, objek JSON bersarang, dll, saya masih dapat menanyakannya dengan SQL

{
    "my-field": "doc2",
    "my-other-field":[
        {
            "c1": "this",
            "c2": "field",
            "c3": "has",
            "c4": "changed"
        }
    ]
}

Saya menambahkannya ke koleksi yang sama dan dapat menanyakannya seperti sebelumnya

Saya selanjutnya dapat meratakan objek JSON bersarang dan kolom array pada waktu kueri dan membuat tabel yang ingin saya tuju — tanpa harus melakukan transformasi apa pun sebelumnya

Selain itu, ada informasi tipe yang kuat yang disimpan, yang berarti saya tidak akan tersandung dengan tipe campuran, dll. Menambahkan dokumen ketiga

{
    "my-field": "doc3",
    "my-other-field":[
        {
            "c1": "unexpected",
            "c2": 99,
            "c3": 100,
            "c4": 101
        }
    ]
}

Itu masih menambahkan dokumen saya seperti yang diharapkan

… dan bidang diketik dengan kuat

Jika bisa menjalankan SQL pada JSON yang kompleks, tanpa ETL, pipa data, atau skema tetap, terdengar menarik bagi Anda, Anda harus mencoba Rockset

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

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 kumpulan 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 kolom 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 diekstraksi, 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, itu lebih disukai dalam banyak kasus

Namun, jika Anda ingin mengekstrak data dari array JSON bersarang atau objek JSON, Anda tidak dapat menggunakan -> berantai 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 -> berantai 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 menyimpan JSON bersarang di database MySQL?

Ikuti langkah-langkah ini. .

Anda harus membuat tabel yang berisi kolom JSON, MySQL JSON Docs CREATE TABLE myjson (jdoc JSON);

Sambungkan ke mysql, saya menggunakan driver mysql, instal. npm instal mysql

Perbaiki kode Anda. Misalnya, pada baris 3, larik objek harus menggunakan [{ }] dan bukan []

Bagaimana cara menyimpan JSON bersarang di basis data?

Ikuti langkah-langkah di bawah ini untuk memahami bagaimana objek JSON bersarang dapat diimpor ke database Redis. .

Jalankan wadah RedisJSON Docker.

Verifikasi apakah modul RedisJSON dimuat.

JSON bersarang.

Muat Modul Redis.

Jalankan skrip python.

Pastikan objek JSON ditambahkan ke Redis.

Mengambil bidang tertentu

Bagaimana cara memasukkan data JSON di MySQL?

Cara Memasukkan Beberapa Data JSON ke dalam Database MySQL di Php .

pertama, buat file Multi JSON dan tulis file dengan kode PHP

Setelah itu, akses file variabel Php

Buat koneksi database menggunakan sintaks database MySQL

Kemudian tambahkan kueri Sisipkan SQL untuk menyimpan data yang menyimpan variabel

Bagaimana cara memasukkan data JSON ke MySQL menggunakan Python?

Memasukkan JSON ke dalam MySQL menggunakan Python - Stack Overflow . use json. dumps (json_value) untuk mengonversi objek json Anda (objek python) menjadi string json yang dapat Anda sisipkan di bidang teks di mysql http. //dokumen. python. org/perpustakaan/json. html Bagikan Ikuti dijawab Nov 30, 2010 at 17. 56 Mordi 691 4 5 1 str (json.