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

JSON 数据类型

寻找指南吗？

请查看我们的 JSON 最佳实践指南，以获取使用 JSON 类型的示例、先进特性和注意事项。

阅读更多


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

如果您想使用 JSON 类型，并且想要查看本页上的示例，请使用：

但是，如果您使用的是 ClickHouse Cloud，则必须首先 联系支持团队 以启用 JSON 类型的使用。

备注

在 ClickHouse 开源版本中，JSON 数据类型在 25.3 版本中被标记为生产就绪。在以前的版本中，不建议在生产环境中使用该类型。

要声明一个 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 将各种类型强制转换。

从 String 转换为 JSON​

从 Tuple 转换为 JSON​

从 Map 转换为 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 数据进行近乎完全扫描。

路径的类型推断​

在解析 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 对象时，当当前数据块达到限制时， 所有新路径将存储在共享数据结构中。我们可以使用以下两种自省函数 JSONDynamicPaths、JSONSharedDataPaths：

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

在 MergeTree 表引擎的数据部分合并期间​

在将多个数据部分合并到 MergeTree 表中时，结果数据部分中的 JSON 列可能达到动态路径的限制 而无法将源部分中的所有路径存储为子列。 在这种情况下，ClickHouse 选择合并后哪些路径将作为子列保留，哪些路径将存储在共享数据结构中。 在大多数情况下，ClickHouse 尝试保留包含最多非 NULL 值的路径，并将最少见的路径移动到共享数据结构中。不过，这取决于实现。

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

每次插入都会创建一个包含单一路径的 JSON 列的单独数据部分：

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

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

自省函数​

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

示例

让我们调查日期为 2020-01-01 的 GH Archive 数据集的内容：

ALTER MODIFY COLUMN 到 JSON 类型​

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

示例

比较 JSON 类型值​

JSON 对象的比较与 Map 类似。

例如：

注意： 当 2 个路径包含不同数据类型的值时，它们将根据 Variant 数据类型的 比较规则 进行比较。

有关更好使用 JSON 类型的提示​

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

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

进一步阅读​