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

JSON 数据类型

Beta feature. Learn more.

JSON 类型将 JavaScript 对象表示法 (JSON) 文档存储在单个列中。

备注

此功能正在测试阶段，尚未准备好投入生产。如果您需要处理 JSON 文档，请考虑使用 此指南。

如果您想使用 JSON 类型，以及本页面上的示例，请使用：

要声明一个 JSON 类型的列，可以使用以下语法：

其中，上述语法中的参数定义如下：

参数描述默认值
max_dynamic_paths一个可选参数，指示可以单独存储多少路径作为子列，跨越存储在单个数据块中的单个数据部分（例如，MergeTree 表）。

如果超过此限制，所有其他路径将作为单个结构合并存储。		1024
max_dynamic_types介于 1 和 255 之间的一个可选参数，指示可以存储多少种不同数据类型在单个路径列中，类型为 Dynamic，跨越存储在单个数据块中的单个数据部分（例如，MergeTree 表）。

如果超出此限制，所有新类型将转换为类型 String。		32
some.path TypeName特定路径的可选类型提示。这样的路径将始终作为指定类型的子列存储。
SKIP path.to.skip对特定路径的可选提示，表示在 JSON 解析期间应跳过该路径。这样的路径将永远不会存储在 JSON 列中。如果指定的路径是嵌套的 JSON 对象，则整个嵌套对象将被跳过。
SKIP REGEXP 'path_regexp'一个可选提示，使用正则表达式，在 JSON 解析过程中跳过路径。所有匹配此正则表达式的路径将永远不会存储在 JSON 列中。

创建 JSON​

在本节中，我们将看一下创建 JSON 的各种方式。

在表列定义中使用 JSON​

使用 CAST 和 ::JSON​

可以使用特殊语法 ::JSON 将各种类型转换为 JSON。

从 字符串 转换为 JSON​

从 元组 转换为 JSON​

从 映射 转换为 JSON​

从已弃用的 Object('json') 转换为 JSON​

备注

JSON 路径被存储为扁平结构。这意味着，当从路径如 a.b.c 格式化 JSON 对象时，无法知道该对象是应该构建为 { "a.b.c" : ... } 还是 { "a" : {"b" : {"c" : ... }}}。我们的实现将始终假设后者。

例如：

将返回：

而 不是：

读取 JSON 路径作为子列​

JSON 类型支持将每个路径作为单独的子列进行读取。 如果请求的路径类型在 JSON 类型声明中未指定，则该路径的子列将始终具有类型 Dynamic。

例如：

如果请求的路径在数据中未找到，则将用 NULL 值填充：

让我们检查返回的子列的数据类型：

如我们所见，a.b 的类型是 UInt32，因为我们在 JSON 类型声明中指定了，所有其他子列的类型是 Dynamic。

还可以使用特殊语法 json.some.path.:TypeName 读取 Dynamic 类型的子列：

Dynamic 子列可以转换为任何数据类型。在这种情况下，如果 Dynamic 内部类型无法被转换为请求类型，将抛出异常：

读取 JSON 子对象作为子列​

JSON 类型支持使用特殊语法 json.^some.path 将嵌套对象作为子列读取：

备注

以子列读取子对象可能效率低下，因为这可能需要对 JSON 数据进行几乎全面的扫描。

路径的类型推断​

在解析 JSON 时，ClickHouse 会尝试检测每个 JSON 路径的最合适数据类型。 它的工作方式类似于 输入数据的自动架构推断，并受相同设置控制：

让我们看看一些示例：

处理 JSON 对象数组​

包含对象数组的 JSON 路径被解析为类型 Array(JSON) 并插入到该路径的 Dynamic 列中。 要读取对象数组，您可以将其作为子列从 Dynamic 列中提取：

您可能已注意到，嵌套 JSON 类型的 max_dynamic_types/max_dynamic_paths 参数与默认值相比有所减少。 这是为避免在 JSON 对象的嵌套数组中子列数量失控而需要的。

