Unable to plot grouped values when using ARRAY JOIN

[KryptosKid]

It appears that the plugin does not properly group results when using ARRAY JOIN with GROUP BY in a query.

Using the following query from grafana, I would expect the graph to plot the separate (key, value) pairs by time:

select
    toUInt32(time) * 1000 as t,
    dataMap.key as key,
    sum(dataMap.value) as value
from test.nested_array_join_example
array join dataMap
group by t, key
order by t, key

Instead, the graph does not plot them appropriately:

The example above can be tested with the following:

create database if not exists test;
create table if not exists test.nested_array_join_example
(
    time DateTime,
    dataMap Nested (
        key String,
        value UInt64
    )
)
engine = MergeTree
partition by (toYYYYMMDD(time))
order by (time);

insert into test.nested_array_join_example values
(1613710800, ['a', 'b'], [1, 2]),
(1613710800, ['a', 'b'], [3, 4]),
(1613710801, ['a', 'b'], [5, 6]),
(1613710801, ['a', 'b'], [7, 8]),
(1613710802, ['a', 'b'], [9, 10]);

Versions

Grafana v7.4.1 (07649d1313)
vertamedia-clickhouse-datasource 2.2.3
Clickhouse 21.2.2 revision 54447

[peterpoetzi]

I have the same problem and solved it by using a VIEW on the server, which returns an identical result set. The problem appears to be the JOIN keyword, when it is present in the query, the function is disabled.

[Slach]

@peterpoetzi @KryptosKid sorry for late reply

I would like to recommends
change query to

$columns(
    dataMap.key AS key,
    sum(dataMap.value) AS value
)
FROM default.nested_array_join_example
ARRAY JOIN dataMap
WHERE $timeFilter