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

其他建模 JSON 的方法

以下是 ClickHouse 中建模 JSON 的替代方案。这些方法是为了完整性而记录的，并且在 JSON 类型开发之前适用，因此在大多数用例中通常不推荐使用。

应用对象级别的方法

在相同的模式中，可以对不同的对象应用不同的技术。例如，某些对象可以使用 String 类型解决，而其他对象则可以使用 Map 类型。请注意，一旦使用了 String 类型，则不需要做进一步的模式决定。相反，可以在 Map 键中嵌套子对象 - 包括一个表示 JSON 的 String - 正如我们下面所示：

使用 String​

如果对象高度动态，结构不可预测并包含任意嵌套对象，用户应使用 String 类型。可以在查询时使用 JSON 函数提取值，如下所示。

对于拥有动态 JSON 的用户，使用上述结构化方法处理数据通常不可行，这些 JSON 要么有可能会变化，要么其模式不易理解。为了获得绝对的灵活性，用户可以简单地将 JSON 作为 String 存储，然后使用函数根据需要提取字段。这代表了将 JSON 处理为结构化对象的极端对立面。这种灵活性带来了显著的缺陷 - 主要是查询语法复杂性的增加以及性能下降。

如前所述，对于 原始人员对象，我们无法确保 tags 列的结构。我们插入原始行（包括 company.labels，我们暂时忽略），将 Tags 列声明为 String：

我们可以选择 tags 列，并看到 JSON 已作为字符串插入：

JSONExtract 函数可用于检索此 JSON 中的值。考虑下面这个简单的例子：

请注意，这些函数需要同时引用 String 列 tags 和提取 JSON 的路径。嵌套路径要求函数嵌套，例如 JSONExtractUInt(JSONExtractString(tags, 'car'), 'year')，它提取列 tags.car.year。通过函数 JSON_QUERY 和 JSON_VALUE，可以简化嵌套路径的提取。

考虑极端情况下的 arxiv 数据集，我们将整个正文视为 String。

要插入此模式，我们需要使用 JSONAsString 格式：

假设我们希望按年份统计发布的论文数量。对比使用字符串的以下查询与模式的 结构化版本：

请注意，这里使用 XPath 表达式来根据方法过滤 JSON，即 JSON_VALUE(body, '$.versions[0].created')。

字符串函数明显比显式类型转换带索引慢 (> 10x)。上述查询始终需要完全扫描整个表并处理每一行。尽管在小数据集上这样的查询仍然会很快，但在更大型的数据集中，性能会降低。

这种方法的灵活性伴随着明显的性能和语法成本，仅应对高度动态的对象使用。

简单 JSON 函数​

上述示例使用了 JSON* 函数系列。它们利用了基于 simdjson 的完整 JSON 解析器，该解析器在解析时很严格，并将处于不同级别的相同字段加以区分。这些函数能够处理语法正确但格式不佳的 JSON，例如键之间的双空格字符。

还有一组更快且更严格的函数。这些 simpleJSON* 函数通过对 JSON 的结构和格式做出严格假设，提供潜在的优越性能。具体要求如下：

  • 字段名必须为常量
  • 字段名称编码一致，例如 simpleJSONHas('{"abc":"def"}', 'abc') = 1，而 visitParamHas('{"\\u0061\\u0062\\u0063":"def"}', 'abc') = 0
  • 字段名称在所有嵌套结构中是唯一的。对嵌套级别不做区分，匹配是无差别的。如果存在多个匹配字段，则使用第一个匹配的字段。
  • 除了字符串文字外，不允许有特殊字符。这包括空格。以下是无效的，并且无法解析。

而下面的将正确解析：

上述查询使用 simpleJSONExtractString 提取 created 键，利用我们只需获取已发布日期的第一个值。在这种情况下，simpleJSON* 函数的限制对性能提升是可接受的。

使用 Map​

如果对象用于存储任意键，且大多数为同一类型，则考虑使用 Map 类型。理想情况下，唯一键的数量不应超过几百个。Map 类型也可以用于具有子对象的对象，前提是后者在其类型上具有一致性。一般来说，我们建议将 Map 类型用于标签和标签，例如日志数据中的 Kubernetes pod 标签。

虽然 Map 提供了一种简单的方式来表示嵌套结构，但他们有一些显著的限制：

  • 所有字段必须是同一类型。
  • 访问子列需要特殊的 Map 语法，因为这些字段不存在为列。整个对象 是 一列。
  • 访问子列会加载整个 Map 值，即所有兄弟和它们各自的值。对于较大的 Map，这可能会导致显著的性能惩罚。
字符串键

在将对象建模为 Map 时，使用 String 键来存储 JSON 键名称。因此 Map 将始终是 Map(String, T)，其中 T 取决于数据。

原始值​

