在 ClickHouse 中导入和查询 JSON 数组对象
了解如何将 JSON 数组对象导入到 ClickHouse 中，并使用 JSON 函数和数组操作执行高级查询。
问题
我如何导入 JSON 数组以及如何查询内部对象？
答案
将此 1 行 JSON 数组转储到 sample.json
{"_id":"1","channel":"help","events":[{"eventType":"open","time":"2021-06-18T09:42:39.527Z"},{"eventType":"close","time":"2021-06-18T09:48:05.646Z"}]},{"_id":"2","channel":"help","events":[{"eventType":"open","time":"2021-06-18T09:42:39.535Z"},{"eventType":"edit","time":"2021-06-18T09:42:41.317Z"}]},{"_id":"3","channel":"questions","events":[{"eventType":"close","time":"2021-06-18T09:42:39.543Z"},{"eventType":"create","time":"2021-06-18T09:52:51.299Z"}]},{"_id":"4","channel":"general","events":[{"eventType":"create","time":"2021-06-18T09:42:39.552Z"},{"eventType":"edit","time":"2021-06-18T09:47:29.109Z"}]},{"_id":"5","channel":"general","events":[{"eventType":"edit","time":"2021-06-18T09:42:39.560Z"},{"eventType":"open","time":"2021-06-18T09:42:39.680Z"},{"eventType":"close","time":"2021-06-18T09:42:41.207Z"},{"eventType":"edit","time":"2021-06-18T09:42:43.372Z"},{"eventType":"edit","time":"2021-06-18T09:42:45.642Z"}]}
检查数据
clickhousebook.local :) SELECT * FROM file('/path/to/sample.json','JSONEachRow');
SELECT *
FROM file('/path/to/sample.json', 'JSONEachRow')
Query id: 0bbfa09f-ac7f-4a1e-9227-2961b5ffc2d4
┌─_id─┬─channel───┬─events─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ 1 │ help │ [{'eventType':'open','time':'2021-06-18T09:42:39.527Z'},{'eventType':'close','time':'2021-06-18T09:48:05.646Z'}] │
│ 2 │ help │ [{'eventType':'open','time':'2021-06-18T09:42:39.535Z'},{'eventType':'edit','time':'2021-06-18T09:42:41.317Z'}] │
│ 3 │ questions │ [{'eventType':'close','time':'2021-06-18T09:42:39.543Z'},{'eventType':'create','time':'2021-06-18T09:52:51.299Z'}] │
│ 4 │ general │ [{'eventType':'create','time':'2021-06-18T09:42:39.552Z'},{'eventType':'edit','time':'2021-06-18T09:47:29.109Z'}] │
│ 5 │ general │ [{'eventType':'edit','time':'2021-06-18T09:42:39.560Z'},{'eventType':'open','time':'2021-06-18T09:42:39.680Z'},{'eventType':'close','time':'2021-06-18T09:42:41.207Z'},{'eventType':'edit','time':'2021-06-18T09:42:43.372Z'},{'eventType':'edit','time':'2021-06-18T09:42:45.642Z'}] │
└─────┴───────────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
5 rows in set. Elapsed: 0.001 sec.
创建一个表来接收 JSON 行
clickhousebook.local :) CREATE TABLE IF NOT EXISTS sample_json_objects_array (
`rawJSON` String EPHEMERAL,
`_id` String DEFAULT JSONExtractString(rawJSON, '_id'),
`channel` String DEFAULT JSONExtractString(rawJSON, 'channel'),
`events` Array(JSON) DEFAULT JSONExtractArrayRaw(rawJSON, 'events')
) ENGINE = MergeTree
ORDER BY
channel
CREATE TABLE IF NOT EXISTS sample_json_objects_array
(
`rawJSON` String EPHEMERAL,
`_id` String DEFAULT JSONExtractString(rawJSON, '_id'),
`channel` String DEFAULT JSONExtractString(rawJSON, 'channel'),
`events` Array(JSON) DEFAULT JSONExtractArrayRaw(rawJSON, 'events')
)
ENGINE = MergeTree
ORDER BY channel
Query id: d02696dd-3f9f-4863-be2a-b2c9a1ae922d
0 rows in set. Elapsed: 0.173 sec.
插入数据
clickhousebook.local :) INSERT INTO
sample_json_objects_array
SELECT
*
FROM
file(
'/opt/cases/000000/sample_json_objects_arrays.json',
'JSONEachRow'
);
INSERT INTO sample_json_objects_array SELECT *
FROM file('/opt/cases/000000/sample.json', 'JSONEachRow')
Query id: 60c4beab-3c2c-40c1-9c6f-bbbd7118dde3
Ok.
0 rows in set. Elapsed: 0.002 sec.
检查数据推断如何处理 JSON 对象类型
clickhousebook.local :) DESCRIBE TABLE sample_json_objects_array SETTINGS describe_extend_object_types = 1;
DESCRIBE TABLE sample_json_objects_array
SETTINGS describe_extend_object_types = 1
Query id: 302c0c84-1b63-4f60-ad95-d91c0267b0d4
┌─name────┬─type────────────────────────────────────────┬─default_type─┬─default_expression─────────────────────┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ rawJSON │ String │ EPHEMERAL │ defaultValueOfTypeName('String') │ │ │ │
│ _id │ String │ DEFAULT │ JSONExtractString(rawJSON, '_id') │ │ │ │
│ channel │ String │ DEFAULT │ JSONExtractString(rawJSON, 'channel') │ │ │ │
│ events │ Array(Tuple(eventType String, time String)) │ DEFAULT │ JSONExtractArrayRaw(rawJSON, 'events') │ │ │ │
└─────────┴─────────────────────────────────────────────┴──────────────┴────────────────────────────────────────┴─────────┴──────────────────┴────────────────┘
Events 是一个 Tuple 数组，每个元组包含一个 eventType String 和一个 time String 字段。 后者类型不太理想（我们希望使用 DateTime 代替）。
让我们看看数据
clickhousebook.local :) SELECT
_id,
channel,
events.eventType,
events.time
FROM sample_json_objects_array
WHERE has(events.eventType, 'close')
SELECT
_id,
channel,
events.eventType,
events.time
FROM sample_json_objects_array
WHERE has(events.eventType, 'close')
Query id: 3ddd6843-5206-4f52-971f-1699f0ba1728
┌─_id─┬─channel───┬─events.eventType──────────────────────┬─events.time──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ 5 │ general │ ['edit','open','close','edit','edit'] │ ['2021-06-18T09:42:39.560Z','2021-06-18T09:42:39.680Z','2021-06-18T09:42:41.207Z','2021-06-18T09:42:43.372Z','2021-06-18T09:42:45.642Z'] │
│ 1 │ help │ ['open','close'] │ ['2021-06-18T09:42:39.527Z','2021-06-18T09:48:05.646Z'] │
│ 3 │ questions │ ['close','create'] │ ['2021-06-18T09:42:39.543Z','2021-06-18T09:52:51.299Z'] │
└─────┴───────────┴───────────────────────────────────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
3 rows in set. Elapsed: 0.001 sec.
让我们运行几个查询
_id 和 channel，对于 eventType 值为 close 的事件
clickhousebook.local :) SELECT
_id,
channel,
events.eventType
FROM
sample_json_objects_array
WHERE
has(events.eventType,'close')
SELECT
_id,
channel,
events.eventType
FROM sample_json_objects_array
WHERE has(events.eventType, 'close')
Query id: 033a0c56-7bfa-4261-a334-7323bdc40f87
┌─_id─┬─channel───┬─events.eventType──────────────────────┐
│ 5 │ general │ ['edit','open','close','edit','edit'] │
│ 1 │ help │ ['open','close'] │
│ 3 │ questions │ ['close','create'] │
└─────┴───────────┴───────────────────────────────────────┘
┌─_id─┬─channel───┬─events.eventType──────────────────────┐
│ 5 │ general │ ['edit','open','close','edit','edit'] │
│ 1 │ help │ ['open','close'] │
│ 3 │ questions │ ['close','create'] │
└─────┴───────────┴───────────────────────────────────────┘
6 rows in set. Elapsed: 0.001 sec.
我们想查询 time，例如在给定时间范围内的所有事件，但我们注意到它被导入为 String
clickhousebook.local :) SELECT toTypeName(events.time) FROM sample_json_objects_array;
SELECT toTypeName(events.time)
FROM sample_json_objects_array
Query id: 27f07f02-66cd-420d-8623-eeed7d501014
┌─toTypeName(events.time)─┐
│ Array(String) │
│ Array(String) │
│ Array(String) │
│ Array(String) │
│ Array(String) │
└─────────────────────────┘
5 rows in set. Elapsed: 0.001 sec.
因此，为了将这些作为日期处理，首先我们想将其转换为 DateTime。 要转换一个数组，我们使用 map 函数
clickhousebook.local :)
SELECT
_id,
channel,
arrayMap(x->parseDateTimeBestEffort(x), events.time)
FROM
sample_json_objects_array
SELECT
_id,
channel,
arrayMap(x -> parseDateTimeBestEffort(x), events.time)
FROM sample_json_objects_array
Query id: f3c7881e-b41c-4872-9c67-5c25966599a1
┌─_id─┬─channel───┬─arrayMap(lambda(tuple(x), parseDateTimeBestEffort(x)), events.time)─────────────────────────────────────────────┐
│ 4 │ general │ ['2021-06-18 11:42:39','2021-06-18 11:47:29'] │
│ 5 │ general │ ['2021-06-18 11:42:39','2021-06-18 11:42:39','2021-06-18 11:42:41','2021-06-18 11:42:43','2021-06-18 11:42:45'] │
│ 1 │ help │ ['2021-06-18 11:42:39','2021-06-18 11:48:05'] │
│ 2 │ help │ ['2021-06-18 11:42:39','2021-06-18 11:42:41'] │
│ 3 │ questions │ ['2021-06-18 11:42:39','2021-06-18 11:52:51'] │
└─────┴───────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
5 rows in set. Elapsed: 0.001 sec.
我们可以使用 toTypeName 在两个数组上欣赏差异
clickhousebook.local :) SELECT
_id,
channel,
toTypeName(events.time) as events_as_strings,
toTypeName(arrayMap(x->parseDateTimeBestEffort(x), events.time)) as events_as_datetime
FROM
sample_json_objects_array
SELECT
_id,
channel,
toTypeName(events.time) AS events_as_strings,
toTypeName(arrayMap(x -> parseDateTimeBestEffort(x), events.time)) AS events_as_datetime
FROM sample_json_objects_array
Query id: 1af54994-b756-472f-88d7-8b5cdca0e54e
┌─_id─┬─channel───┬─events_as_strings─┬─events_as_datetime─┐
│ 4 │ general │ Array(String) │ Array(DateTime) │
│ 5 │ general │ Array(String) │ Array(DateTime) │
│ 1 │ help │ Array(String) │ Array(DateTime) │
│ 2 │ help │ Array(String) │ Array(DateTime) │
│ 3 │ questions │ Array(String) │ Array(DateTime) │
└─────┴───────────┴───────────────────┴────────────────────┘
5 rows in set. Elapsed: 0.001 sec.
现在让我们获取 time 在给定间隔内的行的 id。
我们使用 arrayCount 来查看 map 函数返回的数组中，匹配条件 x BETWEEN toDateTime('2021-06-18 11:46:00', 'Europe/Rome') AND toDateTime('2021-06-18 11:50:00', 'Europe/Rome') 的项目数量是否大于 0
clickhousebook.local :) SELECT
_id,
arrayMap(x -> parseDateTimeBestEffort(x), events.time)
FROM
sample_json_objects_array
WHERE
arrayCount(
x -> x BETWEEN toDateTime('2021-06-18 11:46:00', 'Europe/Rome')
AND toDateTime('2021-06-18 11:50:00', 'Europe/Rome'),
arrayMap(x -> parseDateTimeBestEffort(x), events.time)
) > 0;
SELECT
_id,
arrayMap(x -> parseDateTimeBestEffort(x), events.time)
FROM sample_json_objects_array
WHERE arrayCount(x -> ((x >= toDateTime('2021-06-18 11:46:00', 'Europe/Rome')) AND (x <= toDateTime('2021-06-18 11:50:00', 'Europe/Rome'))), arrayMap(x -> parseDateTimeBestEffort(x), events.time)) > 0
Query id: d4882fc3-9f99-4e87-9f89-47683f10656d
┌─_id─┬─arrayMap(lambda(tuple(x), parseDateTimeBestEffort(x)), events.time)─┐
│ 4 │ ['2021-06-18 11:42:39','2021-06-18 11:47:29'] │
│ 1 │ ['2021-06-18 11:42:39','2021-06-18 11:48:05'] │
└─────┴─────────────────────────────────────────────────────────────────────┘
2 rows in set. Elapsed: 0.002 sec.
⚠️
请记住，在撰写本文时，当前的 JSON 实现是实验性的，不适合生产环境。
这个例子展示了如何快速导入 JSON 并开始查询它，代表了易用性之间的权衡，我们可以将 JSON 对象作为 JSON 类型导入，而无需提前指定模式类型。 对于快速测试来说很方便，但是对于数据的长期使用，我们希望（以这个例子为例）使用最合适的类型来存储数据，因此对于 time 字段，使用 DateTime 代替 String，以避免像上面说明的那样进行导入后的转换阶段。 请参阅 文档 以了解更多关于处理 JSON 的信息。
·6 分钟阅读