让我们尝试从嵌套 JSON 列中读取子列：

我们可以使用特殊语法避免编写 Array(JSON) 子列名称：

路径后面的 [] 表示数组级别。例如，json.path[][] 将被转换为 json.path.:Array(Array(JSON))

让我们检查我们 Array(JSON) 中的路径和类型：

让我们从 Array(JSON) 列读取子列：

我们还可以从嵌套的 JSON 列读取子对象子列：

从数据中读取 JSON 类型​

所有文本格式 (JSONEachRow、 TSV、 CSV、 CustomSeparated、 Values 等) 支持读取 JSON 类型。

示例：

对于像 CSV/TSV/等的文本格式，JSON 是从包含 JSON 对象的字符串解析的：

达到 JSON 中动态路径的限制​

JSON 数据类型只能将有限数量的路径作为内部单独子列存储。 默认情况下，此限制为 1024，但是您可以在类型声明中使用参数 max_dynamic_paths 更改此限制。

当达到限制时，插入到 JSON 列中的所有新路径将存储在单个共享数据结构中。 仍然可以将这些路径作为子列读取，但将需要读取整个共享数据结构以提取该路径的值。 此限制的设定是为了避免生成过多的不同子列，使得表无法使用。

让我们看看在几种不同情况下达到限制时会发生什么。

在数据解析期间达到限制​

在从数据解析 JSON 对象时，当对当前数据块达到限制时，所有新路径将存储在共享数据结构中。我们可以使用以下两个 introspection 函数 JSONDynamicPaths，JSONSharedDataPaths：

正如我们所见，在插入路径 e 和 f.g 之后达到了限制， 它们被插入到共享数据结构中。

在MergeTree表引擎中合并数据分片期间​

在MergeTree表的多个数据分片合并期间，生成的数据部分中的JSON列可能会达到动态路径的限制，并且无法将所有源部分的路径作为子列存储。在这种情况下，ClickHouse会选择哪些路径在合并后保留为子列，哪些路径会存储在共享数据结构中。在大多数情况下，ClickHouse会尝试保留包含最多非空值的路径，并将稀有的路径移到共享数据结构中。然而，这取决于具体的实现。

让我们看一个这样的合并示例。首先，我们创建一个具有JSON列的表，将动态路径的限制设置为3，然后插入具有5个不同路径的值：

每次插入将创建一个单独的数据分片，JSON列包含一个路径：

现在，让我们将所有分片合并为一个，并查看会发生什么：

如我们所见，ClickHouse保留了最常见的路径a、b和c，并将路径d和e移至共享数据结构中。

观察函数​

有几个函数可以帮助检查JSON列的内容：

示例

让我们检查GH Archive数据集中2020-01-01的内容：

ALTER MODIFY COLUMN 修改为JSON类型​

可以更改现有表并将列的类型更改为新的JSON类型。目前仅支持从String类型的ALTER。

示例

JSON类型值之间的比较​

JSON列的值无法使用less/greater函数进行比较，但可以使用equal函数进行比较。

当两个JSON对象具有相同的路径集合，并且每个路径在两个对象中具有相同的类型和值时，被认为是相等的。

例如：

更好使用JSON类型的提示​

在创建JSON列并向其中加载数据之前，请考虑以下提示：

  • 调查您的数据并尽可能多地指定路径提示及其类型。这将使存储和读取效率更高。
  • 考虑您需要哪些路径以及哪些路径是永远不需要的。将您不需要的路径在SKIP部分中指定，必要时也可以在SKIP REGEXP部分中指定。这将改善存储。
  • 不要将max_dynamic_paths参数设置为非常高的值，因为这可能会降低存储和读取效率。虽然高度依赖于系统参数，例如内存、CPU等，但一般经验法则是不要将max_dynamic_paths设置为> 10,000。

进一步阅读​