How to query all last values for the particular tag

Fluxlang
1

I am using the following query to get last values from database within selected range filtered by tag:

from(bucket: “MYBUCKET”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“mytag”] == “${TAG1}”)
|> filter(fn: (r) => r[“_field”] == “Status”)
|> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)

If there is no data for some values of mytag during specified range, they are just not returning by this query.

How is it possible to have last values for all possible tag values of mytag, i.e. have nulls for those tag values that do not exist in the specified range?

I found how to list all possible tag values:

import “influxdata/influxdb/schema”

schema.tagValues(
bucket: “MYBUCKET”,
tag: “mytag”,
predicate: (r) => r._field == “Status”,
start: -3000d
)

But can’t figure out how to join these two queries.

For example, I have “val1, val2, val3” as possible values of “mytag”. In selected period val1 and val3 have values value1 and value3 respectively, but val2 doesn’t have.

I need to get:

val1 : value1
val2: NULL
val3: value3

2

Here’s a blog post on outer joins in flux:

For example:

import "array"
import "join"

left =
    array.from(
        rows: [
            {_time: 2022-01-01T00:00:00Z, _value: 1, label: "a"},
            {_time: 2022-01-01T00:00:00Z, _value: 2, label: "b"},
            {_time: 2022-01-01T00:00:00Z, _value: 3, label: "d"},
        ],
    )
right =
    array.from(
        rows: [
            {_time: 2022-01-01T00:00:00Z, _value: 0.4, id: "a"},
            {_time: 2022-01-01T00:00:00Z, _value: 0.5, id: "c"},
            {_time: 2022-01-01T00:00:00Z, _value: 0.6, id: "d"},
        ],
    )

join.full(
    left: left,
    right: right,
    on: (l, r) => l.label == r.id and l._time == r._time,
    as: (l, r) => {
    
        label = if exists l.label then l.label else r.id

        return {label: label, v_left: l._value, v_right: r._value}
    },
)

So you’d have to assign your two queries to 2 variables and then perform an full outer join.

1 Like
3

Thank you very much. Outer join helped to get what I needed.

import "array"
import "join"
import "influxdata/influxdb/schema"

left = schema.tagValues(
    bucket: "MYBUCKET",
    tag: "mytag",
    predicate: (r) => r._field == "Status",
    start: -3000d
)


right =
from(bucket: "MYBUCKET")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["mytag"] == "${TAG1}")
  |> filter(fn: (r) => r["_field"] == "Status")
  |> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
  |> last()
  |> group()
  |> pivot(rowKey:["_time", "mytag"], columnKey: ["_field"], valueColumn: "_value")

join.full(
    left: left,
    right: right,
    on: (l, r) => l._value == r.mytag,
    as: (l, r) => {
    
        label = if exists l._value then l._value else r.mytag

        return {label: label, v_left: l._value, v_right: r.Status}
    },
)