JSON

更新时间：2024-11-25

加载 JSON

在本节中，我们假设 JSON 数据为NDJSON（换行符分隔的JSON）格式，在ClickHouse中称为JSONEachRow。这是加载 JSON 的首选格式，因为它简洁且能有效利用空间，但其他格式也支持输入和输出。

考虑以下JSON示例，它表示来自Python PyPI 数据集的一行：

{
  "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)

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.

请注意，我们不需要指定文件格式。相反，我们使用glob模式来读取*.json.gz存储桶中的所有文件。ClickHouse 会自动JSONEachRow根据文件扩展名和内容推断格式为 (ndjson)。如果 ClickHouse 无法检测到格式，可以通过参数函数手动指定格式。

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

要加载这些文件中的行，可以使用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"}

JSON架构推断

ClickHouse 可以自动确定 JSON 数据的结构。这可用于直接查询 JSON 数据（例如使用磁盘clickhouse-local或 S3 存储桶），和/或在将数据加载到 ClickHouse 之前自动创建模式。

何时使用类型推断

一致的结构- 您要从中推断类型的数据包含您感兴趣的所有列。类型推断后添加的附加列的数据将被忽略并且无法查询。
一致的类型- 特定列的数据类型需要兼容。

检测类型

之前的示例使用了 NDJSON 格式的Python PyPI 数据集的简单版本。在本节中，我们将探索具有嵌套结构的更复杂的数据集 -包含 250 万篇学术论文的arXiv数据集。此数据集中的每一行都以 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，避免手动构建它并加速开发过程。

使用命令中的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))用于嵌套数组。

查询 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

2.为了确认表模式，我们使用以下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) 控制。

使用函数创建表

使用函数创建表，如下所示：

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.

2.加载后，我们可以查询数据，可选择使用格式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 架构

虽然可以使用架构推断来为JSON数据建立初始架构并就地查询 JSON 数据文件（例如在 S3 中），但用户应该致力于为其数据建立优化的版本化架构。我们在下面讨论用于建模 JSON 结构的选项。

静态 JSON 与动态 JSON

定义 JSON 架构的主要任务是确定每个键值的适当类型。我们建议用户对 JSON 层次结构中的每个键递归应用以下规则，以确定每个键的适当类型。

原始类型-如果键的值是原始类型，无论它是子对象的一部分还是位于根上，请确保根据通用架构设计最佳实践和类型优化规则选择其类型。原始数组（例如，phone_numbers如下所示）可以建模为Array(<type>)。Array(String)。
静态与动态- 如果键的值是一个复杂对象，即一个对象或一个对象数组，则确定它是否会发生变化。很少有新键的对象，可以通过模式更改来预测和处理新键的添加和处理新键的添加 ALTER TABLE ADD COLUMN，可以被视为静态的。这包括在某些 JSON 文档中可能只提供部分键的对象。经常添加新键和/或不可预测的新键的对象应被视为动态的。

重要提示：上述规则应递归应用。如果确定某个键的值是动态的，则无需进一步评估，并可遵循处理动态对象中的指导原则。如果对象是静态的，则继续评估子键，直到遇到键值是原始键或动态键为止。

为了说明这些规则，使用以下代表一个 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
    }
  }
}

4.应用这些规则：

*根键name、username、email、website可表示为类型String。列phone_numbers是类型的数组原语Array(String)，分别具有dob和id类型Date和UInt32。

不会向address对象添加新键（只有新地址对象），因此可以将其视为静态的。如果我们递归，则除之外的所有子列都可以被视为基元（和类型String）geo。这也是一个具有两列的静态结构Float32，lat和lon。
该tags列是动态的。我们假设可以向任何类型和结构的此对象添加新的任意标签。
该company对象是静态的，并且始终最多包含指定的 3 个键。子键name和catchPhrase的类型为String。键labels是动态的。我们假设可以向此对象添加新的任意标签。值始终是字符串类型的键值对。

处理静态对象

建议使用命名元组来处理静态对象Tuple。可以使用元组数组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)带有嵌套的Tuple来表示geo列。

3.可以将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"}

4.在上面的例子中，有最少的数据。但如下所示，可以通过句点分隔的名称来查询元组字段：

SELECT
    address.street,
    company.name
FROM people

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

5.请注意address.street列是如何作为来返回的Array。要按位置查询数组内的特定对象，应在列名后指定数组偏移量。例如，要从第一个地址访问street：

