JSON我处理的大部分开始生活作为API响应,需要最终在一个数据库。toolz。dev后端,它运行在Express和TypeORM上通过PostgreSQL,我已经失去了数次我有一个JSON导出从一个服务,我想加载到一个表中快速查询,迁移种子或本地复制bug。用手做是严峻的:你写一个 CREATE TABLE那么你就手型了 INSERT 语句,引用每个字符串,将每个撇号加倍,记住哪些字段是数字,并将缺失的键转换为 NULL.两百错一行,整批运行失败。
JSON到SQL转换器为你做那个机械工作,你粘贴一个对象数组,挑选一个数据库方言,它就产生了一个 CREATE TABLE 具有合理的列类型加上 INSERT statements 来加载每一行。本指南解释了转换的工作原理、它对类型和引用做出的决定,以及它节省一个下午的真实情况。
TL;博士: 的 JSON 到 SQL 转换器 将对象的 JSON 数组转换为
CREATE TABLE並INSERTmysql、postgresql、sqlite或标准sql的语句,它将对象键读作列,从数据中推断出每个列类型,转义字符串,将缺失的键和空变成NULL的,并且将嵌套对象存储为JSON。Everything都运行在你的浏览器中,因此一个真实数据的API响应永远不会离开你的机器。对于反向或表格来源,请参阅 CSV 到 SQL 並 json to csv。
JSON 到 SQL 转换器的作用是什么?
JSON和SQL模型数据的方式根本不同,JSON是一棵嵌套对象和数组的树,具有动态的,每值类型关系表是一个固定,类型列的平坦网格,从一个转换到另一个是一系列小决策,工具的价值是一致地做出这些决策。
将你的对象的键读作列集,把每个对象当成一行,每列选择一个SQL类型,然后写出语句,输出有两部分:一个可选 CREATE TABLE 这声明了列及其推断的类型,并且 INSERT carry the values 的语句。您可以将结果粘贴到数据库客户端中, 保存为迁移的一部分, 或者将其管道化 mysql、 psql、或者 sqlite3 从命令行。
输入通常是对象数组,例如 [{ "id": 1, "name": "Ada" }, { "id": 2, "name": "Grace" }].单个对象也起作用,变成一行,它不会接受的是裸值数组,如 [1, 2, 3]的,因为没有键可以变成列,它告诉你是这样而不是猜测。
列类型是如何推断的?
这是将有用的转换器与将所有内容转储到文本列中的转换器分开的部分。对于每一列,工具都会扫描每一行中的值并选择适合所有行的最窄类型。
1列的每1个现值都是整数,则列变为整数类型,如果值是数但有的是小数则变为浮点类型,如果每1个值都是 true 或 false,它变成布尔值,如果每个值都是嵌套对象或数组,它就变成JSON类型,其他任何东西,包括值不一致的列,都回落到文本上所以the INSERT 类型不匹配时不能失败。
null and 缺失的键在决定类型时跳过;它们只使列在实践中可为空。那表示在大多数行中保存数字的列和 null 在少数中仍然被输入为数字,这就是您想要的。
具体类型名称取决于方言,以下是中性种类如何映射:
| 推断类型 | 我的SQL | 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 |
式推理可以完全关闭,在这种情况下,每一列都变成文本,这偶尔是你在将数据加载到分期表中并计划稍后投放时想要的。
它如何处理真实数据的尴尬部分?
Real JSON很少是统一的,转换要应付,工具处理三个跳闸幼稚转换器的案例。
首先,具有不同键的对象。如果一个对象有一个 email 字段和另一个不这样做,列集是出现的每个键的并集,按首先看到的顺序保存,任何缺少键的对象都会得到 NULL that列。工具警告你,当对象不统一,所以你不感到惊讶。
其次,嵌套对象和数组。 SQL 列是平坦的,因此值类似于 { "street": "1 Main St", "city": "London" } bu能在不决定形状的情况下自动变成几列,而是转换器将嵌套值序列化回json文本,并存储在mysql上的json列、postgresql上的jsonb列或其他地方的文本列中,结构保持完整,以后可以用数据库查询's JSON函数。
第三,在一列中混合类型。如果一个字段是一条记录中的数字,另一条记录中的字符串,则强制使用数字类型将使一半的插入失败。工具检测到分歧并将该列类型为文本,然后将每个值引用为字符串文字,以便批处理运行干净。
价值观是如何逃脱的?
正确转义是运行的脚本与在第 47 行抛出语法错误的脚本之间的区别。转换器应用的规则是标准 SQL 规则。
文本值用单引号包裹,值内的任何单引号都加倍,因此 O'Brien 变成 'O''Brien'。这是ANSI SQL字符串转义,它适用于所有四种方言。数字和布尔值是在没有引号的情况下写的,因为在大多数引擎中,引号是一个字符串。布尔值渲染为 TRUE 並 FALSE 在 PostgreSQL 和标准 SQL 上,以及 1 並 0 mysql和sqlite上,匹配每个引擎存储它们的方式。 null 缺少的密钥成为未引用的关键字 NULL。
Identifiers,意思是表和列名,也被引用,在MySQL上带有反向标记,其他地方都有双引号。这使得列保持命名 order 或 select 从与保留词碰撞。
如何使用 JSON 到 SQL 转换器
第 1 步:粘贴您的 JSON
Drop in 一组对象,或者按 Load Sample 查看预期的形状,每个对象变成一行。
2步:命名表并选择方言
键入目标表名称并选择 MySQL、PostgreSQL、SQLite 或标准 SQL。方言控制引用样式、类型名称以及布尔值和嵌套 JSON 的编写方式。
3步:设置输出选项
决定是否包括 CREATE TABLE、是否推断列类型、是否发出一多行 INSERT or 每行一个语句。单个多行插入加载速度更快;每行一个语句产生一个差异友好文件,并允许您单独运行行。
4步:复制SQL
读生成的语句并复制它们。将脚本粘贴到数据库客户端或从命令行运行它来构建和加载表。
常见用例
API 响应中为数据库播种
您正在构建一个功能,需要逼真的行来对抗。从上游 API 中提取 JSON 响应,将其粘贴到其中,然后您就得到了一个 CREATE TABLE 并插入准备为您的本地数据库播种。没有手动打字,没有遗漏的报价。
将配置或导出到表中
A服务将其数据导出为JSON数组,你想针对它运行临时SQL查询,而不是编写JavaScript来过滤数组,将其转换为SQL,加载到SQLite中,并用为之构建的语言进行查询。
构建迁徙种子
Framework 迁移通常需要插入参考数据的种子步骤。生成 INSERT 来自已知良好 JSON 固定装置的语句为您提供了一个稳定、可复习的块来粘贴到迁移中,而不是手动翻译数据。
在本地复制生产错误
Bug只针对特定的一组记录出现,把那些记录的JSON,转换成插入,加载到本地数据库中,再现问题,而不需要复制生产数据库。
多行与每行 INSERT:选择哪个?
多行插入将每一行捆绑到一个具有多个语句中 VALUES tuples。compact,加载最快,因为数据库解析和规划一个语句而不是数百个。选择它进行批量加载。
1行1个语句比较啰嗦但是有两个优点,它产生一个文件,每行都是自己的行,在代码审查中读取干净,在版本控制中差异很好,而且如果1行违反约束,其他还是成功,这在你加载凌乱的数据时很方便,想看看哪些行失败而不是丢失整批,选择它用于你将提交或尚未完全信任的数据。
从命令行运行生成的 SQL
1旦你有了脚本, 加载它就是每个引擎中的一行, 对于SQLite, 把输出保存到一个文件中, 并在: sqlite3 mydata.db < insert.sql 创建表并将行加载到一个新的数据库文件中。对于 PostgreSQL, psql -d mydb -f insert.sql 对着命名数据库运行整个脚本。对于 MySQL, mysql -u user -p mydb < insert.sql does一样。因为生成器引用标识符,并转义你所选方言的值,所以脚本运行时没有手写sql通常需要的手动修复。
如果您每行生成一个语句而不是单个多行插入,则将负载包裹在事务中是值得做的。添加 BEGIN; 在顶部和 COMMIT; at the bottom 将数百个单独的插入转入一个原子操作,这既更快又更安全:如果后面的行失败一个约束,则整个负载会回滚,而不是让表一半填充。对于多行插入,这已经是有效的原子,因为它是一个单一的语句。
大阵列的性能和限制
转换本身在输入的大小上是线性的,并且完全在您的浏览器中运行,因此几千行会立即转换。实际的限制不在工具中,而是在您对输出的操作中。单个多行 INSERT 数以万计的元组可以超过数据库's最大语句大小或数据包限制,mysql's max_allowed_packet being the one people stirl。in 加载一个非常大的数组,要么切换到每行一个语句所以每个都很小,要么把输入分成几百行的批量,分别转换。
还有一个值得了解的精确警告。JSON 数字由浏览器's JSON 引擎解析为标准双精度浮点,因此大于约九千万亿的整数,超出 JavaScript's 安全整数范围,可能会在工具看到之前失去精度。那是每个环境中 JSON 解析的属性,而不是转换器的属性,但这意味着一个巨大的数字 id 更安全地表示为源 JSON 中的字符串,然后工具将将其存储为文本并准确保存。
这与其他工具相符的地方
JSON到SQL是一个小家庭的一个方向,当你的来源是电子表格或者CSV导出而不是JSON时, CSV 到 SQL 从同类型推理和转义的表格输入中做同样的工作,当你需要将数据作为平面文件而不是数据库时, json to csv 将数组平坦成行,并且 CSV 转 JSON 倒退。在你转换任何东西之前, json格式化程序 会漂亮打印并验证JSON,这样你就可以在一个杂散逗号变成解析错误之前抓住它。而且一旦你有了SQL, SQL格式化程序 将缩进并调整您自豪提交的迁移文件的语句。
由于在浏览器中执行所有这些操作而不是将数据上传到转换器服务背后的原因, 在线工具中的数据隐私 指南列出了客户端模型,以及 web开发人员工具包 收集与数据转换工作良好配对的实用程序。
常问问题
如何将 JSON 转换为 SQL?
粘贴对象的 JSON 数组,设置表名,然后选择您的 SQL 方言。该工具将对象键读取为列名,推断每种列类型,并生成 CREATE TABLE 加 INSERT 语句,您可以在数据库中复制和运行。
JSON形状接受什么?
1个对象数组是正常输入,其中每个对象为1行,单个对象也工作并产生1行,原语或深度错配的形状数组被拒绝,并错误地解释了预期的内容。
支持哪些 SQL 数据库?
MySQL、PostgreSQL、SQLite、标准SQL的语句转换器输出,你挑选的方言控制标识符引用、类型和布尔语法以及本机JSON类型,所以脚本运行在该数据库中,无需编辑。
列类型是如何决定的?
每列都针对每个对象进行检查。如果所有当前值都是整数,则所有数字都成为整数类型,所有真值或假值都成为布尔类型,所有对象或数组都成为 JSON 类型,任何混合的值都成为文本。
嵌套对象和数组会发生什么?
它们被序列化回 JSON 文本并存储在 MySQL 上的 JSON 列、PostgreSQL 上的 JSONB 列或 SQLite 和标准 SQL 上的 TEXT 列中。这可以保持结构完整,以便您稍后可以使用数据库 JSON 函数进行查询。
如果我的对象有不同的键怎么办?
That is fine。列是出现的每个键的并集,按首先看到的顺序保留。任何缺少其中一个键的对象都会在该列中得到NULL,当对象不统一时,工具会警告您。
字符串和引号是如何逃脱的?
文本值用单引号包裹,值内的任何单引号都加倍,这是标准的 SQL 转义。数字和布尔值不被引用,null 变为 NULL,标识符用 MySQL 的反引号或其他方言的双引号引用。
我的json是否上传到任何地方？
No。Parsing和SQL生成在你的浏览器中以JavaScript运行。没有传输,记录或存储任何内容,并且该工具一旦加载就保持离线工作,您可以在转换时通过查看网络选项卡来确认。
