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

其他建模 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 中的值。考虑下面这个简单的例子:

请注意,这些函数需要同时引用 Stringtags 和提取 JSON 的路径。嵌套路径要求函数嵌套,例如 JSONExtractUInt(JSONExtractString(tags, 'car'), 'year'),它提取列 tags.car.year。通过函数 JSON_QUERYJSON_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 的子对象具有 nametime 列。这样 JSON 文档的简化示例如下:

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

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

使用 Nested

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

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

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

flatten_nested

设置 flatten_nested 控制嵌套的行为。

flatten_nested=1

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

下面,我们插入到此表中:

这里有几点重要事项:

  • 我们需要使用 input_format_import_nested_json 设置将 JSON 作为嵌套结构插入。没有此设置,我们需要将 JSON 扁平化,即。
  • 嵌套字段 methodpathversion 需要作为 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