Map function across tables

1

Hi community! I’m stumped so I’m here. I have a map function (red arrow in image) that references two points (chast_int and locremctl_int) and returns two tables. Will the map function be applied to all rows in both tables?

I’m asking because the output I’m looking for is the map value which I will set to a new point_name. Just want to confirm this is how it works. Thank you!

image
image1801×513 63.3 KB


from(bucket: "dashboard-metrics-staging")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "site")
  |> filter(fn: (r) => r["point_name"] == "chast_int" or r["point_name"] == "locremctl_int")
  |> filter(fn: (r) => r["_field"] == "float")
  |> aggregateWindow(every: 1h, fn: mean, createEmpty: true)
  |> map(fn: (r) => ({ r with _value: if r.chast_int == 1.0 and r.locremctl_int == 1.0 then 1.0 else 0.0}))
2

@scott or @Anaisdg Would love your feedback here. Thank you!

3

@Marion_Akagi map() iterates over each row in all input tables, but only values in the row are available to the map iteration. map() structures row data as a record, and assigns the record to r—for example:

r = {_time: 2024-05-15T00:00:00Z, _measurement: "site", point_name: "chast_int", site_controller_id: "0215D1EC", topic: "...", _field: "float:, _value: 0.0}

For map() to be able to compare chast_int to locremctl_int, those two columns need to exist in each row. For your specific query, you can use pivot() to do that:

from(bucket: "dashboard-metrics-staging")
    |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
    |> filter(fn: (r) => r["_measurement"] == "site")
    |> filter(fn: (r) => r["point_name"] == "chast_int" or r["point_name"] == "locremctl_int")
    |> filter(fn: (r) => r["_field"] == "float")
    |> aggregateWindow(every: 1h, fn: mean, createEmpty: true)
    |> pivot(rowKey: ["_time"], columnKey: ["point_name"], valueColumn: "_value")
    |> map(fn: (r) => ({ r with _value: if r.chast_int == 1.0 and r.locremctl_int == 1.0 then 1.0 else 0.0}))
1 Like
4

@scott Thank you so much! I tried a pivot earlier but I had the aggregate window AFTER the pivot and it was dropping columns I needed but your solution of aggregate window before pivot and map at the end works beautifully! I appreciate you!

2 Likes