Issue obtaining column titles with InfluxDB query

[temuccio]

Hi everyone,

I’m facing an issue with an InfluxDB query, and I hope someone can help me understand what’s going wrong. I’m trying to retrieve data from a bucket called “Tracker” using the following query:

 from(bucket: "Tracker")
  |> range(start: 2023-11-28T17:03:12.737Z, stop: 2023-11-28T17:33:12.737Z)
  |> filter(fn: (r) => r["dev_eui"] == "e8e1e1000108283a")
  |> filter(fn: (r) => r["f_port"] == "136")
  |> filter(fn: (r) => r["_measurement"] == "device_frmpayload_data_bytes_0" or
                       r["_measurement"] == "device_frmpayload_data_battery" or
                       r["_measurement"] == "device_frmpayload_data_temperature" or
                       r["_measurement"] == "device_frmpayload_data_positionLongitude" or
                       r["_measurement"] == "device_frmpayload_data_positionLatitude") 
  |> pivot(rowKey:["_time"], columnKey: ["_measurement"], valueColumn: "_value")
  |> rename(columns: {device_frmpayload_data_bytes_0:"B0"})
  |> rename(columns: {device_frmpayload_data_battery:"battery"})
  |> rename(columns: {device_frmpayload_data_positionLatitude:"lat"})
  |> rename(columns: {device_frmpayload_data_positionLongitude:"lng"})
  |> rename(columns: {device_frmpayload_data_temperature:"Temp"})
  |> drop(columns: ["_field", "application_name", "device_name"])

Everything seems to be working correctly, but when I retrieve the results, the column titles contain data in JSON format. I can’t figure out why this is happening

.

Has anyone encountered a similar issue, or does anyone have any suggestions on how to resolve it? Perhaps there’s something wrong with my query?

Thanks in advance for any help!

I use Grafana v10.0.3 (eb8dd72637)

[grant2]

Welcome @temuccio to the Grafana forum. Nice work on your query. I believe the column titles are correct, but it’s the inclusion of {_start="2023-11-24.... _stop="20203-11-25...} that you want to exclude, right?

How does your query look when viewed as a table in Influx Data Explorer?

Perhaps you could add to the |> drop function the columns “_start”, “_stop” and any other ones appearing inside the {..}

As a last resort (and probably the fastest & easiest method), you could use the Rename by regex transformation or Organize fields transformation in Grafana to clean up the column titles.

[temuccio]

Hi @grant2,
thanks for response.
Yes, i want to exclude the part with {_start="2023-11-24.... _stop="20203-11-25...}.
I try the query in Influxdb and it works fine:

I don’t understand why into grafana I have this more data…
Where I find “Rename by regex transformation” or “Organize fields transformation”?
It is in Transform Tabs?

[grant2]

Here are the docs: Transform data | Grafana documentation

Example:

[temuccio]

Thank you @grant2