处理其他 JSON 格式
之前加载 JSON 数据的示例假设使用 JSONEachRow
(NDJSON
) 格式。该格式将每行 JSON 中的键作为列读取。例如:
虽然这通常是 JSON 中最常用的格式,但用户可能会遇到其他格式,或需要将 JSON 作为单个对象读取。
以下是其他常见格式读取和加载 JSON 的示例。
将 JSON 读取为对象
我们之前的示例展示了 JSONEachRow
如何读取换行符分隔的 JSON,每行被读取为映射到表行的单独对象,每个键映射到一列。这对于 JSON 可预测且每列具有单一类型的情况是理想的。
相反,JSONAsObject
将每行视为一个单独的 JSON
对象,并将其存储在一个类型为 JSON
的单列中,更适合需要嵌套 JSON 负载和动态键且可能有多种类型的情况。
对于逐行插入,请使用 JSONEachRow
,对于储存灵活或动态 JSON 数据,请使用 JSONAsObject
。
将上述示例与下面读取相同数据为每行 JSON 对象的查询进行对比:
JSONAsObject
对于使用单个 JSON 对象列插入行非常有用,例如:
在处理对象结构不一致的情况下,JSONAsObject
格式也可能适用于读取换行符分隔的 JSON。例如,如果某个键在各行中类型不同(可能有时是字符串,而其他时候是对象)。在这种情况下,ClickHouse 不能通过 JSONEachRow
推断出稳定的架构,而 JSONAsObject
允许数据在没有严格类型强制的情况下被导入,将每个 JSON 行整体存储在单列中。例如,注意 JSONEachRow
如何在以下示例中失败:
相反,在这种情况下可以使用 JSONAsObject
,因为 JSON
类型支持同一子列的多种类型。
JSON 对象数组
JSON 数据最流行的形式之一是将 JSON 对象的列表放在 JSON 数组中,如 这个示例:
让我们为这种数据创建一个表:
要导入 JSON 对象的列表,可以使用 JSONEachRow
格式(从 list.json 文件插入数据):
我们使用了 FROM INFILE 子句从本地文件加载数据,我们可以看到导入成功:
JSON 对象键
在某些情况下,JSON 对象的列表可以被编码为对象属性,而不是数组元素(例如,见 objects.json):
ClickHouse 可以使用 JSONObjectEachRow
格式加载这种类型的数据:
指定父对象键值
假设我们还想在表中保存父对象键的值。在这种情况下,我们可以使用 以下选项 来定义我们想要保存键值的列名:
现在,我们可以使用 file()
函数检查将从原始 JSON 文件加载的数据:
注意 id
列已经通过键值正确填充。
JSON 数组
有时,为了节省空间,JSON 文件以数组格式而非对象格式编码。在这种情况下,我们处理的是 JSON 数组列表:
在这种情况下,ClickHouse 将加载该数据,并根据数组中的顺序将每个值归属到相应的列。我们使用 JSONCompactEachRow
格式:
从 JSON 数组导入单独列
在某些情况下,数据可以按列而非按行编码。在这种情况下,父 JSON 对象包含带有值的列。看看 以下文件:
ClickHouse 使用 JSONColumns
格式解析这样的数据:
在处理 列的数组 而不是对象时,也支持更紧凑的格式,使用 JSONCompactColumns
格式:
保存 JSON 对象而不是解析
在某些情况下,您可能希望将 JSON 对象保存到单个 String
(或 JSON
)列中,而不是解析它。这在处理具有不同结构的 JSON 对象列表时非常有用。让我们以 这个文件 为例,其中我们在父列表中有多个不同的 JSON 对象:
我们希望将原始 JSON 对象保存到以下表中:
现在我们可以使用 JSONAsString
格式从文件加载数据,以保留 JSON 对象而不是解析它们:
我们可以使用 JSON 函数 查询保存的对象:
注意,JSONAsString
在我们有 JSON 对象逐行格式的文件时(通常与 JSONEachRow
格式一起使用)运行良好。
嵌套对象的架构
在处理 嵌套 JSON 对象 时,我们还可以定义显式架构,并使用复杂类型(Array
、Object Data Type
或 Tuple
)加载数据:
访问嵌套 JSON 对象
我们可以通过启用 以下设置选项 来引用 嵌套 JSON 键:
这使我们能够使用点表示法引用嵌套 JSON 对象键(记得用反引号包裹它们以便正常工作):
通过这种方式,我们可以展平嵌套 JSON 对象,或使用某些嵌套值将其作为单独列保存。
跳过未知列
默认情况下,ClickHouse 在导入 JSON 数据时会忽略未知列。我们试着将原始文件导入到没有 month
列的表中:
我们仍然可以将 3 列的 原始 JSON 数据 插入到该表中:
ClickHouse 在导入时将忽略未知列。这可以通过 input_format_skip_unknown_fields 设置选项禁用:
在 JSON 和表列结构不一致的情况下,ClickHouse 将抛出异常。
BSON
ClickHouse 允许从 BSON 编码文件导入和导出数据。该格式被一些数据库管理系统使用,例如 MongoDB 数据库。
要导入 BSON 数据,我们使用 BSONEachRow 格式。让我们从 此 BSON 文件 导入数据:
我们还可以使用相同的格式导出到 BSON 文件:
之后,我们将把数据导出到 out.bson
文件。