clickhouse在数组内修改数组

Question

给定以下查询:

SELECT
arrayZip(Groups.Names, Groups.Scores) AS ZipGroups,
arrayZip(Symbols.Names, Symbols.Scores) AS ZipSymbols
FROM rspamd
WHERE (IsBayes = 'spam') AND ((Action = 'no action') OR (Action = 'greylist')) AND (Score <= 6.3) AND notEmpty(Symbols.Names) AND ((Date >= (now() - toIntervalHour(72))) AND (Date <= now()))
ORDER BY Score DESC LIMIT 1

返回如下内容:

Row 1:
──────
ZipGroups:  [('MX',0.5),('policies',-0.9),('reputation',1.5636996),('mime_types',-0.1),('headers',0.5),('dkim',-0.2),('arc',0),('dmarc',-1),('Message ID',0),('url',1),('statistics',3.796762),('neural',-1),('spf',-0.2)]
ZipSymbols: [('ARC_NA',0),('BAYES_SPAM',3.796762),('R_DKIM_ALLOW',-0.2),('MX_INVALID',0.5),('FROM_HAS_DN',0),('MV_CASE',0.5),('TO_MATCH_ENVRCPT_ALL',0),('MIME_GOOD',-0.1),('R_SPF_ALLOW',-0.2),('URI_COUNT_ODD',1),('RCPT_COUNT_ONE',0),('MID_RHS_MATCH_FROMTLD',0),('TO_DN_ALL',0),('DKIM_TRACE',0),('DMARC_POLICY_ALLOW',-1),('NEURAL_HAM_SHORT',-1),('IP_REPUTATION_SPAM',1.5636996),('RCVD_COUNT_ZERO',0),('FROM_EQ_ENVFROM',0),('MIME_TRACE',0),('ASN',0)]

我发现结果非常不可读，想转换它。我用"红"字。我可以使用JSON作为显示选项。如果结果是这样就更好了:

["key1": value1, "key2": value2, ...]

[更新2021-02-17 11:00 CET]

数组中的值的来源是:

符号。名字和符号。分数。它们1:1匹配。因此，每个SQL记录都有这两个字段。我想要一个JSON格式的组合输出。

下面是一个来自SQL的示例记录:

Groups.Names:   ['statistics','policies','reputation','mime_types','headers','local','arc','Message ID','body','neural']
Groups.Scores:  [-2.2,0,-0.7413712,-2.2,2.1666667,0,0,0,0,-1]
Symbols.Names:  ['ARC_NA','RCVD_VIA_SMTP_AUTH','TO_DOM_EQ_FROM_DOM','BAYES_HAM','FROM_HAS_DN','LOCAL_META_LEARN_HAM_1','MV_CASE','TO_MATCH_ENVRCPT_ALL','MIME_GOOD','PREVIOUSLY_DELIVERED','HAS_ATTACHMENT','RCPT_COUNT_ONE','GENERIC_REPUTATION','TO_DN_ALL','NEURAL_HAM_SHORT','HAS_X_PRIO_THREE','RCVD_COUNT_ONE','SIGNED_PGP','POLICY_SUBMISSION','MIME_TRACE','R_MIXED_CHARSET','ASN','RCVD_TLS_ALL','MID_RHS_MATCH_FROM','FROM_EQ_ENVFROM']
Symbols.Scores: [0,0,0,-2.2,0,0,0.5,0,-0.2,0,0,0,-0.7413712,0,-1,0,0,-2,0,0,1.6666666,0,0,0,0]

对于上面给出的例子，我希望符号的结果是这样的:

{
"ARC_NA": 0,
"BAYES_SPAM": 3.796762,
"R_DKIM_ALLOW": -0.2,
"MX_INVALID": 0.5,
"FROM_HAS_DN": 0,
"MV_CASE": 0.5,
"TO_MATCH_ENVRCPT_ALL": 0,
"MIME_GOOD": -0.1,
"R_SPF_ALLOW": -0.2,
"URI_COUNT_ODD": 1,
"RCPT_COUNT_ONE": 0,
"MID_RHS_MATCH_FROMTLD": 0,
"TO_DN_ALL": 0,
"DKIM_TRACE": 0,
"DMARC_POLICY_ALLOW": -1,
"NEURAL_HAM_SHORT": -1,
"IP_REPUTATION_SPAM": 1.5636996,
"RCVD_COUNT_ZERO": 0,
"FROM_EQ_ENVFROM": 0,
"MIME_TRACE": 0,
"ASN": 0
}

(/更新)

是否有可能转换查询以获得如图所示的结果?

Answer 1

SELECT formatRow('JSONEachRow', x, y) AS z
FROM
(
SELECT
number AS x,
number / 3 AS y
FROM numbers(3)
)
Query id: 1413bd9f-bd32-48fc-b1e0-ac4eadd41f11
┌─z────────────────────────────────┐
│ {"x":"0","y":0}
│
│ {"x":"1","y":0.3333333333333333}
│
│ {"x":"2","y":0.6666666666666666}
│
└──────────────────────────────────┘

Answer 2

SELECT
x,
concat('{', arrayStringConcat(arrayMap((x, y) -> concat('"', x, '": ', toString(y)), `Symbols.Names`, `Symbols.Scores`), ', '), '}') AS j
FROM
(
SELECT
1 AS x,
['ARC_NA', 'RCVD_VIA_SMTP_AUTH', 'TO_DOM_EQ_FROM_DOM'] AS `Symbols.Names`,
[-2.2, 2, 0] AS `Symbols.Scores`
)
Query id: 6a852799-35ee-44ed-a58e-331eaf5917a2
┌─x─┬─j──────────────────────────────────────────────────────────────────┐
│ 1 │ {"ARC_NA": -2.2, "RCVD_VIA_SMTP_AUTH": 2, "TO_DOM_EQ_FROM_DOM": 0} │
└───┴────────────────────────────────────────────────────────────────────┘