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 (jsonb в PostgreSQL, JSON в MySQL или TEXT в SQLite с функциями 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 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]);

Экранируйте вручную, только когда обязаны выгрузить файл .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 или используйте собственный массовый загрузчик базы (COPY в PostgreSQL, LOAD DATA в MySQL, многострочные вставки внутри транзакции 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. Когда ключа нет вообще, добавьте стабильный идентификатор на стороне 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 её не восстановит. Такие идентификаторы должны быть строками в JSON и преобразовываться в BIGINT или TEXT при импорте.

Что использовать как первичный ключ, если в JSON нет id?

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

Что делать, если массовый INSERT упирается в конфликт уникального ключа?

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

Вложенным массивам всегда нужна дочерняя таблица?

Нет. Для read-only отображения без запросов по подэлементам столбец JSON проще. Как только нужно фильтровать или агрегировать по тегу или SKU, дочерняя таблица с индексами становится устойчивым выбором. Решающий фактор — как вы запрашиваете данные, а не как они устроены.