JSON у SQL: інструкції INSERT, проєктування таблиць і пастки відображення типів

Коротко: спершу визначте, яку бізнес-сутність представляє один рядок, а тоді проєктуйте таблиці й пишіть явні багаторядкові VALUES в одній транзакції; вирішуйте найменування полів і відображення типів до імпорту, а не латайте їх у SQL.

JSON описує форму; SQL описує ще й обмеження. Коли ви переносите перше в друге, кожне обмеження, яке ви не записали, пізніше повертається як брудні дані.

1. Спершу вирішіть: одна таблиця чи один запис на рядок JSON

Подивіться на найзовнішній шар даних, перш ніж проєктувати будь-яку таблицю. Помиліться з цим рішенням — і кожен наступний INSERT доведеться переписувати.

Критерій лише один: яку бізнес-сутність представляє один рядок? Якщо рядок — це замовлення, позиції мають жити у власній таблиці. Якщо рядок — це користувач, теги належать дочірній таблиці або стовпцю JSON, а не рядку, з'єднаному комами.

2. Від масиву до INSERT: найменший виконуваний шаблон

Найбезпечніша форма завжди явно перелічує назви стовпців і ніколи не покладається на порядок стовпців:

复制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);

Важливі три деталі:

• Пишіть кожну назву стовпця. Коли схема згодом отримає стовпець, ніщо не зсунеться в неправильне поле.
• Багаторядковий VALUES працює в SQLite, PostgreSQL і MySQL і економить велику кількість зворотних викликів порівняно з циклом однорядкових вставок.
• NULL — ключове слово; не беріть його в лапки. Написання 'NULL' збереже рядок із чотирьох символів.

Якщо ключі JSON не збігаються з назвами стовпців (userId проти user_id), додайте шар відображення. Не вгадуйте через COALESCE усередині SQL. Виправлення назв полів у шарі даних набагато дешевше, ніж латання в запитах.

3. Вкладені об'єкти: батьківська таблиця плюс дочірня

Замовлення — класичний випадок «один-до-багатьох»:

复制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);

Це відповідає JSON {"id":1001,"customer":"Ada","total":129.00,"items":[{"sku":"A-1","quantity":2},{"sku":"B-7","quantity":1}]}.

Ключові моменти:

• Довжина масиву items визначає кількість дочірніх рядків; батьківська таблиця отримує рівно один рядок.
• Зберігайте гроші як цілі копійки (total_cents), ніколи як FLOAT.
• Дайте дочірній таблиці зовнішній ключ order_id і фіксуйте обидві вставки в одній транзакції, інакше батько може існувати без своїх позицій.
• Якщо дані лише для читання і ніхто не фільтрує за позицією, стовпець JSON прийнятний (PostgreSQL jsonb, MySQL JSON або SQLite TEXT із функціями json1), але прийміть ціну: без індексів і незручні запити.

Розгортайте фіксовані вкладені об'єкти, як-от user.address, у стовпці (address_city, address_zip). Розділяйте на таблицю лише тоді, коли вкладеність може зростати далі; поділ фіксованої форми лише додає JOIN-и.

4. NULL проти відсутніх полів: SQL не має «відсутнього ключа»

JSON дозволяє ключу взагалі бути відсутнім. Стовпці SQL фіксовані. Після імпорту обидва випадки виглядають ідентично — NULL — хоча можуть означати різне:

Дві поширені помилки:

1. Використання порожнього рядка замість NULL. Згодом WHERE email IS NULL пропустить ці рядки, і ваша метрика «користувачі без email» непомітно недорахує.
2. Перевірка через col = NULL. У тризначній логіці SQL NULL = NULL обчислюється як NULL, а не TRUE. Треба писати IS NULL:

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

Якщо треба зберегти, чи ключ узагалі був присутній, додайте булевий службовий стовпець, а не виводьте це з NULL. Контракт API теж має це описувати; дивіться Проєктування JSON API і найменування полів.

5. Лапкування й екранування: ніколи не склеюйте рядки SQL

Рядки JSON можуть містити одинарні лапки, подвійні лапки, зворотні скісні риски, переведення рядків і навіть \u0000. У ручному складанні рядків один пропущений escape зсуває решту інструкції, а в найгіршому випадку стає SQL-ін'єкцією.

У стандартному SQL одинарна лапка екранується подвоєнням:

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

Виробничий код має використовувати зв'язування параметрів і дозволяти драйверу обробляти екранування й типи:

复制// PostgreSQL використовує $1/$2, MySQL — ?; цей приклад використовує node-postgres
await client.query('INSERT INTO users (id, name) VALUES ($1, $2)', [row.id, row.name]);

Вручну екрануйте лише тоді, коли мусите експортувати файл .sql, і тоді як мінімум:

• замініть ' на '';
• якщо переведення рядків зберігаються, переконайтеся, що цільова база даних приймає багаторядкові рядкові літерали;
• приберіть \u0000, бо більшість баз даних відхиляють NUL усередині рядків;
• MySQL за замовчуванням трактує зворотну скісну риску як escape-символ, тож \ має стати \\, або перемкніться в стандартний режим.

