JSON ke SQL: Pernyataan INSERT, Desain Tabel, dan Jebakan Pemetaan Tipe

Intinya: tentukan entitas bisnis apa yang diwakili satu baris sebelum mendesain tabel, lalu tulis multi-baris VALUES eksplisit di dalam satu transaksi; selesaikan penamaan field dan pemetaan tipe sebelum impor alih-alih menambalnya di SQL.

JSON mendeskripsikan bentuk; SQL juga mendeskripsikan batasan. Saat Anda memindahkan yang pertama ke yang kedua, setiap batasan yang tidak Anda tuliskan akan kembali kemudian sebagai data kotor.

1. Tentukan dulu: satu tabel atau satu record per baris JSON

Lihat lapisan terluar data sebelum mendesain tabel apa pun. Salah dalam keputusan ini dan setiap INSERT setelahnya harus ditulis ulang.

Hanya ada satu kriteria: entitas bisnis apa yang diwakili satu baris? Jika satu baris adalah order, item baris harus berada di tabelnya sendiri. Jika satu baris adalah user, tag berada di tabel anak atau kolom JSON, bukan di string yang digabung koma.

2. Dari array ke INSERT: templat terkecil yang dapat dijalankan

Bentuk teraman selalu mendaftar nama kolom secara eksplisit dan tidak pernah bergantung pada urutan kolom:

复制CREATE TABLE users (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE
);

INSERT INTO users (id, name, email) VALUES
  (1, 'Ada', 'ada@example.com'),
  (2, 'Linus', NULL);

Tiga detail penting:

• Tulis setiap nama kolom. Saat schema nanti bertambah kolom, tidak ada yang bergeser ke field yang salah.
• VALUES multi-baris bekerja di SQLite, PostgreSQL, dan MySQL, dan menghemat banyak round trip dibandingkan loop insert satu baris.
• NULL adalah kata kunci; jangan mengutipnya. Menulis 'NULL' menyimpan string empat karakter.

Jika kunci JSON tidak cocok dengan nama kolom (userId versus user_id), tambahkan lapisan pemetaan. Jangan menebak dengan COALESCE di dalam SQL. Memperbaiki nama field di lapisan data jauh lebih murah daripada menambalnya di kueri.

3. Object bersarang: tabel induk plus tabel anak

Order adalah kasus satu-ke-banyak klasik:

复制INSERT INTO orders (id, customer_name, total_cents) VALUES
  (1001, 'Ada', 12900);

INSERT INTO order_items (order_id, sku, quantity) VALUES
  (1001, 'A-1', 2),
  (1001, 'B-7', 1);

Ini sesuai dengan JSON {"id":1001,"customer":"Ada","total":129.00,"items":[{"sku":"A-1","quantity":2},{"sku":"B-7","quantity":1}]}.

Poin kunci:

• Panjang array items menentukan jumlah baris anak; tabel induk mendapat tepat satu baris.
• Simpan uang sebagai integer sen (total_cents), jangan pernah sebagai FLOAT.
• Berikan tabel anak foreign key order_id dan commit kedua insert dalam satu transaksi, jika tidak induk bisa ada sementara itemnya hilang.
• Jika data bersifat read-only dan tidak ada yang memfilter per item baris, kolom JSON dapat diterima (PostgreSQL jsonb, MySQL JSON, atau SQLite TEXT dengan fungsi json1), tetapi terima biayanya: tidak ada indeks dan kueri yang canggung.

Ratalkan object bersarang tetap seperti user.address menjadi kolom (address_city, address_zip). Pecah menjadi tabel hanya ketika penyarangan bisa terus bertambah; memecah bentuk tetap hanya menambah JOIN.

4. NULL versus field hilang: SQL tidak punya "kunci tidak ada"

JSON memungkinkan kunci benar-benar tidak ada. Kolom SQL bersifat tetap. Setelah impor kedua kasus tampak identik — NULL — meskipun bisa bermakna berbeda:

Dua kesalahan umum:

1. Menggunakan string kosong alih-alih NULL. Nanti, WHERE email IS NULL melewatkan baris-baris itu, dan metrik "pengguna tanpa email" Anda diam-diam menghitung kurang.
2. Menguji dengan col = NULL. Dalam logika tiga-nilai SQL, NULL = NULL dievaluasi menjadi NULL, bukan TRUE. Anda harus menulis IS NULL:

复制SELECT COUNT(*) FROM users WHERE email IS NULL;

Jika Anda perlu mempertahankan apakah kunci pernah ada, tambahkan kolom penanda boolean alih-alih menyimpulkannya dari NULL. Kontrak API juga harus menyatakan ini; lihat Desain API JSON dan penamaan field.

5. Quoting dan escaping: jangan pernah menyambung string SQL

String JSON dapat berisi kutip tunggal, kutip ganda, backslash, baris baru, dan bahkan \u0000. Dengan string yang dirakit manual, satu escape yang terlewat menggeser sisa pernyataan, dan dalam kasus terburuk menjadi SQL injection.

Dalam SQL standar, kutip tunggal di-escape dengan menggandakannya:

复制INSERT INTO notes (id, content) VALUES (1, 'It''s a ''quoted'' value');

Kode produksi harus menggunakan parameter binding dan membiarkan driver menangani escaping dan tipe:

复制// PostgreSQL uses $1/$2, MySQL uses ?; this example uses node-postgres
await client.query('INSERT INTO users (id, name) VALUES ($1, $2)', [row.id, row.name]);

Lakukan escape manual hanya saat Anda harus mengekspor file .sql, dan setidaknya:

• ganti ' dengan '';
• jika baris baru dipertahankan, pastikan basis data target menerima literal string multi-baris;
• hapus \u0000, karena sebagian besar basis data menolak NUL di dalam string;
• MySQL memperlakukan backslash sebagai karakter escape secara default, sehingga \ harus menjadi \\, atau beralih ke mode standar.

Kutip ganda dari JSON tidak perlu di-escape di dalam string SQL berkutip tunggal. Menambahkan lapisan escaping ekstra adalah cara backslash liar berakhir di data Anda.

6. Pemetaan tipe: di mana enam nilai JSON mendarat di SQL

JSON hanya punya string, number, boolean, null, object, dan array. SQL memiliki sistem tipe yang jauh lebih rinci. Kesalahan biasanya terjadi pada presisi dan semantik, bukan sintaks:

Boolean menyembunyikan bug paling halus: false di JSON menjadi 0 di MySQL TINYINT(1), round-trip tampak baik, dan tidak ada yang menyadari. Tetapi begitu beberapa kode melakukan if (value) pada string "false", ia dievaluasi sebagai truthy. Skrip konversi harus memanggil Boolean(value) secara eksplisit.

PostgreSQL JSONB mem-parsing dan menghapus duplikat kunci, menyimpan kemunculan terakhir, yang cocok untuk kueri; JSON mempertahankan teks asli dan urutan kunci. SQLite JSON hanyalah TEXT plus sekumpulan fungsi, tanpa batasan tipe nyata. Sebelum memilih, tanyakan apakah tabel ini perlu memfilter field di dalam JSON.

7. Insert massal dan transaksi: batch, commit, buat dapat dijalankan ulang

Saat volume bertambah, tiga hal menentukan apakah impor tetap terkendali:

1. Batch. Loop N pernyataan INSERT satu baris membutuhkan N round trip jaringan. Gabungkan 500 hingga 1000 baris menjadi satu VALUES multi-baris, atau gunakan bulk loader milik basis data (PostgreSQL COPY, MySQL LOAD DATA, insert multi-baris di dalam transaksi SQLite).
2. Gunakan transaksi. Semua atau tidak sama sekali, sehingga tabel yang setengah termuat tidak pernah bertahan setelah error:

复制BEGIN;
INSERT INTO users (id, name) VALUES (1, 'Ada'), (2, 'Linus');
INSERT INTO order_items (order_id, sku, quantity) VALUES (1, 'A-1', 2);
COMMIT;

3. Buat idempoten. Skrip impor terus-menerus dijalankan ulang. Gunakan upsert alih-alih delete-then-insert:

ON CONFLICT membutuhkan unique constraint atau primary key, jika tidak ia tidak pernah terpicu. Ketika tidak ada kunci alami, buat indeks unik pada kunci bisnis seperti email. Ketika tidak ada kunci sama sekali, tambahkan id stabil di sisi JSON — jangan pernah bergantung pada id auto-increment untuk idempotensi, karena menjalankan ulang akan menyisipkan duplikat.

8. Perbedaan dialek sekilas

Agar satu file SQL berfungsi di ketiganya, setidaknya hindari ON CONFLICT, backtick, dan SERIAL. Untuk proyek lintas basis data, hasilkan schema dan pernyataan upsert secara terpisah; jangan berharap satu skrip cocok untuk semua.

9. Tiga pemeriksaan sekilas setelah impor

Sebelum menghapus file sumber, verifikasi tiga hal:

1. Jumlah baris. SELECT COUNT(*) FROM users; harus sama dengan panjang array JSON. Satu baris yang hilang biasanya berarti record terakhir terpotong.
2. Kolom dengan tingkat NULL tertinggi. Itu menunjuk ke nama kunci yang tidak konsisten atau penyarangan yang tidak diperluas. Kembali dan periksa apakah sumber memakai userId dan user_id sekaligus.
3. Uang dan pengenal panjang. Ambil nilai maksimum dan minimum dan pastikan konversi floating point maupun integer tidak memotongnya.

Jika Anda perlu memahami bentuk tabularnya lebih dulu, gunakan pendekatan perataan di Konversi JSON, CSV, dan data tabular. Jika audiensinya adalah rekan kerja yang hanya ingin membaca data, empat cara mengonversi JSON ke Excel lebih sedikit kerja daripada mendirikan basis data.

Mengapa angka kehilangan presisi setelah mengimpor JSON ke SQL?

Integer di atas 2^53 sudah kehilangan presisi selama JSON.parse JavaScript, dan menuliskannya ke BIGINT tidak dapat memulihkannya. id seperti itu harus berupa string di JSON dan dikonversi ke BIGINT atau TEXT saat impor.

Apa yang harus saya gunakan sebagai primary key ketika JSON tidak punya id?

Biarkan basis data menghasilkannya, atau buat UUID stabil sebelum impor. Jangan pernah menggunakan indeks array sebagai id: menjalankan ulang akan mengurutkan ulang data dan mengarahkan baris yang salah ke record yang salah. Indeks unik pada kunci bisnis (email, nomor order) juga memungkinkan upsert.

Apa yang harus saya lakukan saat INSERT massal mengalami konflik kunci unik?

Ganti INSERT biasa dengan upsert yang sesuai untuk dialek Anda, dan pastikan kolom yang berkonflik memiliki unique constraint. Menghapus dulu lalu menyisipkan juga bisa, tetapi harus terjadi dalam satu transaksi dan Anda menerima jendela singkat tanpa baris.

Apakah array bersarang selalu membutuhkan tabel anak?

Tidak. Untuk tampilan read-only tanpa kueri per sub-item, kolom JSON lebih sederhana. Begitu Anda perlu memfilter atau mengagregasi per tag atau SKU, tabel anak dengan indeks adalah pilihan yang berkelanjutan. Faktor penentunya adalah bagaimana Anda mengueri, bukan bagaimana data dibentuk.