Большая часть JSON, с которой я имею дело, начинает жизнь как ответ API и должна попасть в базу данных.На бэкэнде Toolz.dev, который работает на Express и TypeORM через PostgreSQL, я потерял счет времени, когда у меня был экспорт JSON из одной службы, которую я хотел загрузить в таблицу для быстрого запроса, миграционного начального числа или локального воспроизведения ошибки. делать это вручную мрачно: вы пишете CREATE TABLEтогда вы ручной тип INSERT утверждения, цитирование каждой строки, удвоение каждого апострофа, запоминание того, какие поля являются числами, и превращение недостающих ключей в NULL. Получите одну строку неправильно из двухсот, и вся партия не запустится.
Преобразователь JSON в SQL выполняет эту механическую работу для вас. Вы вставляете массив объектов, выбираете диалект базы данных, и он создает CREATE TABLE с разумными типами столбцов плюс INSERT операторы для загрузки каждой строки. в этом руководстве объясняется, как работает преобразование, решения, которые оно принимает о типах и цитировании, а также реальные ситуации, когда оно экономит день.
TL;DR: выше Конвертер JSON в SQL превращает массив объектов JSON в
CREATE TABLEаINSERTоператоры для MySQL, PostgreSQL, SQLite или стандартного SQL. Он читает ключи объектов в виде столбцов, выводит каждый тип столбца из данных, ускользает от строк, превращает недостающие ключи и нули вNULLи хранит вложенные объекты как JSON. Все работает в вашем браузере, поэтому ответ API с реальными данными никогда не покидает вашу машину. обратное или табличные источники см CSV в SQL а json в CSV, йо-
Что делает преобразователь JSON в SQL?
Данные моделей JSON и SQL принципиально по-разному.JSON - это дерево вложенных объектов и массивов с динамическими типами, для каждого значения. реляционная таблица - это плоская сетка фиксированных, типизированных столбцов. Преобразование одного в другое - это серия небольших решений, и ценность инструмента - это последовательное принятие этих решений.
Он считывает ключи ваших объектов как набор столбцов, обрабатывает каждый объект как строку, выбирает тип SQL для каждого столбца, а затем записывает операторы. Выход имеет две части: необязательную CREATE TABLE это объявляет столбцы с их предполагаемыми типами и INSERT операторы, несущие значения. можно вставить результат в клиент базы данных, сохранить его как часть миграции или передать в mysql, psql, или sqlite3 из командной строки.
Вход обычно представляет собой массив объектов, например [{ "id": 1, "name": "Ada" }, { "id": 2, "name": "Grace" }]. Один объект тоже работает и становится одной строкой. Чего он не примет, так это массива голых значений, таких как [1, 2, 3], потому что нет ключей, которые можно было бы превратить в столбцы, и это говорит вам об этом, а не о том, чтобы гадать.
Как выводятся типы столбцов?
Это часть, которая отделяет полезный преобразователь от той, которая сбрасывает все в текстовые столбцы. для каждого столбца инструмент сканирует значение в каждой строке и выбирает самый узкий тип, который подходит всем из них.
Если каждое приведенное значение в столбце является целым числом, столбец становится целым типом Если значения являются числами, но некоторые имеют десятичные дроби, он становится типом с плавающей запятой Если каждое значение является true или false, он становится логическим Если каждое значение является вложенным объектом или массивом, оно становится типом JSON. Все остальное, включая столбец, где значения не совпадают, возвращается к тексту, поэтому INSERT не может потерпеть неудачу при несоответствии типов.
null а недостающие ключи пропускаются при выборе типа; они делают столбец на практике недействительным. Это означает столбец, в котором хранятся числа в большинстве строк и null в некоторых все еще вводится число, чего вы и хотите.
Конкретное название типа зависит от диалекта Вот как отображаются нейтральные виды:
| Предполагаемый вид | MySQL | PostgreSQL | SQLite | Стандартный SQL |
|---|---|---|---|---|
| Целочисленный | INT |
INTEGER |
INTEGER |
INTEGER |
| десятичный | DOUBLE |
DOUBLE PRECISION |
REAL |
REAL |
| логический | TINYINT(1) |
BOOLEAN |
INTEGER |
BOOLEAN |
| Вложенный JSON | JSON |
JSONB |
TEXT |
TEXT |
| Текст | VARCHAR(255) |
TEXT |
TEXT |
TEXT |
Вы можете полностью отключить вывод типа, и в этом случае каждый столбец становится текстом. Иногда это то, что вы хотите, когда загружаете данные в промежуточную таблицу и планируете отобразить их позже.
Как он обрабатывает неудобные части реальных данных?
Настоящий JSON редко бывает однородным, и преобразованию приходится справляться. Инструмент обрабатывает три случая, которые приводят в действие наивные преобразователи.
Во-первых, объекты с разными ключами. Если один объект имеет email поле, а другое нет, набор столбцов - это объединение каждого появившегося ключа, сохраненного в порядке, который был впервые виден, и любой объект, у которого отсутствует ключ, получает NULL в том столбце. инструмент предупреждает вас, когда объекты не однородны, чтобы вы не удивились.
Во-вторых, вложенные объекты и массивы. столбцы SQL плоские, поэтому значение, как { "street": "1 Main St", "city": "London" } не может стать несколькими столбцами автоматически, если вы не определите форму. вместо этого конвертер сериализует вложенное значение обратно в текст JSON и сохраняет его в столбце JSON на MySQL, столбце JSONB на PostgreSQL или текстовом столбце в другом месте. Структура остается нетронутой, и вы можете запросить ее позже с помощью своей базы данных и функций JSON #39;s.
В-третьих, смешанные типы в одном столбце. если поле - это число в одной записи и строка в другой, принудительное использование числового типа приведет к сбою половины вставок. инструмент обнаруживает несогласие и вводит столбец в текст, затем цитирует каждое значение в виде строкового литерала, чтобы пакет работал чисто.
Как ускользают ценности?
Правильное экранирование - это разница между запущенным сценарием и сценарием, который выдает синтаксическую ошибку в строке 47. Правила, которые применяет конвертер, являются стандартными SQL.
Значения текста обернуты в отдельные кавычки, и любая отдельная кавычка внутри значения удваивается, так что O'Brien становиться 'O''Brien'.Это ANSI SQL строка escaping и она работает по всем четырем диалектам. числа и логические значения пишутся без кавычек, потому что цитируемое число является строкой в большинстве движков. логические значения отображаются как TRUE а FALSE на PostgreSQL и стандартном SQL, а также как 1 а 0 на MySQL и SQLite, в соответствии с тем, как каждый движок их хранит. null а недостающие ключи становятся нецитируемым ключевым словом NULL, йо-
Идентификаторы, означающие названия таблиц и столбцов, также цитируются, с обратными ссылками на MySQL и двойными кавычками везде. Это сохраняет имя столбца order или select от столкновения с зарезервированным словом.
Как использовать конвертер JSON в SQL
Шаг 1: Вставьте JSON
Опустите массив объектов или нажмите "Образец нагрузки", чтобы увидеть ожидаемую форму. Каждый объект становится одной строкой.
Шаг 2: Назовите таблицу и выберите диалект
Введите имя целевой таблицы и выберите MySQL, PostgreSQL, SQLite или стандартный SQL. Диалект управляет стилем цитирования, именами типов и тем, как пишутся логические значения и вложенный JSON.
Шаг 3: Установите параметры вывода
Решите, включать ли CREATE TABLE, следует ли делать вывод о типах столбцов и следует ли выделять один многорядный столбец INSERT или один оператор в строке. одна многорядная вставка загружается быстрее; один оператор в строке создает файл, удобный для различий, и позволяет запускать строки индивидуально.
Шаг 4: Скопируйте SQL
Прочтите сгенерированные операторы и скопируйте их. вставьте сценарий в клиент базы данных или запустите его из командной строки, чтобы создать и загрузить таблицу.
Общие варианты использования
Выделение базы данных из ответа API
Вы создаете функцию, и вам нужны реалистичные строки для работы с. Вытащите ответ JSON из восходящего API, вставьте его, и у вас есть a CREATE TABLE и вставки готовы к посеву вашей локальной базы данных. Нет ручного ввода, нет пропущенных котировок.
Превращение конфигурации или экспорта в таблицу
Служба экспортирует свои данные в виде массива JSON. вы хотите запускать специальные SQL-запросы против нее, а не писать JavaScript для фильтрации массива. Преобразуйте его в SQL, загрузите в SQLite и запросите его с помощью языка, созданного именно для этого.
Создание миграционного семени
Для миграции фреймворков часто требуется начальный этап, на котором вставляются справочные данные. Создание INSERT операторы из известного хорошего устройства JSON дают вам стабильный, проверяемый блок для вставки в миграцию, а не для перевода данных вручную.
Воспроизведение производственной ошибки локально
Ошибка появляется только для определенного набора записей. Возьмите JSON для этих записей, преобразуйте его во вставки, загрузите их в локальную базу данных и воспроизведите проблему без копирования производственной базы данных.
ВСТАВКА "Многорядная" и "порядная": что выбрать?
Многорядная вставка объединяет каждую строку в один оператор со многими VALUES кортежи. он компактен и загружается быстрее всего, потому что база данных анализирует и планирует один оператор вместо сотен. Выберите его для массовой загрузки.
Один оператор в строке более многословный, но имеет два преимущества. Он создает файл, в котором каждая строка представляет собой свою собственную строку, которая читается чисто при проверке кода и хорошо различается при контроле версий. И если одна строка нарушает ограничение, другие все равно преуспевают, что удобно, когда вы загружаете беспорядочные данные и хотите увидеть, какие строки не работают, а не теряют всю партию. Выберите его для семян, которые вы зафиксируете, или для данных, которым вы еще не полностью доверяете.
Запуск сгенерированного SQL из командной строки
Как только у вас появится сценарий, загрузка его является однострочной в каждом движке. для SQLite сохраните вывод в файл и передайте его в: sqlite3 mydata.db < insert.sql создает таблицу и загружает строки в свежий файл базы данных. Для PostgreSQL, psql -d mydb -f insert.sql запускает весь сценарий по именованной базе данных. Для MySQL, mysql -u user -p mydb < insert.sql делает то же самое. поскольку генератор цитирует идентификаторы и ускользает от значений выбранного вами диалекта, сценарий запускается без исправлений вручную, которые обычно необходимы рукописному SQL.
Если вы сгенерировали один оператор в строке, а не одну многорядную вставку, стоит обернуть загрузку в транзакцию. Добавлять BEGIN; наверху и COMMIT; внизу превращает сотни отдельных вставок в одну атомную операцию, что одновременно быстрее и безопаснее: если более поздняя строка не выполняет ограничение, вся нагрузка откатывается назад, вместо того, чтобы оставить таблицу наполовину заполненной. Для многорядной вставки это уже фактически атомный, поскольку это единое утверждение.
Производительность и ограничения с большими массивами
Само преобразование линейно по размеру ввода и полностью выполняется в вашем браузере, поэтому несколько тысяч строк преобразуются мгновенно Практические ограничения не в инструменте, а в том, что вы делаете с выходом. один многорядный INSERT десятки тысяч кортежей могут превышать максимальную величину оператора базы данных и #39;s или ограничение пакета MySQL's max_allowed_packet будучи одним человеком, который ударил первым Если вы загружаете очень большой массив, либо переключитесь на один оператор в строке, чтобы каждый был маленьким, либо разделите входные данные на партии из нескольких сотен строк и преобразуйте их отдельно.
Есть также предостережение точности, которое стоит знать. номера JSON анализируются браузером и #39;s JSON движок в стандартные двойные точности плавает, так что целое число больше, чем около девяти квадриллионов, за пределами JavaScript's безопасный целочисленный диапазон, может потерять точность, прежде чем инструмент когда-либо увидит его. Это свойство JSON синтаксический анализ в каждой среде, а не конвертера, но это означает, что гигантский числовой идентификатор безопаснее представлен как строка в исходном JSON, который инструмент затем будет хранить как текст и сохранить точно.
Где это сочетается с другими инструментами
JSON-SQL - это одно из направлений небольшой семьи. Когда вашим источником является электронная таблица или экспорт CSV, а не JSON, CSV в SQL выполняет ту же работу из табличного ввода с выводом того же типа и экранирования Когда вам нужны данные в виде плоского файла вместо базы данных, json в CSV выравнивает массив по строкам, и CSV в JSON идет обратно в другую сторону. прежде чем что-либо конвертировать, Форматтер JSON будет красиво печатать и проверять JSON, чтобы вы могли поймать случайную запятую, прежде чем она станет ошибкой анализа. И как только у вас появится SQL, Форматтер SQL будет отступать и выравнивать операторы для файла миграции, который вы с гордостью фиксируете.
По причине того, что все это делается в браузере, а не загружается данные в службу конвертера, Конфиденциальность данных в онлайн-инстру в руководстве изложена модель на стороне клиента и Набор инструментов для веб-разработчиков собирает утилиты, которые хорошо сочетаются с работой по преобразованию данных.
часто задаваемые вопросы
Как конвертировать JSON в SQL?
Вставить массив объектов JSON, установить имя таблицы и выбрать диалект SQL. инструмент считывает ключи объектов как имена столбцов, делает вывод о каждом типе столбцов и генерирует операторы CREATE TABLE плюс INSERT, которые вы можете скопировать и запустить в своей базе данных.
Какую форму JSON он принимает?
Массив объектов - это нормальный вход, где каждый объект - это одна строка. также работает и производит одну строку. массивы примитивов или глубоко несовпадающих форм отклоняются с ошибкой, объясняющей ожидаемое.
Какие базы данных SQL поддерживаются?
Преобразователь выводит операторы для MySQL, PostgreSQL, SQLite и стандартного SQL. Выбранный вами диалект управляет цитированием идентификатора, типом и логическим синтаксисом, а также собственным типом JSON, поэтому сценарий запускается в этой базе данных без изменений.
Как определяются типы столбцов?
Каждый столбец сверяется с каждым объектом. если все приведенные значения являются целыми числами, он становится целочисленным типом, все числа становятся десятичным типом, все истинные или ложные значения становятся логическим типом, все объекты или массивы становятся типом JSON, а все смешанное становится текстом.
Что происходит с вложенными объектами и массивами?
Они сериализуются обратно в текст JSON и сохраняются в столбце JSON на MySQL, столбце JSONB на PostgreSQL или столбце TEXT на SQLite и стандартном SQL. Это сохраняет структуру нетронутой, поэтому вы можете запросить ее с помощью функций JSON своей базы данных позже.
Что, если у моих объектов разные ключи?
Это нормально. столбцы являются объединением каждого ключа, который появляется, сохраняются в порядке, который был впервые виден Любой объект, отсутствующий в одной из этих клавиш, получает NULL в этом столбце, и инструмент предупреждает вас, когда объекты не однородны.
Как ускользают строки и кавычки?
Значения текста обернуты в одиночные кавычки и любая одиночная кавычка внутри значения удваивается, что является стандартным экранированием SQL. числа и логические значения остаются без кавычек, нуль становится НУЛЕВЫМ, а идентификаторы цитируются с обратными кавычками для MySQL или двойными кавычками для других диалектов.
мой json где-нибудь загружен?
Нет. парсинг и генерация SQL работают как JavaScript в вашем браузере. Ничего не передается, не регистрируется и не сохраняется, и инструмент продолжает работать в автономном режиме после загрузки, что вы можете подтвердить, просматривая вкладку сети во время конвертации.