Подвійні лапки з JSON не потребують екранування всередині рядка SQL в одинарних лапках. Додатковий шар екранування — ось як у ваших даних з'являються зайві зворотні скісні риски.

6. Відображення типів: де шість значень JSON потрапляють у SQL

У JSON лише рядок, число, булеве значення, null, об'єкт і масив. У SQL значно тонша система типів. Помилки зазвичай трапляються в точності й семантиці, а не в синтаксисі:

Булеві значення ховають найтонший баг: JSON false стає 0 у MySQL TINYINT(1), зворотні перетворення виглядають добре, і ніхто не помічає. Але щойно якийсь код робить if (value) на рядку "false", він обчислюється як істинне. Скрипти конвертації мають явно викликати Boolean(value).

PostgreSQL JSONB розбирає й дедуплікує ключі, зберігаючи останнє входження, що підходить для запитів; JSON зберігає оригінальний текст і порядок ключів. SQLite JSON — це просто TEXT плюс набір функцій, без справжнього обмеження типу. Перш ніж обирати, запитайте, чи потрібна цій таблиці фільтрація за полями всередині JSON.

7. Масові вставки й транзакції: пакетуйте, фіксуйте, робіть повторюваним

Зі зростанням обсягу три речі визначають, чи залишається імпорт контрольованим:

1. Пакетуйте. Цикл із N однорядкових INSERT коштує N мережевих зворотних викликів. Об'єднуйте 500–1000 рядків в один багаторядковий VALUES або використовуйте власний масовий завантажувач бази даних (PostgreSQL COPY, MySQL LOAD DATA, багаторядкові вставки в транзакції SQLite).
2. Використовуйте транзакцію. Усе або нічого, щоб напівзавантажена таблиця ніколи не пережила помилку:

复制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. Робіть його ідемпотентним. Скрипти імпорту перезапускають постійно. Використовуйте upsert замість видалення-потім-вставки:

ON CONFLICT потребує унікального обмеження або первинного ключа, інакше він ніколи не спрацьовує. Коли немає природного ключа, збудуйте унікальний індекс на бізнес-ключі, як-от email. Коли ключа немає взагалі, додайте стабільний id на стороні JSON — ніколи не покладайтеся на автоінкрементний id для ідемпотентності, бо перезапуск вставить дублікати.

8. Відмінності діалектів стисло

Щоб один SQL-файл працював у всіх трьох, принаймні уникайте ON CONFLICT, зворотних лапок і SERIAL. Для кросс-базових проєктів генеруйте схему й інструкції upsert окремо; не сподівайтеся, що один скрипт підійде всім.

9. Три вибіркові перевірки після імпорту

Перш ніж видаляти вихідний файл, перевірте три речі:

1. Кількість рядків. SELECT COUNT(*) FROM users; має дорівнювати довжині масиву JSON. Один відсутній рядок зазвичай означає, що останній запис обрізано.
2. Стовпець із найвищою часткою NULL. Це вказує на неузгоджені назви ключів або нерозгорнуту вкладеність. Поверніться й перевірте, чи джерело використовує і userId, і user_id.
3. Гроші й довгі ідентифікатори. Виберіть максимальне й мінімальне значення та підтвердьте, що ні плаваюча точка, ні перетворення цілого не обрізали їх.

Якщо спершу треба зрозуміти табличну форму, скористайтеся підходом розгортання в Конвертація JSON, CSV і табличних даних. Якщо аудиторія — колеги, які просто хочуть читати дані, чотири способи конвертувати JSON в Excel вимагають менше роботи, ніж піднімати базу даних.

Чому числа втрачають точність після імпорту JSON у SQL?

Цілі числа понад 2^53 вже втратили точність під час JSON.parse у JavaScript, і запис у BIGINT не може її відновити. Такі id мають бути рядками в JSON і перетворюватися на BIGINT або TEXT під час імпорту.

Що використовувати як первинний ключ, коли в JSON немає id?

Дозвольте базі даних згенерувати його або створіть стабільний UUID перед імпортом. Ніколи не використовуйте індекс масиву як id: перезапуск перевпорядковує дані й спрямовує неправильні рядки на неправильні записи. Унікальний індекс на бізнес-ключі (email, номер замовлення) також уможливлює upsert.

Що робити, коли масовий INSERT натрапляє на конфлікт унікального ключа?

Замініть простий INSERT відповідним upsert для вашого діалекту й підтвердьте, що конфліктний стовпець має унікальне обмеження. Спершу видалити, а потім вставити теж працює, але це має відбуватися в одній транзакції, і ви приймаєте коротке вікно без рядків.

Чи завжди вкладені масиви потребують дочірньої таблиці?

Ні. Для дисплею лише для читання без запитів за підпунктом стовпець JSON простіший. Щойно вам треба фільтрувати чи агрегувати за тегом або SKU, дочірня таблиця з індексами — стале рішення. Вирішальний фактор — те, як ви запитуєте, а не те, яка форма даних.