ClickHouse 官方文档：处理 JSON 数据（1/2）

ClickHouseInc 2025-01-02

1349

本文字数：27902；估计阅读时间：70分钟

概述

ClickHouse 提供了多种处理 JSON 的方法，每种方法都有其优缺点和适用场景。在本指南中，我们将介绍如何加载 JSON 并优化架构设计。本指南包括以下内容：

加载 JSON：在 ClickHouse 中使用简单架构加载和查询 JSON（尤其是 NDJSON）。
JSON 架构推断：通过 JSON 架构推断查询 JSON 并生成表结构。
设计 JSON 架构：设计并优化 JSON 架构的步骤。
导出 JSON：JSON 的导出方法。
处理其他 JSON 格式：处理非 NDJSON 格式 JSON 的技巧。
其他 JSON 建模方法：高级 JSON 建模方法（不推荐使用）。

重要：全新 JSON 类型已进入 Beta 阶段
本指南涵盖现有的 JSON 处理技术。值得注意的是，ClickHouse 已推出一种新的 JSON 类型，目前处于 Beta 阶段。详情请参见这里。【https://clickhouse.com/docs/en/sql-reference/data-types/newjson】

加载 JSON 数据

本节假设 JSON 数据采用 NDJSON（换行分隔的 JSON，Newline delimited JSON）格式，在 ClickHouse 中称为 JSONEachRow。这种格式因其简洁性和高效的空间利用率而成为加载 JSON 的首选，但 ClickHouse 同样支持其他格式的输入和输出。

以下示例展示了一行来自 Python PyPI 数据集的 JSON 数据：

{
  "date": "2022-11-15",
  "country_code": "ES",
  "project": "clickhouse-connect",
  "type": "bdist_wheel",
  "installer": "pip",
  "python_minor": "3.9",
  "system": "Linux",
  "version": "0.3.0"
}

要将该 JSON 对象加载到 ClickHouse 中，需要先定义表架构。以下是一个简单的架构示例，其中 JSON 的键被映射为表的列名：

CREATE TABLE pypi (
  `date` Date,
  `country_code` String,
  `project` String,
  `type` String,
  `installer` String,
  `python_minor` String,
  `system` String,
  `version` String
)
ENGINE = MergeTree
ORDER BY (project, date)

排序键
我们使用 ORDER BY 子句定义了一个排序键。有关排序键的详细信息以及如何选择，请参考相关文档。【https://clickhouse.com/docs/en/data-modeling/schema-design#choosing-an-ordering-key】

ClickHouse 支持以多种格式加载 JSON 数据，并能根据文件扩展名和内容自动推断其类型。以下示例中，我们通过 S3 函数读取了与上述表对应的 JSON 文件：

SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/json/*.json.gz')
LIMIT 1
┌───────date─┬─country_code─┬─project────────────┬─type────────┬─installer────┬─python_minor─┬─system─┬─version─┐
│ 2022-11-15 │ CN           │ clickhouse-connect │ bdist_wheel │ bandersnatch │              │        │ 0.2.8   │
└────────────┴──────────────┴────────────────────┴─────────────┴──────────────┴──────────────┴────────┴─────────┘


1 row in set. Elapsed: 1.232 sec.

注意，这里无需显式指定文件格式。我们通过通配模式读取桶中的所有 *.json.gz 文件，ClickHouse 会根据文件扩展名和内容自动识别格式为 JSONEachRow（ndjson）。如果格式无法自动识别，可以通过参数函数手动指定。

SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/json/*.json.gz', JSONEachRow)

压缩文件
上述文件均已压缩，ClickHouse 能够自动检测并处理这些压缩文件。

要加载这些文件中的数据行，可以使用 INSERT INTO SELECT：

INSERT INTO pypi SELECT * FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/pypi/json/*.json.gz')
Ok.


0 rows in set. Elapsed: 10.445 sec. Processed 19.49 million rows, 35.71 MB (1.87 million rows/s., 3.42 MB/s.)


SELECT * FROM pypi LIMIT 2


┌───────date─┬─country_code─┬─project────────────┐
│ 2022-05-26 │ CN           │ clickhouse-connect │
│ 2022-05-26 │ CN           │ clickhouse-connect │
└────────────┴──────────────┴────────────────────┘


2 rows in set. Elapsed: 0.005 sec. Processed 8.19 thousand rows, 908.03 KB (1.63 million rows/s., 180.38 MB/s.)

数据行也可以通过 FORMAT 子句直接加载，例如：

INSERT INTO pypi
FORMAT JSONEachRow
{"date":"2022-11-15","country_code":"CN","project":"clickhouse-connect","type":"bdist_wheel","installer":"bandersnatch","python_minor":"","system":"","version":"0.2.8"}

以上示例假设使用 JSONEachRow 格式。ClickHouse 还支持其他常见的 JSON 格式，其加载方法请参考相关示例。【https://clickhouse.com/docs/en/integrations/data-formats/json/other-formats】

上述内容展示了加载 JSON 数据的基础方法。对于更复杂的 JSON 数据结构（如嵌套结构），请参考“设计 JSON 架构”指南。【https://clickhouse.com/docs/en/integrations/data-formats/json/schema】

JSON 架构自动推断

ClickHouse 支持自动推断 JSON 数据的结构。这一功能允许直接查询 JSON 数据，例如使用 clickhouse-local 查询磁盘数据，或查询存储在 S3 存储桶中的数据。此外，还可以在数据加载到 ClickHouse 之前自动生成表架构。

适用场景

结构一致：用于类型推断的数据需包含所有目标列。如果推断完成后数据增加了额外的列，这些列可能无法被查询。
类型一致：特定列的类型需要相互兼容。

注意事项
如果 JSON 数据具有动态结构，例如频繁新增键但未能及时更新架构（如日志中的 Kubernetes 标签），建议参考“设计 JSON 架构”指南。

类型检测

在之前的示例中，我们使用了 NDJSON 格式的 Python PyPI 数据集的简单版本。本节将探索一个更复杂的数据集——arXiv 数据集。该数据集包含约 250 万篇学术论文，以 NDJSON 格式分发，每一行代表一篇已发表的学术论文。以下为其中一行示例：

{
  "id": "2101.11408",
  "submitter": "Daniel Lemire",
  "authors": "Daniel Lemire",
  "title": "Number Parsing at a Gigabyte per Second",
  "comments": "Software at https://github.com/fastfloat/fast_float and\n  https://github.com/lemire/simple_fastfloat_benchmark/",
  "journal-ref": "Software: Practice and Experience 51 (8), 2021",
  "doi": "10.1002/spe.2984",
  "report-no": null,
  "categories": "cs.DS cs.MS",
  "license": "http://creativecommons.org/licenses/by/4.0/",
  "abstract": "With disks and networks providing gigabytes per second ....\n",
  "versions": [
    {
      "created": "Mon, 11 Jan 2021 20:31:27 GMT",
      "version": "v1"
    },
    {
      "created": "Sat, 30 Jan 2021 23:57:29 GMT",
      "version": "v2"
    }
  ],
  "update_date": "2022-11-07",
  "authors_parsed": [
    [
      "Lemire",
      "Daniel",
      ""
    ]
  ]
}

该数据集需要更复杂的架构设计。以下将概述定义此架构的过程，并介绍如 Tuple 和 Array 等复杂类型。

数据集存储于公共 S3 存储桶，路径为 s3://datasets-documentation/arxiv/arxiv.json.gz。

如示例所示，该数据集包含嵌套的 JSON 对象。尽管用户通常需要设计并版本化架构，但通过类型推断功能，可直接从数据中推断出类型。此功能允许自动生成架构的 DDL，避免手动创建架构，加速开发流程。

自动格式检测
除了自动推断架构，JSON 架构推断还会根据文件扩展名和内容检测数据格式。上述文件会被系统自动识别为 NDJSON 格式。

通过结合 s3 函数与 DESCRIBE 命令，可查看推断出的数据类型。

DESCRIBE TABLE s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz')
SETTINGS describe_compact_output = 1

┌─name───────────┬─type────────────────────────────────────────────────────────────────────┐
│ id             │ Nullable(String)                                                        │
│ submitter      │ Nullable(String)                                                        │
│ authors        │ Nullable(String)                                                        │
│ title          │ Nullable(String)                                                        │
│ comments       │ Nullable(String)                                                        │
│ journal-ref    │ Nullable(String)                                                        │
│ doi            │ Nullable(String)                                                        │
│ report-no      │ Nullable(String)                                                        │
│ categories     │ Nullable(String)                                                        │
│ license        │ Nullable(String)                                                        │
│ abstract       │ Nullable(String)                                                        │
│ versions       │ Array(Tuple(created Nullable(String),version Nullable(String)))         │
│ update_date    │ Nullable(Date)                                                          │
│ authors_parsed │ Array(Array(Nullable(String)))                                          │
└────────────────┴─────────────────────────────────────────────────────────────────────────┘

避免空值
推断结果显示，许多列被标记为 Nullable 类型。我们不建议在非必要情况下使用 Nullable 类型。您可以通过 schema_inference_make_columns_nullable 参数控制 Nullable 类型的应用行为。

系统会自动将大多数列检测为 String 类型，而 update_date 列则正确识别为 Date 类型。versions 列被定义为 Array(Tuple(created String, version String))，用于存储对象列表；authors_parsed 列则为 Array(Array(String))，表示嵌套数组。

控制类型检测
日期和时间的自动检测可通过参数 input_format_try_infer_dates 和 input_format_try_infer_datetimes 分别进行控制（两者默认启用）。对象被推断为 Tuple 的行为由参数 input_format_json_try_infer_named_tuples_from_objects 控制。其他与 JSON 架构推断相关的设置，例如数字类型的自动检测，可参考相关文档。

通过架构推断查询 JSON 数据

通过架构推断，我们可以直接查询 JSON 数据。以下示例展示了如何利用自动检测的日期和数组字段，统计每年的主要作者。

SELECT
    toYear(update_date) AS year,
    authors,
    count() AS c
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz')
GROUP BY
    year,
    authors
ORDER BY
    year ASC,
    c DESC
LIMIT 1 BY year


┌─year─┬─authors────────────────────────────────────┬───c─┐
│ 2007 │ The BABAR Collaboration, B. Aubert, et al  │  98 │
│ 2008 │ The OPAL collaboration, G. Abbiendi, et al │  59 │
│ 2009 │ Ashoke Sen                                 │  77 │
│ 2010 │ The BABAR Collaboration, B. Aubert, et al  │ 117 │
│ 2011 │ Amelia Carolina Sparavigna                 │  21 │
│ 2012 │ ZEUS Collaboration                         │ 140 │
│ 2013 │ CMS Collaboration                          │ 125 │
│ 2014 │ CMS Collaboration                          │  87 │
│ 2015 │ ATLAS Collaboration                        │ 118 │
│ 2016 │ ATLAS Collaboration                        │ 126 │
│ 2017 │ CMS Collaboration                          │ 122 │
│ 2018 │ CMS Collaboration                          │ 138 │
│ 2019 │ CMS Collaboration                          │ 113 │
│ 2020 │ CMS Collaboration                          │  94 │
│ 2021 │ CMS Collaboration                          │  69 │
│ 2022 │ CMS Collaboration                          │  62 │
│ 2023 │ ATLAS Collaboration                        │ 128 │
│ 2024 │ ATLAS Collaboration                        │ 120 │
└──────┴────────────────────────────────────────────┴─────┘


18 rows in set. Elapsed: 20.172 sec. Processed 2.52 million rows, 1.39 GB (124.72 thousand rows/s., 68.76 MB/s.)

架构推断使我们无需预先定义架构即可查询 JSON 文件，从而加快临时数据分析的过程。

创建表

通过架构推断，可以自动生成表的架构。以下 CREATE AS EMPTY 命令将推断表的 DDL 并创建表，但不会加载任何数据：

CREATE TABLE arxiv
ENGINE = MergeTree
ORDER BY update_date EMPTY
AS SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz')
SETTINGS schema_inference_make_columns_nullable = 0

要确认生成的表架构，可以使用 SHOW CREATE TABLE 命令：

SHOW CREATE TABLE arxiv


CREATE TABLE arxiv
(
    `id` String,
    `submitter` String,
    `authors` String,
    `title` String,
    `comments` String,
    `journal-ref` String,
    `doi` String,
    `report-no` String,
    `categories` String,
    `license` String,
    `abstract` String,
    `versions` Array(Tuple(created String, version String)),
    `update_date` Date,
    `authors_parsed` Array(Array(String))
)
ENGINE = MergeTree
ORDER BY update_date
SETTINGS index_granularity = 8192

以上架构正确匹配数据。架构推断是通过对数据进行采样并逐行读取实现的。列值根据文件格式提取，递归解析器与启发式算法共同确定每个值的类型。在架构推断过程中，读取的最大行数和字节数分别由参数 input_format_max_rows_to_read_for_schema_inference（默认值为 25000）和 input_format_max_bytes_to_read_for_schema_inference（默认值为 32MB）控制。如果推断结果不准确，用户可以参考相关文档提供必要的提示。

从片段创建表

在上述示例中，我们使用了 S3 文件来创建表架构。用户也可以从单行 JSON 数据片段生成表架构。以下为使用 format 函数实现的示例：

CREATE TABLE arxiv
ENGINE = MergeTree
ORDER BY update_date EMPTY
AS SELECT *
FROM format(JSONEachRow, '{"id":"2101.11408","submitter":"Daniel Lemire","authors":"Daniel Lemire","title":"Number Parsing at a Gigabyte per Second","comments":"Software at https://github.com/fastfloat/fast_float and","doi":"10.1002/spe.2984","report-no":null,"categories":"cs.DS cs.MS","license":"http://creativecommons.org/licenses/by/4.0/","abstract":"Withdisks and networks providing gigabytes per second ","versions":[{"created":"Mon, 11 Jan 2021 20:31:27 GMT","version":"v1"},{"created":"Sat, 30 Jan 2021 23:57:29 GMT","version":"v2"}],"update_date":"2022-11-07","authors_parsed":[["Lemire","Daniel",""]]}') SETTINGS schema_inference_make_columns_nullable = 0


SHOW CREATE TABLE arxiv


CREATE TABLE arxiv
(
    `id` String,
    `submitter` String,
    `authors` String,
    `title` String,
    `comments` String,
    `doi` String,
    `report-no` String,
    `categories` String,
    `license` String,
    `abstract` String,
    `versions` Array(Tuple(created String, version String)),
    `update_date` Date,
    `authors_parsed` Array(Array(String))
)
ENGINE = MergeTree
ORDER BY update_date

加载 JSON 数据到表中

在前面的命令中，我们已创建了一个可加载数据的表。使用以下 INSERT INTO SELECT 命令，可以将数据插入表中：

INSERT INTO arxiv SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz')


0 rows in set. Elapsed: 38.498 sec. Processed 2.52 million rows, 1.39 GB (65.35 thousand rows/s., 36.03 MB/s.)
Peak memory usage: 870.67 MiB.

如果需要从其他来源（如文件）加载数据，请参考相关示例。【https://clickhouse.com/docs/en/sql-reference/statements/insert-into】

数据加载完成后，即可进行查询。您还可以选择使用 PrettyJSONEachRow 格式，将数据行显示为其原始结构：

SELECT *
FROM arxiv
LIMIT 1
FORMAT PrettyJSONEachRow


{
    "id": "0704.0004",
    "submitter": "David Callan",
    "authors": "David Callan",
    "title": "A determinant of Stirling cycle numbers counts unlabeled acyclic",
    "comments": "11 pages",
    "journal-ref": "",
    "doi": "",
    "report-no": "",
    "categories": "math.CO",
    "license": "",
    "abstract": "  We show that a determinant of Stirling cycle numbers counts unlabeled acyclic\nsingle-source automata.",
    "versions": [
        {
            "created": "Sat, 31 Mar 2007 03:16:14 GMT",
            "version": "v1"
        }
    ],
    "update_date": "2007-05-23",
    "authors_parsed": [
        [
            "Callan",
            "David"
        ]
    ]
}


1 row in set. Elapsed: 0.009 sec.

处理错误

在某些情况下，可能会遇到无效数据，例如列的类型不匹配或 JSON 格式错误。为应对这些问题，可以通过设置 input_format_allow_errors_ratio 来忽略一定比例的错误数据，从而避免插入失败。此外，还可以通过提供推断提示来优化结果。

延伸阅读

有关数据类型推断的更多信息，请参考相关文档【https://clickhouse.com/docs/en/interfaces/schema-inference】。

设计 JSON 数据架构

尽管可以利用架构推断为 JSON 数据创建初始架构并直接查询（如查询存储在 S3 上的文件），但用户应优先为数据设计一个经过优化的版本化架构。以下探讨 JSON 结构建模的优化方案。

优化字段提取

建议将常用的 JSON 键提取为架构的根列。这不仅简化了查询语法，还能将这些列用于 ORDER BY 子句或创建二级索引。

以JSON 架构推断指南中的 arxiv 数据集为例：

{
  "id": "2101.11408",
  "submitter": "Daniel Lemire",
  "authors": "Daniel Lemire",
  "title": "Number Parsing at a Gigabyte per Second",
  "comments": "Software at https://github.com/fastfloat/fast_float and\n  https://github.com/lemire/simple_fastfloat_benchmark/",
  "journal-ref": "Software: Practice and Experience 51 (8), 2021",
  "doi": "10.1002/spe.2984",
  "report-no": null,
  "categories": "cs.DS cs.MS",
  "license": "http://creativecommons.org/licenses/by/4.0/",
  "abstract": "With disks and networks providing gigabytes per second ....\n",
  "versions": [
    {
      "created": "Mon, 11 Jan 2021 20:31:27 GMT",
      "version": "v1"
    },
    {
      "created": "Sat, 30 Jan 2021 23:57:29 GMT",
      "version": "v2"
    }
  ],
  "update_date": "2022-11-07",
  "authors_parsed": [
    [
      "Lemire",
      "Daniel",
      ""
    ]
  ]
}

假设需要将 versions.created 的第一个值作为主要排序键，并将其命名为 published_date。建议在数据插入之前提取该字段，或者通过 ClickHouse 的物化视图或物化列在插入时完成提取。

如果提取逻辑较为简单，可优先使用物化列，这是提取数据的最简便方法。例如，可以在 arxiv 的架构中添加 published_date 作为物化列，并将其定义为排序键，如下所示：

CREATE TABLE arxiv
(
    `id` String,
    `submitter` String,
    `authors` String,
    `title` String,
    `comments` String,
    `journal-ref` String,
    `doi` String,
    `report-no` String,
    `categories` String,
    `license` String,
    `abstract` String,
    `versions` Array(Tuple(created String, version String)),
    `update_date` Date,
    `authors_parsed` Array(Array(String)),
    `published_date` DateTime DEFAULT parseDateTimeBestEffort(versions[1].1)
)
ENGINE = MergeTree
ORDER BY published_date

嵌套列的表达式
此操作需使用 versions[1].1 的符号来访问元组，以位置引用 created 列，而非更直观的 versions.created_at[1] 语法。

在数据加载时，该列将被自动提取：

INSERT INTO arxiv SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz')
0 rows in set. Elapsed: 39.827 sec. Processed 2.52 million rows, 1.39 GB (63.17 thousand rows/s., 34.83 MB/s.)


SELECT published_date
FROM arxiv_2
LIMIT 2
┌──────published_date─┐
│ 2007-03-31 02:26:18 │
│ 2007-03-31 03:16:14 │
└─────────────────────┘


2 rows in set. Elapsed: 0.001 sec.

物化列的行为
物化列的值仅在插入时计算，无法通过 INSERT 查询直接指定。默认情况下，SELECT * 查询不会返回物化列，以确保查询结果可以直接用于 INSERT 操作。如果需要包含物化列，可设置参数 asterisk_include_materialized_columns=1。

对于更复杂的过滤和转换任务，推荐使用物化视图。

区分静态与动态 JSON 数据

为 JSON 数据设计架构的关键任务是确定每个键的适当数据类型。建议用户针对 JSON 层级中的每个键递归应用以下规则，明确其类型。

基本类型：当键值为基本类型时，无论其位于根对象还是子对象，都应依据通用的架构设计最佳实践和类型优化规则选择类型。例如，对于基本类型数组（如 phone_numbers），可以定义为 Array(<类型>)，如 Array(String)。
静态与动态：如果键值是复杂对象（如对象或对象数组），需判断其是否会变化。对于新键添加较少且可以通过 ALTER TABLE ADD COLUMN 命令更新架构处理的对象，可视为静态对象，包括部分键缺失的对象。频繁且不可预测地新增键的对象，应视为动态对象。有关如何区分静态和动态对象，请参考以下“处理静态对象”和“处理动态对象”章节。

重要提示

上述规则应递归应用。如果某键被判断为动态对象，无需进一步分析，可直接参照“处理动态对象”部分的指导。如果判断为静态对象，则继续分析其子键，直到所有键值为基本类型或遇到动态键。

以下 JSON 示例描述了一个人员信息对象，演示了上述规则的应用：

{
  "id": 1,
  "name": "Clicky McCliickHouse",
  "username": "Clicky",
  "email": "clicky@clickhouse.com",
  "address": [
    {
      "street": "Victor Plains",
      "suite": "Suite 879",
      "city": "Wisokyburgh",
      "zipcode": "90566-7771",
      "geo": {
        "lat": -43.9509,
        "lng": -34.4618
      }
    }
  ],
  "phone_numbers": ["010-692-6593", "020-192-3333"],
  "website": "clickhouse.com",
  "company": {
    "name": "ClickHouse",
    "catchPhrase": "The real-time data warehouse for analytics",
    "labels": {
      "type": "database systems",
      "founded": "2021"
    }
  },
  "dob": "2007-03-31",
  "tags": {
    "hobby": "Databases",
    "holidays": [
      {
        "year": 2024,
        "location": "Azores, Portugal"
      }
    ],
    "car": {
      "model": "Tesla",
      "year": 2023
    }
  }
}

规则应用：

根键 name、username、email 和 website 的类型为 String；phone_numbers 列为基本类型数组，类型为 Array(String)；dob 和 id 的类型分别为 Date 和 UInt32。
address 对象不会新增键（但可能新增地址对象），因此被视为静态对象。递归分析表明，除 geo 外，其子列均为基本类型（类型为 String）；geo 是静态结构，包含两个 Float32 列：lat 和 lon。
tags 列为动态对象。可能动态新增任意类型和结构的标签。
company 对象为静态，最多包含 3 个固定键。子键 name 和 catchPhrase 的类型为 String；labels 键为动态对象，可能新增任意键值对标签，值均为 String 类型。

处理静态 JSON 对象

建议使用命名元组（Tuple）来处理静态 JSON 对象。对于对象数组，可以采用元组数组（Array(Tuple)）的方式表示。元组内部的列及其类型应遵循相同的规则定义。嵌套的元组可以用于表示嵌套对象，如下所示。

为说明这一点，我们采用前述人员信息 JSON 示例，去除动态对象部分：

{
  "id": 1,
  "name": "Clicky McCliickHouse",
  "username": "Clicky",
  "email": "clicky@clickhouse.com",
  "address": [
    {
      "street": "Victor Plains",
      "suite": "Suite 879",
      "city": "Wisokyburgh",
      "zipcode": "90566-7771",
      "geo": {
        "lat": -43.9509,
        "lng": -34.4618
      }
    }
  ],
  "phone_numbers": ["010-692-6593", "020-192-3333"],
  "website": "clickhouse.com",
  "company": {
    "name": "ClickHouse",
    "catchPhrase": "The real-time data warehouse for analytics"
  },
  "dob": "2007-03-31"
}

以下是表的架构定义：

CREATE TABLE people
(
    `id` Int64,
    `name` String,
    `username` String,
    `email` String,
    `address` Array(Tuple(city String, geo Tuple(lat Float32, lng Float32), street String, suite String, zipcode String)),
    `phone_numbers` Array(String),
    `website` String,
    `company` Tuple(catchPhrase String, name String),
    `dob` Date
)
ENGINE = MergeTree
ORDER BY username

请注意，company 列被定义为 Tuple(catchPhrase String, name String)，而 address 列使用了 Array(Tuple)，其中嵌套元组表示 geo 列。

JSON 数据可以按现有结构直接插入表中：

INSERT INTO people FORMAT JSONEachRow
{"id":1,"name":"Clicky McCliickHouse","username":"Clicky","email":"clicky@clickhouse.com","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics"},"dob":"2007-03-31"}

在上述示例中，数据量较少，但正如下例所示，可以通过点号分隔的路径来查询元组字段。

SELECT
    address.street,
    company.name
FROM people


┌─address.street────┬─company.name─┐
│ ['Victor Plains'] │ ClickHouse   │
└───────────────────┴──────────────┘

例如，address.street 列以数组形式返回。如果需要查询数组中特定位置的对象，可以在列名后指定数组偏移量。例如，要访问第一个地址的 street 字段：

SELECT address.street[1] AS street
FROM people


┌─street────────┐
│ Victor Plains │
└───────────────┘


1 row in set. Elapsed: 0.001 sec.

Tuple 的局限在于其子列无法作为排序键。例如，以下排序操作会失败：

CREATE TABLE people
(
    `id` Int64,
    `name` String,
    `username` String,
    `email` String,
    `address` Array(Tuple(city String, geo Tuple(lat Float32, lng Float32), street String, suite String, zipcode String)),
    `phone_numbers` Array(String),
    `website` String,
    `company` Tuple(catchPhrase String, name String),
    `dob` Date
)
ENGINE = MergeTree
ORDER BY company.name


Code: 47. DB::Exception: Missing columns: 'company.name' while processing query: 'company.name', required columns: 'company.name' 'company.name'. (UNKNOWN_IDENTIFIER)

元组在排序键中的应用
虽然元组的子列不能单独用作排序键，但整个元组可以用于排序键。然而，这种用法通常意义不大。

默认值的处理

尽管 JSON 对象可能是结构化的，但它们通常是稀疏的，仅包含部分已知键。Tuple 类型无需 JSON 数据中包含所有列；对于未提供的键，将自动使用默认值。

以下示例展示了人员表中一个缺少 suite、geo、phone_numbers 和 catchPhrase 键的稀疏 JSON 数据：

{
  "id": 1,
  "name": "Clicky McCliickHouse",
  "username": "Clicky",
  "email": "clicky@clickhouse.com",
  "address": [
    {
      "street": "Victor Plains",
      "city": "Wisokyburgh",
      "zipcode": "90566-7771"
    }
  ],
  "website": "clickhouse.com",
  "company": {
    "name": "ClickHouse"
  },
  "dob": "2007-03-31"
}

可以看到，该行成功插入表中：

INSERT INTO people FORMAT JSONEachRow
{"id":1,"name":"Clicky McCliickHouse","username":"Clicky","email":"clicky@clickhouse.com","address":[{"street":"Victor Plains","city":"Wisokyburgh","zipcode":"90566-7771"}],"website":"clickhouse.com","company":{"name":"ClickHouse"},"dob":"2007-03-31"}


Ok.


1 row in set. Elapsed: 0.002 sec.

查询这行数据时，未提供的列（包括子对象）将自动使用默认值：

SELECT *
FROM people
FORMAT PrettyJSONEachRow


{
    "id": "1",
    "name": "Clicky McCliickHouse",
    "username": "Clicky",
    "email": "clicky@clickhouse.com",
    "address": [
        {
            "city": "Wisokyburgh",
            "geo": {
                "lat": 0,
                "lng": 0
            },
            "street": "Victor Plains",
            "suite": "",
            "zipcode": "90566-7771"
        }
    ],
    "phone_numbers": [],
    "website": "clickhouse.com",
    "company": {
        "catchPhrase": "",
        "name": "ClickHouse"
    },
    "dob": "2007-03-31"
}


1 row in set. Elapsed: 0.001 sec.

区分空值和 NULL
如果需要区分值为空与未提供的情况，可以使用 Nullable 类型。不过，除非绝对必要，否则建议避免使用此类型，因为它会对存储和查询性能产生负面影响。

应对 JSON 数据中的新增列

对于静态 JSON 键，采用结构化方法是最简单的选择。当新键可以提前规划，且架构允许调整时，这种方法同样适用。

需要注意，ClickHouse 默认会忽略 JSON 数据中存在但架构中未定义的键。例如，以下 JSON 数据新增了 nickname 键：

{
  "id": 1,
  "name": "Clicky McCliickHouse",
  "nickname": "Clicky",
  "username": "Clicky",
  "email": "clicky@clickhouse.com",
  "address": [
    {
      "street": "Victor Plains",
      "suite": "Suite 879",
      "city": "Wisokyburgh",
      "zipcode": "90566-7771",
      "geo": {
        "lat": -43.9509,
        "lng": -34.4618
      }
    }
  ],
  "phone_numbers": ["010-692-6593", "020-192-3333"],
  "website": "clickhouse.com",
  "company": {
    "name": "ClickHouse",
    "catchPhrase": "The real-time data warehouse for analytics"
  },
  "dob": "2007-03-31"
}

该数据可以成功插入，但 nickname 键会被忽略。

INSERT INTO people FORMAT JSONEachRow
{"id":1,"name":"Clicky McCliickHouse","nickname":"Clicky","username":"Clicky","email":"clicky@clickhouse.com","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics"},"dob":"2007-03-31"}


Ok.


1 row in set. Elapsed: 0.002 sec.

如果需要支持新增列，可以通过 ALTER TABLE ADD COLUMN 命令添加新列。可以使用 DEFAULT 子句为列设置默认值。在后续插入中，若未提供该列的值，将使用默认值。对于新列创建前插入的旧数据行，也会返回默认值。如果未指定 DEFAULT 值，则使用数据类型的默认值。

例如：

-- insert initial row (nickname will be ignored)
INSERT INTO people FORMAT JSONEachRow
{"id":1,"name":"Clicky McCliickHouse","nickname":"Clicky","username":"Clicky","email":"clicky@clickhouse.com","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics"},"dob":"2007-03-31"}


-- add column
ALTER TABLE people
    (ADD COLUMN `nickname` String DEFAULT 'no_nickname')


-- insert new row (same data different id)
INSERT INTO people FORMAT JSONEachRow
{"id":2,"name":"Clicky McCliickHouse","nickname":"Clicky","username":"Clicky","email":"clicky@clickhouse.com","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics"},"dob":"2007-03-31"}


-- select 2 rows
SELECT id, nickname FROM people


┌─id─┬─nickname────┐
│  2 │ Clicky      │
│  1 │ no_nickname │
└────┴─────────────┘


2 rows in set. Elapsed: 0.001 sec.

动态对象的处理

对于动态对象，推荐以下两种方法：

1. Map(String, V) 类型

2. String 类型结合 JSON 函数

以下规则有助于选择最优方案：

如果对象高度动态，结构无法预测，且包含任意嵌套对象，建议使用 String 类型。在查询时通过 JSON 函数提取值。
如果对象存储的主要是单一类型的任意键，建议使用 Map 类型。理想情况下，唯一键的数量应少于几百个。如果对象包含一致类型的子对象，也可使用 Map 类型。一般而言，Map 类型适用于处理标签和标记，例如日志数据中的 Kubernetes pod 标签。

对象级别的处理方法
在同一架构中，可以针对不同对象采用不同方法。某些对象适合用 String 类型处理，而其他对象则可以使用 Map 类型。需要注意，一旦选择 String 类型，无需进一步的架构设计。而选择 Map 类型时，可以在键中嵌套子对象（包括以 JSON 格式表示的 String）。

使用 String 类型处理动态 JSON 数据

当 JSON 数据的结构不明确或频繁变化时，结构化处理方法可能并不适用。为追求极大的灵活性，用户可以将 JSON 数据存储为 String 类型，并根据需要使用函数动态提取字段。这种方式与将 JSON 作为结构化对象处理的方法完全相反。然而，这种灵活性也带来了显著的缺点，例如查询语法更加复杂，且性能会有所下降。

正如之前提到的人员对象示例中，由于无法保证 tags 列的结构一致，我们可以将原始行插入数据表（同时包含 company.labels，但暂时忽略），并将 Tags 列声明为 String 类型：

CREATE TABLE people
(
    `id` Int64,
    `name` String,
    `username` String,
    `email` String,
    `address` Array(Tuple(city String, geo Tuple(lat Float32, lng Float32), street String, suite String, zipcode String)),
    `phone_numbers` Array(String),
    `website` String,
    `company` Tuple(catchPhrase String, name String),
    `dob` Date,
    `tags` String
)
ENGINE = MergeTree
ORDER BY username


INSERT INTO people FORMAT JSONEachRow
{"id":1,"name":"Clicky McCliickHouse","username":"Clicky","email":"clicky@clickhouse.com","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics","labels":{"type":"database systems","founded":"2021"}},"dob":"2007-03-31","tags":{"hobby":"Databases","holidays":[{"year":2024,"location":"Azores, Portugal"}],"car":{"model":"Tesla","year":2023}}}


Ok.
1 row in set. Elapsed: 0.002 sec.

查询 tags 列时，可以看到 JSON 数据被存储为字符串格式：

SELECT tags
FROM people


┌─tags───────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ {"hobby":"Databases","holidays":[{"year":2024,"location":"Azores, Portugal"}],"car":{"model":"Tesla","year":2023}} │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘


1 row in set. Elapsed: 0.001 sec.

通过 JSONExtract 函数，可以从 JSON 数据中提取值。例如：

SELECT JSONExtractString(tags, 'holidays') as holidays FROM people


┌─holidays──────────────────────────────────────┐
│ [{"year":2024,"location":"Azores, Portugal"}] │
└───────────────────────────────────────────────┘


1 row in set. Elapsed: 0.002 sec.

需要注意，函数需要同时指定 String 列的名称和 JSON 路径来提取值。对于嵌套路径，需要嵌套函数，例如 JSONExtractUInt(JSONExtractString(tags, 'car'), 'year') 用于提取 tags.car.year。使用 JSON_QUERY 和 JSON_VALUE 函数可以简化嵌套路径的提取过程。

在 arxiv 数据集中，假设我们将 body 列的所有内容存储为 String 类型，这是一种极端处理方式。

CREATE TABLE arxiv (
  body String
)
ENGINE = MergeTree ORDER BY ()

插入此类架构时，需要采用 JSONAsString 格式：

INSERT INTO arxiv SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/arxiv/arxiv.json.gz', 'JSONAsString')


0 rows in set. Elapsed: 25.186 sec. Processed 2.52 million rows, 1.38 GB (99.89 thousand rows/s., 54.79 MB/s.)

如果希望统计每年发布的论文数量，可以对比以下基于结构化架构和仅使用 String 类型的查询方法：

-- using structured schema
SELECT
    toYear(parseDateTimeBestEffort(versions.created[1])) AS published_year,
    count() AS c
FROM arxiv_v2
GROUP BY published_year
ORDER BY c ASC
LIMIT 10


┌─published_year─┬─────c─┐
│           1986 │     1 │
│           1988 │     1 │
│           1989 │     6 │
│           1990 │    26 │
│           1991 │   353 │
│           1992 │  3190 │
│           1993 │  6729 │
│           1994 │ 10078 │
│           1995 │ 13006 │
│           1996 │ 15872 │
└────────────────┴───────┘


10 rows in set. Elapsed: 0.264 sec. Processed 2.31 million rows, 153.57 MB (8.75 million rows/s., 582.58 MB/s.)


-- using unstructured String


SELECT
    toYear(parseDateTimeBestEffort(JSON_VALUE(body, '$.versions[0].created'))) AS published_year,
    count() AS c
FROM arxiv
GROUP BY published_year
ORDER BY published_year ASC
LIMIT 10


┌─published_year─┬─────c─┐
│           1986 │     1 │
│           1988 │     1 │
│           1989 │     6 │
│           1990 │    26 │
│           1991 │   353 │
│           1992 │  3190 │
│           1993 │  6729 │
│           1994 │ 10078 │
│           1995 │ 13006 │
│           1996 │ 15872 │
└────────────────┴───────┘


10 rows in set. Elapsed: 1.281 sec. Processed 2.49 million rows, 4.22 GB (1.94 million rows/s., 3.29 GB/s.)
Peak memory usage: 205.98 MiB.

在这里，通过 xpath 表达式 JSON_VALUE(body, '$.versions[0].created') 提取 JSON 中的字段值。

与显式类型转换和索引相比，String 函数的性能明显较差（慢 10 倍以上）。上述查询需要全表扫描并逐行处理数据。尽管对于小型数据集，这种查询仍能保持较高速度，但在大规模数据集上性能会显著下降。

这种方法虽然灵活，但需付出显著的性能和语法复杂度的代价。建议仅在处理架构中高度动态的对象时使用。

使用 simpleJSON 函数解析 JSON 数据

上述示例中使用了 JSON* 系列函数。这些函数利用 simdjson 提供的完整 JSON 解析功能，能够精确解析并区分嵌套层级中的同名字段。它们还支持处理语法正确但格式不规范的 JSON，例如字段之间包含多余空格的情况。

对于追求更高性能的场景，可以选择使用更严格的 simpleJSON* 函数。这些函数通过对 JSON 结构和格式的严格假设，提升了处理速度。其主要要求包括：

字段名必须为常量。
字段名的编码必须保持一致。例如，simpleJSONHas('{"abc":"def"}', 'abc') = 1，而 visitParamHas('{"\\u0061\\u0062\\u0063":"def"}', 'abc') = 0。
字段名在所有嵌套结构中必须唯一，且不区分嵌套层级。如果存在多个匹配字段，优先使用第一个字段。
除字符串字面值外，不允许包含特殊字符，例如空格。以下 JSON 无法解析：

{"@timestamp": 893964617, "clientip": "40.135.0.0", "request": {"method": "GET",
"path": "/images/hm_bg.jpg", "version": "HTTP/1.0"}, "status": 200, "size": 24736}

但以下内容可正确解析：

{"@timestamp":893964617,"clientip":"40.135.0.0","request":{"method":"GET",
"path":"/images/hm_bg.jpg","version":"HTTP/1.0"},"status":200,"size":24736}

当性能要求较高且 JSON 满足上述条件时，可考虑使用 simpleJSON* 函数。以下是基于 simpleJSONExtractString 的查询示例：

SELECT
    toYear(parseDateTimeBestEffort(simpleJSONExtractString(simpleJSONExtractRaw(body, 'versions'), 'created'))) AS published_year,
    count() AS c
FROM arxiv
GROUP BY published_year
ORDER BY published_year ASC
LIMIT 10


┌─published_year─┬─────c─┐
│           1986 │     1 │
│           1988 │     1 │
│           1989 │     6 │
│           1990 │    26 │
│           1991 │   353 │
│           1992 │  3190 │
│           1993 │  6729 │
│           1994 │ 10078 │
│           1995 │ 13006 │
│           1996 │ 15872 │
└────────────────┴───────┘


10 rows in set. Elapsed: 0.964 sec. Processed 2.48 million rows, 4.21 GB (2.58 million rows/s., 4.36 GB/s.)
Peak memory usage: 211.49 MiB.

该示例提取了 created 键的值，利用仅需第一个值作为发布时间的特点，实现性能优化。

使用 Map

如果对象用于存储单一类型的任意键，建议使用 Map 类型。我们通常推荐在处理标签和标记（如日志数据中的 Kubernetes pod 标签）时使用 Map 类型。尽管 Map 是一种简便的嵌套结构表示方式，但也存在显著限制：

所有字段的值必须为相同的类型。
子列的访问需要特殊的 Map 语法，因为字段本身不作为独立列存在，整个对象视为一个列。
访问子列时会加载整个 Map 值，这可能对大型 Map 导致显著的性能影响。

字符串键
在建模对象为 Map 时，使用 String 类型的键表示 JSON 的字段名。因此，Map 的格式始终为 Map(String, T)，其中 T 为值的数据类型。

基本值

Map 类型的一个典型应用场景是对象的值类型一致，通常选择 String 作为值类型 T。

当对象的所有值为同一基本类型时，可使用 Map 的最简单形式。例如，对于之前人员 JSON 中的 company.labels 对象，仅需要类型为 String 的键值对。因此可以定义为 Map(String, String)：

CREATE TABLE people
(
    `id` Int64,
    `name` String,
    `username` String,
    `email` String,
    `address` Array(Tuple(city String, geo Tuple(lat Float32, lng Float32), street String, suite String, zipcode String)),
    `phone_numbers` Array(String),
    `website` String,
    `company` Tuple(catchPhrase String, name String, labels Map(String,String)),
    `dob` Date,
    `tags` String
)
ENGINE = MergeTree
ORDER BY username

我们可以插入完整的 JSON 数据：

INSERT INTO people FORMAT JSONEachRow
{"id":1,"name":"Clicky McCliickHouse","username":"Clicky","email":"clicky@clickhouse.com","address":[{"street":"Victor Plains","suite":"Suite 879","city":"Wisokyburgh","zipcode":"90566-7771","geo":{"lat":-43.9509,"lng":-34.4618}}],"phone_numbers":["010-692-6593","020-192-3333"],"website":"clickhouse.com","company":{"name":"ClickHouse","catchPhrase":"The real-time data warehouse for analytics","labels":{"type":"database systems","founded":"2021"}},"dob":"2007-03-31","tags":{"hobby":"Databases","holidays":[{"year":2024,"location":"Azores, Portugal"}],"car":{"model":"Tesla","year":2023}}}


Ok.


1 row in set. Elapsed: 0.002 sec.

在请求中查询这些字段时，需要使用 Map 特定的语法，例如：

SELECT company.labels FROM people


┌─company.labels───────────────────────────────┐
│ {'type':'database systems','founded':'2021'} │
└──────────────────────────────────────────────┘


1 row in set. Elapsed: 0.001 sec.


SELECT company.labels['type'] AS type FROM people


┌─type─────────────┐
│ database systems │
└──────────────────┘


1 row in set. Elapsed: 0.001 sec.

提供完整的 Map 函数集支持此类查询，具体功能详见相关文档。如果数据类型不一致，可通过函数进行类型转换。

对象值

对于包含子对象且子对象类型一致的对象，也可以使用 Map 类型。

例如，假设人员对象中的 tags 字段需要一致的结构，其中每个标签的子对象包括 name 和 time 字段。一个示例如下：

{
  "id": 1,
  "name": "Clicky McCliickHouse",
  "username": "Clicky",
  "email": "clicky@clickhouse.com",
  "tags": {
    "hobby": {
      "name": "Diving",
      "time": "2024-07-11 14:18:01"
    },
    "car": {
      "name": "Tesla",
      "time": "2024-07-11 15:18:23"
    }
  }
}

这一问题可以通过将其建模为 Map(String, Tuple(name String, time DateTime)) 来解决，示例如下：

CREATE TABLE people
(
    `id` Int64,
    `name` String,
    `username` String,
    `email` String,
    `tags` Map(String, Tuple(name String, time DateTime))
)
ENGINE = MergeTree
ORDER BY username


INSERT INTO people FORMAT JSONEachRow
{"id":1,"name":"Clicky McCliickHouse","username":"Clicky","email":"clicky@clickhouse.com","tags":{"hobby":{"name":"Diving","time":"2024-07-11 14:18:01"},"car":{"name":"Tesla","time":"2024-07-11 15:18:23"}}}


Ok.


1 row in set. Elapsed: 0.002 sec.


SELECT tags['hobby'] AS hobby
FROM people
FORMAT JSONEachRow


{"hobby":{"name":"Diving","time":"2024-07-11 14:18:01"}}


1 row in set. Elapsed: 0.001 sec.

在这种情况下，使用 Map 类型较为罕见。建议通过重新建模数据，避免动态键名嵌套子对象。例如，可以将上述结构转换为 Array(Tuple(key String, name String, time DateTime))，以获得更高的灵活性和性能。

{
  "id": 1,
  "name": "Clicky McCliickHouse",
  "username": "Clicky",
  "email": "clicky@clickhouse.com",
  "tags": [
    {
      "key": "hobby",
      "name": "Diving",
      "time": "2024-07-11 14:18:01"
    },
    {
      "key": "car",
      "name": "Tesla",
      "time": "2024-07-11 15:18:23"
    }
  ]
}

/END/

注册ClickHouse中国社区大使，领取认证考试券

ClickHouse社区大使计划正式启动，首批过审贡献者享原厂认证考试券！

试用阿里云 ClickHouse企业版

轻松节省30%云资源成本？阿里云数据库ClickHouse 云原生架构全新升级，首次购买ClickHouse企业版计算和存储资源组合，首月消费不超过99.58元（包含最大16CCU+450G OSS用量）了解详情：https://t.aliyun.com/Kz5Z0q9G

征稿启示

面向社区长期正文，文章内容包括但不限于关于 ClickHouse 的技术研究、项目实践和创新做法等。建议行文风格干货输出&图文并茂。质量合格的文章将会发布在本公众号，优秀者也有机会推荐到 ClickHouse 官网。请将文章稿件的 WORD 版本发邮件至：Tracy.Wang@clickhouse.com

文章转载自ClickHouseInc，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

ClickHouse 官方文档：处理 JSON 数据 （1/2）

评论

ClickHouse 官方文档：处理 JSON 数据（1/2）