How to Covert column values into single string

InfluxDB 2
,
1

I have a query which gives me distinct values from tag based on filter

from(bucket: "measurement")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "name")
  |> filter(fn: (r) => r["host"] == "${filter}")
  |> filter(fn: (r) => r["tag_col"] == "value")
  |> keyValues(keyColumns: ["tag_col_out"])
  |> group()
  |> keep(columns: ["tag_col_out"])
  |> distinct(column: "tag_col_out")

Output is as below

table _value
0	value_1
0	value_2

I want to convert this data in below format.
data1=value_1&data2=value_2

Could you suggest a query to achieve this output?

2

Hello @Anaisdg,
Could you suggest a way to solve this problem ?

3

@dhyaneshnaik You could use reduce() to do this. Fair warning, if you have a lot of rows and you end up concatenating a very long string, this process will slow down considerably.

This will get you the string you’re looking for. I added some string processing in there to remove any trailing ampersands:

from(bucket: "measurement")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r["_measurement"] == "name")
    |> filter(fn: (r) => r["host"] == "${filter}")
    |> filter(fn: (r) => r["tag_col"] == "value")
    |> keyValues(keyColumns: ["tag_col_out"])
    |> group()
    |> keep(columns: ["tag_col_out"])
    |> distinct(column: "tag_col_out")
    |> reduce(
        identity: {index: 0, _value: ""},
        fn: (r, accumulator) => {
            index = accumulator.index + 1
            
            return {index: index, _value: accumulator._value + "data${index}=${r._value}&"}
        }
    )
    |> drop(columns: ["index"])
    |> map(fn: (r) => ({r with _value: strings.trimRight(v: r._value, cutset: "&")}))

This will return the following table:

_value
data1=value_1&data2=value_2

How you extract that value out of the table depends on what you’re using to do it and how you’re going to use the value. I assume it’s a query string to append to a URL.

1 Like
4

@scott Thank you for the help. Yes, it’s a query string to append to a URL
Actually, It was an urgent requirement and was able to come up with this solution only.

In case of high row count, processing time will be longer. Is there any alternate possible solution for this ?

My Actual requirement is as below:
All the values present in variable, without any human intervention should be passed in query string.
In Grafana only selected values get passed. So had to come up with this solution.

5

@dhyaneshnaik High row counts in the thousands is where you’d really start to see a difference in performance, but I can’t imagine appending thousands of query parameters to a URL. If you’re in the sub-100s of rows, you probably won’t see much of a performance hit. It just has to read, parse, and update the string for each row. This process can add up the more rows you have.