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

ClickHouseInc 2025-01-07

299

本文字数：15445；估计阅读时间：39分钟

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

2025-01-02

导出 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 数据

导出 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 数据导出到文件

可以使用 INTO OUTFILE 子句将 JSON 数据保存到文件中：

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 格式

在前面的示例中，我们使用 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
  },
  ...
]
复制

可以为此类数据定义一个表：

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

导入 JSON 对象列表时，可以使用 JSONEachRow 格式（从本地文件 list.json 加载数据）：

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 对象列表可能以对象属性的形式编码，而非数组元素（例如 objects.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'
复制

随后，可以通过 file() 函数检查从原始 JSON 文件加载的数据：

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 列表时，您可能希望将 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"}
]
复制

可以将原始 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 数据类型或 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
复制

即使原始 JSON 数据包含 3 列，仍可成功插入表中：

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 │
└───────────────────────────┴──────┘
复制

导入过程中，未知列会被自动忽略。若希望禁用该行为，可以设置 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 文件的数据导入与导出，这是一种被 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 文件中。

ClickHouse 中的其他 JSON 建模方法

以下是 ClickHouse 中建模 JSON 的一些替代方案。这些方法主要是为了完整性记录，但通常不推荐使用，且不适用于大多数场景。

使用 Nested

Nested 类型适用于建模静态且变化较少的对象，是 Tuple 和 Array(Tuple) 的替代方案。尽管如此，我们通常不建议在 JSON 数据中使用 Nested 类型，因为它的行为可能令人困惑。但 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 控制 Nested 的行为，有两种主要模式：

flatten_nested=1

当设置为 1（默认值）时，不支持任意级别的嵌套。此时，可将嵌套数据视为多个长度相同的 Array 列。字段 method、path 和 version 会作为独立的 Array 列，但其长度必须相同：

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}
复制

这里有几个需要注意的点：

1. 启用 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}
复制

2. 嵌套字段 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
}
复制

3. 可以使用点符号访问嵌套列：

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 函数集，包括 ARRAY JOIN 子句——当列包含多个值时，这非常实用。

flatten_nested=0

当设置为 0 时，允许任意嵌套级别。嵌套列被视为元组数组，与 Array(Tuple) 等效：

这是推荐的 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}
复制

1. 无需启用 input_format_import_nested_json 设置。

2. Nested 类型在 SHOW CREATE TABLE 中显示为 Array(Tuple)。

3. 插入时需要将嵌套对象作为数组处理：

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

示例

上述数据的更大示例可以在 s3 的公共存储桶中获取，路径为：s3://datasets-documentation/http/。

SELECT *
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/http/documents-01.ndjson.gz', 'JSONEachRow')
LIMIT 1
FORMAT PrettyJSONEachRow


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


1 row in set. Elapsed: 0.312 sec.
复制

基于 JSON 的限制和输入格式，我们通过以下查询插入此示例数据集，并将 flatten_nested 设置为 0：

以下语句将插入 1000 万行数据，可能需要几分钟执行。如有必要，可以通过 LIMIT 限制行数：

INSERT INTO http
SELECT `@timestamp` AS `timestamp`, clientip, [request], status,
size FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/http/documents-01.ndjson.gz',
'JSONEachRow');
复制

查询这些数据时，需要以数组的形式访问 request 字段。以下是我们对固定时间段内的错误和 HTTP 方法的汇总结果：

SELECT status, request.method[1] as method, count() as c
FROM http
WHERE status >= 400
  AND toDateTime(timestamp) BETWEEN '1998-01-01 00:00:00' AND '1998-06-01 00:00:00'
GROUP by method, status
ORDER BY c DESC LIMIT 5;


┌─status─┬─method─┬─────c─┐
│    404 │ GET    │ 11267 │
│    404 │ HEAD   │   276 │
│    500 │ GET    │   160 │
│    500 │ POST   │   115 │
│    400 │ GET    │    81 │
└────────┴────────┴───────┘


5 rows in set. Elapsed: 0.007 sec.
复制

使用成对数组（Pairwise Arrays）

成对数组是一种在 JSON 灵活性与性能之间的折中方法。其优点是可以在根级别动态添加新字段，但其查询语法较为复杂，且不支持嵌套结构。

以下为一个成对数组表的示例：

CREATE TABLE http_with_arrays (
   keys Array(String),
   values Array(String)
)
ENGINE = MergeTree  ORDER BY tuple();
复制

要插入数据，需要将 JSON 构造为键值对列表，并使用 JSONExtractKeysAndValues 函数完成：

SELECT
    arrayMap(x -> (x.1), JSONExtractKeysAndValues(json, 'String')) AS keys,
    arrayMap(x -> (x.2), JSONExtractKeysAndValues(json, 'String')) AS values
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/http/documents-01.ndjson.gz', 'JSONAsString')
LIMIT 1
FORMAT Vertical


Row 1:
──────
keys:   ['@timestamp','clientip','request','status','size']
values: ['893964617','40.135.0.0','{"method":"GET","path":"/images/hm_bg.jpg","version":"HTTP/1.0"}','200','24736']


1 row in set. Elapsed: 0.416 sec.
复制

请注意，request 列依然是一个嵌套结构，存储为字符串。我们可以向根级别添加新的键值，同时 JSON 本身也允许插入任意差异。要将数据插入本地表，请执行以下操作：

INSERT INTO http_with_arrays
SELECT
    arrayMap(x -> (x.1), JSONExtractKeysAndValues(json, 'String')) AS keys,
    arrayMap(x -> (x.2), JSONExtractKeysAndValues(json, 'String')) AS values
FROM s3('https://datasets-documentation.s3.eu-west-3.amazonaws.com/http/documents-01.ndjson.gz', 'JSONAsString')


0 rows in set. Elapsed: 12.121 sec. Processed 10.00 million rows, 107.30 MB (825.01 thousand rows/s., 8.85 MB/s.)
复制

查询此结构需要结合 indexOf 函数，通过键名获取值数组对应的索引。例如：values[indexOf(keys, 'status')]。对于嵌套 JSON，例如 request 列，可结合 simpleJSONExtractString 提取值。

SELECT toUInt16(values[indexOf(keys, 'status')])                           as status,
       simpleJSONExtractString(values[indexOf(keys, 'request')], 'method') as method,
       count()                                                             as c
FROM http_with_arrays
WHERE status >= 400
  AND toDateTime(values[indexOf(keys, '@timestamp')]) BETWEEN '1998-01-01 00:00:00' AND '1998-06-01 00:00:00'
GROUP by method, status ORDER BY c DESC LIMIT 5;


┌─status─┬─method─┬─────c─┐
│    404 │ GET    │ 11267 │
│    404 │ HEAD   │   276 │
│    500 │ GET    │   160 │
│    500 │ POST   │   115 │
│    400 │ GET    │    81 │
└────────┴────────┴───────┘


5 rows in set. Elapsed: 0.383 sec. Processed 8.22 million rows, 1.97 GB (21.45 million rows/s., 5.15 GB/s.)
Peak memory usage: 51.35 MiB.
复制

/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 数据（2/2）

评论

相关阅读

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

评论

相关阅读

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