跳到主要内容
跳到主要内容

处理其他 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 对象 时,我们还可以定义显式架构,并使用复杂类型(ArrayObject Data TypeTuple)加载数据:

访问嵌套 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 文件。