Map 的最简单应用是当对象包含相同的原始类型作为值时。在大多数情况下，这涉及将 String 类型用于值 T。

考虑我们之前的 人员 JSON，其中 company.labels 对象被确定为动态。重要的是，我们只期望将类型为 String 的键值对添加到这个对象中。因此，我们可以将其声明为 Map(String, String)：

我们可以插入我们原始的完整 JSON 对象：

在请求对象中查询这些字段需要使用 Map 语法，例如：

此时可以使用完整的 Map 函数查询，详细描述见 这里。如果您的数据没有一致的类型，则存在执行 必要的类型强制转换 的函数。

对象值​

Map 类型也可以考虑用于具有子对象的对象，只要后者在其类型上保持一致。

假设我们 persons 对象的 tags 键需要一致的结构，其中每个 tag 的子对象具有 name 和 time 列。这样 JSON 文档的简化示例如下：

这可以建模为 Map(String, Tuple(name String, time DateTime))，如下所示：

在这种情况下使用 Map 通常是罕见的，建议对数据进行重建，使动态键名不再有子对象。例如，上述内容可以重建为允许使用 Array(Tuple(key String, name String, time DateTime))。

使用 Nested​

Nested 类型 可用于建模静态对象，这些对象很少发生变化，提供了一种替代 Tuple 和 Array(Tuple) 的方案。我们一般建议避免将此类型用于 JSON，因为它的行为通常令人困惑。Nested 的主要优点是可以在排序键中使用子列。

下面，我们提供一个使用 Nested 类型建模静态对象的示例。考虑以下简单的 JSON 日志条目：

我们可以将 request 键声明为 Nested。类似于 Tuple，我们需要指定子列。

flatten_nested​

设置 flatten_nested 控制嵌套的行为。

flatten_nested=1​

值为 1（默认值）不支持任意层级的嵌套。使用此值时，可以将嵌套数据结构视为多个 Array 列，且长度相同。字段 method、path 和 version 实际上都是独立的 Array(Type) 列，并有一个关键约束：method、path 和 version 字段的长度必须相同。 如果使用 SHOW CREATE TABLE，将会得到如下示例：

下面，我们插入到此表中：

这里有几点重要事项：

  • 我们需要使用 input_format_import_nested_json 设置将 JSON 作为嵌套结构插入。没有此设置，我们需要将 JSON 扁平化，即。
  • 嵌套字段 method、path 和 version 需要作为 JSON 数组传递，即。

可以使用点表示法查询列：

请注意，对子列使用的 Array 意味着可以充分利用全套 Array 函数，包括 ARRAY JOIN 子句 - 如果您的列具有多个值，这将非常有用。

flatten_nested=0​

这允许任意层级的嵌套，并意味着嵌套列保持为单个 Tuple 的数组 - 实际上它们变成了 Array(Tuple) 的相同形式。

这是使用 Nested 的首选方式，通常也是最简单的方式。如我们下面所示，它只要求所有对象都是一个列表。

下面，我们重新创建表并重新插入一行：

这里有几点重要事项：

  • 不需要 input_format_import_nested_json 来插入。
  • SHOW CREATE TABLE 中保留了 Nested 类型。该列的底层实质上是 Array(Tuple(Nested(method LowCardinality(String), path String, version LowCardinality(String))))
  • 因此，我们需要将 request 作为数组插入，即。

可以再次使用点表示法查询列：

示例​

上述数据的大型示例可在 s3 的公共桶中找到：s3://datasets-documentation/http/。

考虑到 JSON 的约束和输入格式，我们使用以下查询插入此示例数据集。在这里，我们设置 flatten_nested=0。

以下语句插入了 1000 万行，因此此过程可能需要几分钟来执行。如有需要，请应用 LIMIT：

查询此数据需要我们将请求字段作为数组访问。下面，我们总结了固定时间段内的错误和 HTTP 方法。

使用成对数组​

成对数组提供了一种在将 JSON 表示为字符串的灵活性和更结构化方法的性能之间的平衡。模式是灵活的，因为可以潜在地向根添加任何新字段。然而，这需要显著更复杂的查询语法，并且与嵌套结构不兼容。

例如，考虑以下表：

要插入此表，我们需要将 JSON 构建为键值对的列表。以下查询说明了如何使用 JSONExtractKeysAndValues 来实现这一点：

注意请求列仍然作为字符串表示的嵌套结构。我们可以向根添加任何新键。我们还可以在 JSON 本身中有任意差异。要插入我们的本地表，请执行以下操作：

查询此结构需要使用 indexOf 函数识别所需键的索引（该索引应与值的顺序一致）。这可以用于访问值数组列，即 values[indexOf(keys, 'status')]。我们仍然需要一个 JSON 解析方法用于请求列 - 在这种情况下为 simpleJSONExtractString。