How do I preserve the number of rows when using a CTE?

by

I have the following ClickHouse query that works fine:

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

It returns a table of the form:

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"]

Since each row in the CTE is of type string, I’m trying to do a simple test where I use the toTypeName function to get back the following result:

returnType
String
String
String
String

So, I modified my query like this:

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

However, this just returns a single row:

toTypeName(userIds)
String

How can I modify my query so that it preserves the number of rows?

  • I might just misunderstand how SQL works. I’m basically just trying to do a map on the rows selected by the CTE. I.e, in Javascript, you can do array.map(x => x * 2).filter(x => x > 3) Here, I’m trying to do a cte_results.map(x => toTypeName(x) I’m not familiar with SQL, so this might not make any sense. @jNevill

    – 




  • the queries are identical, so should also return the same result, always

    – 

  • Not familiar with JavaScript but `totypename| is not a function in standard SQL. Can you really use it in the manner of your modified example? No errors?

    – 

Try doing an aggregation on the result of the CTE. For example:

WITH CTE AS (
    SELECT
        -- ClickHouse specific JSON function
        JSON_QUERY(json, '$.projects[*].userId') as userIds
    FROM $table
    WHERE
        dt BETWEEN toDateTime64($from, 3)
        AND toDateTime64($to, 3)
        -- ClickHouse specific JSON function
        AND JSONHas(json, 'projects')
    HAVING userIds != ''
)
SELECT 
    count(),
    arrayJoin(userIds) AS id
FROM CTE
GROUP BY id

Leave a Comment