SELECT address.street[1] AS street
FROM people

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

1 row in set. Elapsed: 0.001 sec.

元组的主要缺点是子列不能用于排序键。因此，以下操作将失败：

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 有效负载中的所有列。如果未提供，则将使用默认值。

考虑之前的表和下面的people稀疏 JSON，缺少键suite、geo和phone_numbers catchPhrase:

{
  "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"
}

2.可以从下面看到这一行可以成功插入：

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.

3.查询此单行，可以看到省略的列（包括子对象）使用了默认值：

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.

区分empty和null
如果用户需要区分值为空和未提供，则可以使用Nullable类型。除非绝对必要，否则应避免这样做，因为这会对此类列的存储和查询性能产生负面影响。

处理新列

虽然当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"
}

忽略键即可成功插入此JSONnickname：

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.

处理动态对象

有两种推荐的方法来处理动态对象：

Map(String,V)类型
带有 JSON 函数的字符串

可以应用以下规则来确定最合适的。

如果对象高度动态，没有可预测的结构并且包含任意嵌套对象，则用户应使用该String类型。可以使用 JSON 函数在查询时提取值，如下所示。
如果对象用于存储任意键，且大多为一种类型，请考虑使用该Map类型。理想情况下，唯一键的数量不应超过几百个。Map对于具有子对象的对象，也可以考虑使用该类型，前提是后者的类型一致。通常，我们建议Map将该类型用于标签和标记，例如日志数据中的 Kubernetes pod 标签。

应用对象级方法
不同的技术可以应用于同一架构中的不同对象。有些对象可以用String和其他来最好地解决Map。请注意，一旦String使用类型，就不需要做出进一步的架构决策。相反，可以将子对象嵌套在键中Map，如下所示 - 包括String表示JSON。

使用字符串

对于使用动态 JSON 的用户来说，使用上述结构化方法处理数据通常不可行，因为动态 JSON 可能会发生变化，或者其架构不太为人所理解。为了获得绝对的灵活性，用户可以简单地将 JSON 存储为Strings，然后根据需要使用函数提取字段。这与将 JSON 作为结构化对象处理完全相反。这种灵活性会产生成本，并带来重大缺点 - 主要是查询语法复杂性增加以及性能下降。

如前所述，对于原始person对象，我们无法确保列的结构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列的引用tags，也需要提取 JSON 中的路径。嵌套路径需要嵌套函数，例如JSONExtractUInt(JSONExtractString(tags, 'car'), 'year')提取列。可以通过函数JSON_QUERY和JSON_VALUEtags.car.year简化嵌套路径的提取。

考虑数据集中的极端情况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.)

假设我们希望统计按年份发布的论文数量。将查询与结构化版本的架构进行对比，并与仅使用字符串的情况进行比较：

-- 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 JSON_VALUE(body, '$.versions[0].created')。

字符串函数比使用索引的显式类型转换慢得多（> 10 倍）。上述查询始终需要全表扫描并处理每一行。虽然这些查询在像这样的小型数据集上仍然很快，但在较大的数据集上性能会下降。

这种方法的灵活性显然是以性能和语法为代价的，因此它只应该用于模式中高度动态的对象。

简单的JSON的函数

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

有一组更快、更严格的函数可用。这些simpleJSON*函数主要通过对 JSON 的结构和格式做出严格的假设来提供潜在的卓越性能。具体来说：

字段名称必须是常量
字段名称的一致编码，例如simpleJSONHas('{"abc":"def"}', 'abc') = 1，但是visitParamHas('{"\\u0061\\u0062\\u0063":"def"}', 'abc') = 0
字段名称在所有嵌套结构中都是唯一的。嵌套级别之间没有区别，匹配也是无差别的。如果有多个匹配字段，则使用第一个匹配的字段。
字符串文字之外不得有特殊字符。这包括空格。以下内容无效，无法解析。

