ClickHouse中使用CTE时如何保留查询行数？

我有一段可正常运行的ClickHouse查询：

WITH CTE AS (
    SELECT
        -- ClickHouse 专属JSON函数
        JSON_QUERY(json, '$.projects[*].userId') as userIds
    FROM $table
    WHERE
        dt BETWEEN toDateTime64($from, 3)
        AND toDateTime64($to, 3)
        -- ClickHouse 专属JSON函数
        AND JSONHas(json, 'projects')
    HAVING userIds != ''
)
SELECT * FROM CTE

该查询返回如下结果：

userIds
["f72605b9-3a4c-402e-8eec-9dfc61be8ed9"]
["fbb47dda-3026-40e0-9565-a66386905289"]
["fbb47dda-3026-40e0-9565-a66386905289", "334d0921-149b-42da-8c21-8649c3afa2d1"]
["fbb47dda-3026-40e0-9565-a66386905289", "334d0921-149b-42da-8c21-8649c3afa2d1"]

我通过toTypeName函数确认CTE中每行的userIds都是字符串类型，原本期望修改查询后返回和原结果行数一致的类型信息：

returnType
String
String
String
String

于是我修改了查询：

WITH CTE AS (
    SELECT
        JSON_QUERY(json, '$.projects[*].userId') as userIds
    FROM $table
    WHERE
        dt BETWEEN toDateTime64($from, 3)
        AND toDateTime64($to, 3)
        AND JSONHas(json, 'projects')
    HAVING userIds != ''
)
SELECT toTypeName(userIds) FROM CTE

但实际只返回一行结果：

toTypeName(userIds)
String

请问怎么修改查询才能保留原有的行数？

出现这个问题是因为ClickHouse的toTypeName是聚合函数，默认会对结果集进行合并，当直接调用它且未指定分组时，会把所有行的类型信息合并成一行返回。

要保留原有行数，可通过以下几种方式修改：

方法1：同时查询原字段与类型（直观验证） #

将类型字段和原userIds字段一起查询，ClickHouse会为每行单独计算类型：

WITH CTE AS (
    SELECT
        JSON_QUERY(json, '$.projects[*].userId') as userIds
    FROM $table
    WHERE
        dt BETWEEN toDateTime64($from, 3)
        AND toDateTime64($to, 3)
        AND JSONHas(json, 'projects')
    HAVING userIds != ''
)
SELECT userIds, toTypeName(userIds) AS returnType FROM CTE

方法2：仅返回类型但保留行数 #

如果只需要类型列，可以通过添加行号强制每行独立计算。比如用rowNumberInAllBlocks()生成唯一行号，再按行号和原字段分组：

WITH CTE AS (
    SELECT
        JSON_QUERY(json, '$.projects[*].userId') as userIds,
        rowNumberInAllBlocks() AS rn
    FROM $table
    WHERE
        dt BETWEEN toDateTime64($from, 3)
        AND toDateTime64($to, 3)
        AND JSONHas(json, 'projects')
    HAVING userIds != ''
)
SELECT toTypeName(userIds) AS returnType FROM CTE GROUP BY rn, userIds

方法3：简化查询逻辑 #

直接在主查询中计算类型，避免CTE的额外处理：

SELECT
    toTypeName(JSON_QUERY(json, '$.projects[*].userId')) AS returnType
FROM $table
WHERE
    dt BETWEEN toDateTime64($from, 3)
    AND toDateTime64($to, 3)
    AND JSONHas(json, 'projects')
HAVING JSON_QUERY(json, '$.projects[*].userId') != ''

以上任意一种方式，都能得到和原结果行数一致的类型输出。

内容的提问来源于stack exchange，提问作者Foobar