GroupArray function with tuple not supported?

[Alan01252]

Describe the bug

Unable to pass query with groupArray

SELECT t, groupArray((reason, c)) as reasons FROM ( SELECT toStartOfFiveMinute(record_datetime) AS t, JSONExtractInt(data, 'reason') AS reason, count(*) AS c FROM hepic_data.sip_transaction_call WHERE record_datetime >= toDateTime(1660711612) AND JSONExtractInt(data, 'reason') > 0 AND JSONExtractInt(data, 'reason') != 16 AND JSONExtractInt(data, 'reason') != 17 AND JSONExtractInt(data, 'reason') != 31 GROUP BY t, reason ORDER BY t, reason ) group by t order by t
returns
sql: Scan error on column index 1, name "isdnReasons": unsupported Scan, storing driver.Value type [][]interface {} into type *string: Could not process SQL results

If I wrap toString around group array I get the data I want but in a format I can't seen to wrangle grafana to time visualization to support

2022-08-17 06:30:00 | [(20,2),(21,2),(102,2)]

Versions
Grafana and Plugin versions.

Grafana 9.0.3
Clickhouse 1.1.2.

Expected behavior

When performing this with the vertmedia plugin and the clickhouse endpoint the group array is returned as follows:

Which allows rendering via the timeseries. The expected behaviour would be to be able to use the same query in the official clickhouse plugin.

Additional context

I'm pretty sure if I wrangled hard enough I could get this working straight from the clickhouse query, but it would be great if this worked out of the box.

Any help greatly appreciated :)

[Alan01252]

Hmm I had thought I'd of been able to work around it by doing

`SELECT time, toJSONString(groupArray((reason, c))) as data FROM
(
SELECT $__timeInterval(record_datetime) as time,
JSONExtractInt(data, 'reason') AS reason,
count(*) AS c
FROM hepic_data.sip_transaction_call
WHERE $__timeFilter(record_datetime)
AND JSONExtractInt(data, 'reason') > 0
AND JSONExtractInt(data, 'reason') != 16
AND JSONExtractInt(data, 'reason') != 17
AND JSONExtractInt(data, 'reason') != 31
)

GROUP BY time, reason ORDER BY time, reason
) group by time order by time`

and then telling grafana that field was json but that doesn't work either, although it feels like it should and I am missing something.

[gingerwizard]

Should be fixed in 2.0 beta. Can you test @Alan01252 ?

[gingerwizard]

@Alan01252 2.0 is out, please test

[Alan01252]

Will do mate. On me holidays at the moment but will let you know when I'm back!

[Alan01252]

This does work, sort of.. With the old vertmedia this could be rendered directly into a visualization, now although the query does not error you still get.

but it does render the data in the table without error

[gingerwizard]

thanks @Alan01252 not quite sure how you want this data returned - you can use transformations in Grafana to unfold the array if needed. Are you looking for a row per array entry?

[Alan01252]

So with the old plugin the data renders like this

With the same query as above, I don't know what magic the plugin is doing that is allowing it.

[Alan01252]

Grafana to unfold the array if needed. Are you looking for a row per array entry?

Also, ( and I'm entirely sure it's me being thick here ) I really can't work out what transformation I should be using to achieve the above, any help there would be greatly appreciated :)

[Girgitt]

I do not want to highjack this thread but the very basic use case for the groupAray function is to get e.g. multiple values for the same timestamp - sounds quite bizarre at first but can be used to provide anti-aliasing. For technical application it is super important.

reduced example of a query that works with Altinity plugin but does not in the Grafana Labs one:

[gingerwizard]

@Alan01252 sorry was ooto.

I'm still not clear how you want this to render. Are you expecting a separate series for each entry of the array e.g. all position 0s to be a series, all 1s to be another etc?

Can you send me what your query returns in the clickhouse-client?

[Alan01252]

Hi @gingerwizard thanks for getting back to me!

Yep, that's what the plugin seems to do, so if we take that first timestamp

2022-08-30 reads as, there were 4 of status code 1, 1 of status code 19, 2 of status code 20.

Rendering a graph that looks like

Now fortunately/unfortunately depending on how you look at it, we have a tiny number of values usually so the graph above doesn't show why this is useful but more than once this graph has saved us as it quickly shows an increase in one or more status code showing a real problem in our stack :)

[gingerwizard]

ok now i understand the intention i think we can do something. thank you @Alan01252 and sorry for the delay.

[Alan01252]

Absolutely no need to apologise my friend :) More than happy with you taking the time just to look at my particular special brand of edge case :)

[mshustov]

@jkaflik would you mind taking a look? @gingerwizard can help you with the investigation