{"@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}

simpleJSON*在某些情况下，如果性能至关重要并且您的 JSON 满足上述要求，这些可能是合适的。下面显示了使用函数重写的早期查询的示例：

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.

上述代码使用simpleJSONExtractString来提取created密钥，利用了我们只需要发布日期的第一个值这一事实。在这种情况下，函数的限制simpleJSON*对于性能的提升是可以接受的。

使用 Map

如果对象用于存储主要为一种类型的任意键，请考虑使用该Map类型。理想情况下，唯一键的数量不应超过几百个。我们建议Map将该类型用于标签和标记，例如日志数据中的 Kubernetes pod 标签。虽然这是一种表示嵌套结构的简单方法，Map但它有一些明显的局限性：

所有字段必须属于同一类型。
访问子列需要特殊的映射语法，因为字段不作为列存在；整个对象是一列。
访问子列会加载整个Map值，即所有同级值及其各自的值。对于较大的地图，这可能会导致严重的性能损失。

字符串键
当将对象建模为Map时，String使用 key 来存储JSON 键名称。因此映射将始终为Map(String, T)，其中T取决于数据。

原始值

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

考虑我们之前的Person 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.

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

`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类型也可以考虑用于具有子对象的对象，前提是后者的类型具有一致性。

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

{
  "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.

在这种情况下，映射的应用通常很少见，并且建议对数据进行重构，以使动态键名不包含子对象。例如，可以将上述内容重构如下，以允许使用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"
    }
  ]
}

导出 JSON

几乎任何用于导入的 JSON 格式都可以用于导出。最流行的是JSONEachRow：

SELECT * FROM sometable FORMAT JSONEachRow

{"path":"Bob_Dolman","month":"2016-11-01","hits":245}
{"path":"1-krona","month":"2017-01-01","hits":4}
{"path":"Ahmadabad-e_Kalij-e_Sofla","month":"2017-01-01","hits":3}

或者可以JSONCompactEachRow通过跳过列名来节省磁盘空间：

SELECT * FROM sometable FORMAT JSONCompactEachRow

["Bob_Dolman", "2016-11-01", 245]
["1-krona", "2017-01-01", 4]
["Ahmadabad-e_Kalij-e_Sofla", "2017-01-01", 3]

将数据类型覆盖为字符串

ClickHouse 尊重数据类型并将根据标准导出 JSON。但如果我们需要将所有值编码为字符串，则可以使用JSONStringsEachRow格式：

SELECT * FROM sometable FORMAT JSONStringsEachRow

{"path":"Bob_Dolman","month":"2016-11-01","hits":"245"}
{"path":"1-krona","month":"2017-01-01","hits":"4"}
{"path":"Ahmadabad-e_Kalij-e_Sofla","month":"2017-01-01","hits":"3"}

现在，hits数字列被编码为字符串。所有 JSON* 格式都支持导出为字符串，只需探索JSONStrings\*并JSONCompactStrings\*格式化：

SELECT * FROM sometable FORMAT JSONCompactStringsEachRow

["Bob_Dolman", "2016-11-01", "245"]
["1-krona", "2017-01-01", "4"]
["Ahmadabad-e_Kalij-e_Sofla", "2017-01-01", "3"]

导出元数据和数据

应用程序中流行的通用JSON格式不仅会导出结果数据，还会导出列类型和查询统计信息：

SELECT * FROM sometable FORMAT JSON

{
    "meta":
    [
        {
            "name": "path",
            "type": "String"
        },
        …
    ],

    "data":
    [
        {
            "path": "Bob_Dolman",
            "month": "2016-11-01",
            "hits": 245
        },
        …
    ],

    "rows": 3,

    "statistics":
    {
        "elapsed": 0.000497457,
        "rows_read": 3,
        "bytes_read": 87
    }
}

JSONCompact格式将打印相同的元数据，但对数据本身使用压缩形式：

SELECT * FROM sometable FORMAT JSONCompact

{
    "meta":
    [
        {
            "name": "path",
            "type": "String"
        },
        …
    ],

    "data":
    [
        ["Bob_Dolman", "2016-11-01", 245],
        ["1-krona", "2017-01-01", 4],
        ["Ahmadabad-e_Kalij-e_Sofla", "2017-01-01", 3]
    ],

    "rows": 3,

    "statistics":
    {
        "elapsed": 0.00074981,
        "rows_read": 3,
        "bytes_read": 87
    }
}

考虑JSONStrings或JSONCompactStrings变体将所有值编码为字符串。

导出JSON数据和结构的压缩方法

获取数据及其结构的更有效的方法是使用JSONCompactEachRowWithNamesAndTypes格式：

SELECT * FROM sometable FORMAT JSONCompactEachRowWithNamesAndTypes

["path", "month", "hits"]
["String", "Date", "UInt32"]
["Bob_Dolman", "2016-11-01", 245]
["1-krona", "2017-01-01", 4]
["Ahmadabad-e_Kalij-e_Sofla", "2017-01-01", 3]

这将使用紧凑的 JSON 格式，前面有两个标题行，其中包含列名和类型。然后可以使用此格式将数据导入另一个 ClickHouse 实例（或其他应用程序）。

将JSON导出到文件

要将导出的 JSON 数据保存到文件，可以使用INTO OUTFILE子句：

SELECT * FROM sometable INTO OUTFILE 'out.json' FORMAT JSONEachRow

36838935 rows in set. Elapsed: 2.220 sec. Processed 36.84 million rows, 1.27 GB (16.60 million rows/s., 572.47 MB/s.)

ClickHouse 仅用了 2 秒就将近 3700 万条记录导出到 JSON 文件。我们还可以使用COMPRESSION子句导出以动态启用压缩：

SELECT * FROM sometable INTO OUTFILE 'out.json.gz' FORMAT JSONEachRow

36838935 rows in set. Elapsed: 22.680 sec. Processed 36.84 million rows, 1.27 GB (1.62 million rows/s., 56.02 MB/s.)

需要更多时间来完成，但会生成更小的压缩文件：

2.2G    out.json
576M    out.json.gz

处理其他JSON格式

前面加载JSON数据的示例假设使用JSONEachRow(ndjson)。我们在下面提供了加载其他常见格式的JSON的示例。

JSON 对象数组

JSON 数据最流行的形式之一是在 JSON 数组中拥有 JSON 对象列表，如下例所示：

> cat list.json
[
  {
    "path": "Akiba_Hebrew_Academy",
    "month": "2017-08-01",
    "hits": 241
  },
  {
    "path": "Aegithina_tiphia",
    "month": "2018-02-01",
    "hits": 34
  },
  ...
]

2.为这种数据创建一个表：

CREATE TABLE sometable
(
    `path` String,
    `month` Date,
    `hits` UInt32
)
ENGINE = MergeTree
ORDER BY tuple(month, path)

要导入 JSON 对象列表，我们可以使用一种格式（从list.jsonJSONEachRow文件插入数据）：

INSERT INTO sometable
FROM INFILE 'list.json'
FORMAT JSONEachRow

使用FROM INFILE子句从本地文件加载数据，并且可以看到导入成功：

SELECT *
FROM sometable

┌─path──────────────────────┬──────month─┬─hits─┐
│ 1971-72_Utah_Stars_season │ 2016-10-01 │    1 │
│ Akiba_Hebrew_Academy      │ 2017-08-01 │  241 │
│ Aegithina_tiphia          │ 2018-02-01 │   34 │
└───────────────────────────┴────────────┴──────┘

处理NDJSON（行分隔的JSON）

许多应用程序可以以 JSON 格式记录数据，以便每个日志行都是一个单独的 JSON 对象，就像在此文件中一样：

cat object-per-line.json

{"path":"1-krona","month":"2017-01-01","hits":4}
{"path":"Ahmadabad-e_Kalij-e_Sofla","month":"2017-01-01","hits":3}
{"path":"Bob_Dolman","month":"2016-11-01","hits":245}

相同的JSONEachRow格式可以处理这样的文件：

INSERT INTO sometable FROM INFILE 'object-per-line.json' FORMAT JSONEachRow;
SELECT * FROM sometable;

┌─path──────────────────────┬──────month─┬─hits─┐
│ Bob_Dolman                │ 2016-11-01 │  245 │
│ 1-krona                   │ 2017-01-01 │    4 │
│ Ahmadabad-e_Kalij-e_Sofla │ 2017-01-01 │    3 │
└───────────────────────────┴────────────┴──────┘

JSON对象键

在某些情况下，JSON对象列表可以编码为对象属性而不是数组元素：

cat objects.json

{
  "a": {
    "path":"April_25,_2017",
    "month":"2018-01-01",
    "hits":2
  },
  "b": {
    "path":"Akahori_Station",
    "month":"2016-06-01",
    "hits":11
  },
  ...
}

ClickHouse 可以使用以下格式从此类数据中加载数据JSONObjectEachRow：

INSERT INTO sometable FROM INFILE 'objects.json' FORMAT JSONObjectEachRow;
SELECT * FROM sometable;

┌─path────────────┬──────month─┬─hits─┐
│ Abducens_palsy  │ 2016-05-01 │   28 │
│ Akahori_Station │ 2016-06-01 │   11 │
│ April_25,_2017  │ 2018-01-01 │    2 │
└─────────────────┴────────────┴──────┘

指定父对象键值

假设还想将父对象键中的值保存到表中。在这种情况下，可以使用以下选项来定义要保存键值的列的名称：

SET format_json_object_each_row_column_for_object_name = 'id'

现在，可以使用函数检查要从原始JSON文件中加载哪些数据file()：

SELECT * FROM file('objects.json', JSONObjectEachRow)

┌─id─┬─path────────────┬──────month─┬─hits─┐
│ a  │ April_25,_2017  │ 2018-01-01 │    2 │
│ b  │ Akahori_Station │ 2016-06-01 │   11 │
│ c  │ Abducens_palsy  │ 2016-05-01 │   28 │
└────┴─────────────────┴────────────┴──────┘

请注意该id列是如何正确地填充键值的。

JSON数组

有时，为了节省空间，JSON文件会以数组而不是对象的形式进行编码。在这种情况下，我们处理JSON 数组列表：

cat arrays.json

["Akiba_Hebrew_Academy", "2017-08-01", 241],
["Aegithina_tiphia", "2018-02-01", 34],
["1971-72_Utah_Stars_season", "2016-10-01", 1]

在这种情况下，ClickHouse 将加载此数据并根据数组中的顺序将每个值归属于相应的列。我们JSONCompactEachRow为此使用格式：

SELECT * FROM sometable

┌─c1────────────────────────┬─────────c2─┬──c3─┐
│ Akiba_Hebrew_Academy      │ 2017-08-01 │ 241 │
│ Aegithina_tiphia          │ 2018-02-01 │  34 │
│ 1971-72_Utah_Stars_season │ 2016-10-01 │   1 │
└───────────────────────────┴────────────┴─────┘

从JSON数组导入单个列

在某些情况下，数据可以按列而不是按行进行编码。在这种情况下，父 JSON 对象包含具有值的列。查看以下文件：

cat columns.json

{
  "path": ["2007_Copa_America", "Car_dealerships_in_the_USA", "Dihydromyricetin_reductase"],
  "month": ["2016-07-01", "2015-07-01", "2015-07-01"],
  "hits": [178, 11, 1]
}

ClickHouse使用JSONColumns以下格式来解析数据：

SELECT * FROM file('columns.json', JSONColumns)

┌─path───────────────────────┬──────month─┬─hits─┐
│ 2007_Copa_America          │ 2016-07-01 │  178 │
│ Car_dealerships_in_the_USA │ 2015-07-01 │   11 │
│ Dihydromyricetin_reductase │ 2015-07-01 │    1 │
└────────────────────────────┴────────────┴──────┘

当处理列数组而不是使用格式的对象时，也支持更紧凑的格式JSONCompactColumns：

SELECT * FROM file('columns-array.json', JSONCompactColumns)

┌─c1──────────────┬─────────c2─┬─c3─┐
│ Heidenrod       │ 2017-01-01 │ 10 │
│ Arthur_Henrique │ 2016-11-01 │ 12 │
│ Alan_Ebnother   │ 2015-11-01 │ 66 │
└─────────────────┴────────────┴────┘

保存JSON对象

在某些情况下，您可能希望将JSON对象保存到单个String（或 JSON）列中，而不是对其进行解析。这在处理不同结构的JSON对象列表时很有用。让我们以这个文件为例，其中父列表中有多个不同JSON对象：

cat custom.json

[
  {"name": "Joe", "age": 99, "type": "person"},
  {"url": "/my.post.MD", "hits": 1263, "type": "post"},
  {"message": "Warning on disk usage", "type": "log"}
]

2.我们希望将原始 JSON 对象保存到下表中：

CREATE TABLE events
(
    `data` String
)
ENGINE = MergeTree
ORDER BY ()

现在可以使用格式将文件中的数据加载到该表中JSONAsString以保留JSON对象而不是解析它们：

INSERT INTO events (data)
FROM INFILE 'custom.json'
FORMAT JSONAsString

可以使用JSON函数来查询已保存的对象：

SELECT
    JSONExtractString(data, 'type') AS type,
    data
FROM events

┌─type───┬─data─────────────────────────────────────────────────┐
│ person │ {"name": "Joe", "age": 99, "type": "person"}         │
│ post   │ {"url": "/my.post.MD", "hits": 1263, "type": "post"} │
│ log    │ {"message": "Warning on disk usage", "type": "log"}  │
└────────┴──────────────────────────────────────────────────────┘

JSONAsString请注意，如果我们有JSON对象每行格式的文件（通常与JSONEachRow格式一起使用），那么它可以完美地工作。

嵌套对象的架构

在处理嵌套 JSON 对象的情况下，我们可以另外定义模式并使用复杂类型（Array、Object Data Type或Tuple）来加载数据：

SELECT *
FROM file('list-nested.json', JSONEachRow, 'page Tuple(path String, title String, owner_id UInt16), month Date, hits UInt32')
LIMIT 1

┌─page───────────────────────────────────────────────┬──────month─┬─hits─┐
│ ('Akiba_Hebrew_Academy','Akiba Hebrew Academy',12) │ 2017-08-01 │  241 │
└────────────────────────────────────────────────────┴────────────┴──────┘

访问嵌套的JSON对象

可以通过启用以下设置选项来引用嵌套的JSON键：

SET input_format_import_nested_json = 1

这能够使用点符号引用嵌套的JSON对象键（记住用反引号符号包装它们才能起作用）：

SELECT *
FROM file('list-nested.json', JSONEachRow, '`page.owner_id` UInt32, `page.title` String, month Date, hits UInt32')
LIMIT 1

┌─page.owner_id─┬─page.title───────────┬──────month─┬─hits─┐
│            12 │ Akiba Hebrew Academy │ 2017-08-01 │  241 │
└───────────────┴──────────────────────┴────────────┴──────┘

这样，可以展平嵌套的JSON对象或使用一些嵌套的值将它们保存为单独的列。

跳过未知列

默认情况下，ClickHouse在导入JSON数据时会忽略未知列。我们来尝试将原始文件导入到没有该列的表中month：

CREATE TABLE shorttable
(
    `path` String,
    `hits` UInt32
)
ENGINE = MergeTree
ORDER BY path

这样仍然可以将原始的包含3列的JSON数据插入到该表中：

INSERT INTO shorttable FROM INFILE 'list.json' FORMAT JSONEachRow;
SELECT * FROM shorttable

┌─path──────────────────────┬─hits─┐
│ 1971-72_Utah_Stars_season │    1 │
│ Aegithina_tiphia          │   34 │
│ Akiba_Hebrew_Academy      │  241 │
└───────────────────────────┴──────┘

ClickHouse将在导入时忽略未知列。可以使用input_format_skip_unknown_fields设置选项禁用此功能：

SET input_format_skip_unknown_fields = 0;
INSERT INTO shorttable FROM INFILE 'list.json' FORMAT JSONEachRow;

Ok.
Exception on client:
Code: 117. DB::Exception: Unknown field found while parsing JSONEachRow format: month: (in file/uri /data/clickhouse/user_files/list.json): (at row 1)

如果 JSON 和表列结构不一致，ClickHouse将会抛出异常。

BSON

ClickHouse 允许导出和导入BSON编码文件的数据。一些DBMS使用此格式，例如MongoDB数据库。

要导入BSON数据，使用BSONEachRow格式。从这个BSON文件导入数据：

SELECT * FROM file('data.bson', BSONEachRow)

┌─path──────────────────────┬─month─┬─hits─┐
│ Bob_Dolman                │ 17106 │  245 │
│ 1-krona                   │ 17167 │    4 │
│ Ahmadabad-e_Kalij-e_Sofla │ 17167 │    3 │
└───────────────────────────┴───────┴──────┘

还可以使用相同的格式导出到BSON文件：

SELECT *
FROM sometable
INTO OUTFILE 'out.bson'
FORMAT BSONEachRow

此后，我们将数据导出到out.bson文件。

对JSON进行建模的其他方法

使用嵌套

Nested类型可用于对很少发生变化的静态对象进行建模，从而为Tuple和提供替代方案Array(Tuple)。我们通常建议避免对JSON使用此类型，因为它的行为通常令人困惑。主要好处Nested是子列可用于对键进行排序。

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

{
  "timestamp": 897819077,
  "clientip": "45.212.12.0",
  "request": {
    "method": "GET",
    "path": "/french/images/hm_nav_bar.gif",
    "version": "HTTP/1.0"
  },
  "status": 200,
  "size": 3305
}
``

We can declare the `request` key as `Nested`. Similar to `Tuple`, we are required to specify the sub columns.

```sql
-- default
SET flatten_nested=1
CREATE table http
(
   timestamp Int32,
   clientip     IPv4,
   request Nested(method LowCardinality(String), path String, version LowCardinality(String)),
   status       UInt16,
   size         UInt32,
) ENGINE = MergeTree() ORDER BY (status, timestamp);

flatten_nested

该设置flatten_nested控制嵌套的行为。

flatten_nested=1

值1（默认值）不支持任意嵌套级别。使用此值，最容易将嵌套数据结构视为长度相同的多个Array列。字段“方法”、“路径”和“版本”都是单独的“数组（类型）”列，它们有一个关键约束：方法、路径和版本字段的长度必须相同。如果我们使用SHOW CREATE TABLE，如下所示：

SHOW CREATE TABLE http

CREATE TABLE http
(
    `timestamp` Int32,
    `clientip` IPv4,
    `request.method` Array(LowCardinality(String)),
    `request.path` Array(String),
    `request.version` Array(LowCardinality(String)),
    `status` UInt16,
    `size` UInt32
)
ENGINE = MergeTree
ORDER BY (status, timestamp)

下面，插入到这个表中：

SET input_format_import_nested_json = 1;
INSERT INTO http
FORMAT JSONEachRow
{"timestamp":897819077,"clientip":"45.212.12.0","request":[{"method":"GET","path":"/french/images/hm_nav_bar.gif","version":"HTTP/1.0"}],"status":200,"size":3305}

这里需要注意几个要点：

我们需要使用设置input_format_import_nested_json将JSON作为嵌套结构插入。如果没有这个，我们需要将JSON展平，即

INSERT INTO http FORMAT JSONEachRow
{"timestamp":897819077,"clientip":"45.212.12.0","request":{"method":["GET"],"path":["/french/images/hm_nav_bar.gif"],"version":["HTTP/1.0"]},"status":200,"size":3305}

嵌套字段method、path和version需要作为 JSON 数组传递，即

{
  "@timestamp": 897819077,
  "clientip": "45.212.12.0",
  "request": {
    "method": [
      "GET"
    ],
    "path": [
      "/french/images/hm_nav_bar.gif"
    ],
    "version": [
      "HTTP/1.0"
    ]
  },
  "status": 200,
  "size": 3305
}

可以使用点符号来查询列：

SELECT clientip, status, size, `request.method` FROM http WHERE has(request.method, 'GET');

┌─clientip────┬─status─┬─size─┬─request.method─┐
│ 45.212.12.0 │    200 │ 3305 │ ['GET']        │
└─────────────┴────────┴──────┴────────────────┘
1 row in set. Elapsed: 0.002 sec.

Array请注意，子列的使用意味着可以利用完整的呼吸数组函数ARRAY JOIN，包括子句 - 如果您的列有多个值，则很有用。

flatten_nested=0

这允许任意级别的嵌套，并且意味着嵌套的列保留为单个Tuples 数组 - 实际上它们变得相同Array(Tuple)。

这是使用 JSON 的首选方法，通常也是最简单的方法Nested。如下所示，它仅要求所有对象都是列表。

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

CREATE TABLE http
(
    `timestamp` Int32,
    `clientip` IPv4,
    `request` Nested(method LowCardinality(String), path String, version LowCardinality(String)),
    `status` UInt16,
    `size` UInt32
)
ENGINE = MergeTree
ORDER BY (status, timestamp)

SHOW CREATE TABLE http

-- note Nested type is preserved.
CREATE TABLE default.http
(
    `timestamp` Int32,
    `clientip` IPv4,
    `request` Nested(method LowCardinality(String), path String, version LowCardinality(String)),
    `status` UInt16,
    `size` UInt32
)
ENGINE = MergeTree
ORDER BY (status, timestamp)

INSERT INTO http
FORMAT JSONEachRow
{"timestamp":897819077,"clientip":"45.212.12.0","request":[{"method":"GET","path":"/french/images/hm_nav_bar.gif","version":"HTTP/1.0"}],"status":200,"size":3305}