Fetch latest value and date from multiple tag values and fields

Fluxlang
,
1

Hi,

Our application stores controller information (multiple fields at once). The schema for the line protocol input looks like this (values are within curlies):

{client_id},deviceId={a_device_id} a_field={a_value},another_field={another_value},... {ts}

We have a query that returned the last value of a number of fields up to a given date for some devices. The data is presented with devices as rows and the latest values as columns, so the data returned resembles that to simplify processing. Such query looks like:

data = from(bucket: "{a_bucket_name}")
    |> range(start: time(v: "2021-05-01T22:00:00.000Z"), stop: time(v: "2021-05-07T21:59:59.999Z"))
    |> filter(fn: (r) => r._measurement == "{client_id}")
    |> filter(fn: (r) => r.deviceId == "a_device" or r.deviceId == "another_device" or r.deviceId == "yet_another_one")
    |> filter(fn: (r) => r._field == "a_field" or r._field == "another_field")
    |> last()
    |> pivot(
        rowKey:["deviceId"],
        columnKey: [ "_field"],
        valueColumn: "_value"
    )
    |> map(fn: (r) => ({ "0": r.a_field, "1": r.another_field, _d: r.deviceId }))
    |> drop(columns: ["_start", "_stop", "_measurement"])
    |> yield(name: "result")

Because we are pivoting on deviceId, all values appear as columns and as a result, when using our javascript client to fetch the data, we would be getting an object per device that contains all the values as properties (quite convenient for manipulation).

This is an example of the data from the query for two devices and two fields:

image
image1927Ă—188 11.2 KB

And this is an example of what we get through the javascript client:

[{
    _d: "223926",
    table: 0,
    result: "",
    0: 229688,
    1: 231234
}, {
    _d: "OPR0020_00054",
    table: 1,
    result: "",
    0: 6711,
    1: 6711
}]

We got a new request to include the date of such latest value for each of the fields.
Of course, with the current query the pivoting removes the _time column.

We tried several things but we are not able to “easily” (that is, getting one object with all the field values and times from our client) get the a new time for each of the fields.
We’d like to get something like this:

[{
    _d: "223926",
    table: 0,
    result: "",
    0: 229688,
    t0: "some_date",
    1: 231234,
    t1: "another_date"
}, {
    _d: "OPR0020_00054",
    table: 1,
    result: "",
    0: 6711,
    t0: "some_date",
    1: 6711,
    t1: "another_date"
}]

Any clues on how to proceed to get the data that we need in the format that is easy to process?

Thanks in advance

2

hmm, I must admit @danielgonnet this is the first time I have seen pivot used in this way and an awesome use case. Let me drag my colleague @Anaisdg into this one.

Anais, do you think the best option here to create another variable

date = from(bucket: "{a_bucket_name}")
    |> range(start: time(v: "2021-05-01T22:00:00.000Z"), stop: time(v: "2021-05-07T21:59:59.999Z"))
    |> filter(fn: (r) => r._measurement == "{client_id}")
    |> filter(fn: (r) => r.deviceId == "a_device" or r.deviceId == "another_device" or r.deviceId == "yet_another_one")
    |> filter(fn: (r) => r._field == "a_field" or r._field == "another_field")
    |> last()

Then perform a join between the two variables on the deviceID?

3

@danielgonnet I don’t know how performant this would be, but one approach would be to create a “lookup” function that returns the time value from a specific field/deviceId:

data =
    from(bucket: "{a_bucket_name}")
        |> range(start: time(v: "2021-05-01T22:00:00.000Z"), stop: time(v: "2021-05-07T21:59:59.999Z"))
        |> filter(fn: (r) => r._measurement == "{client_id}")
        |> filter(
            fn: (r) => r.deviceId == "a_device" or r.deviceId == "another_device" or r.deviceId == "yet_another_one",
        )
        |> filter(fn: (r) => r._field == "a_field" or r._field == "another_field")
        |> last()

getTime = (field, deviceId) => {
    lastRow =
        data
            |> findRecord(fn: (key) => key._field == field and key.deviceId == deviceId, idx: 0)

    return lastRow._time
}

data
    |> pivot(rowKey: ["deviceId"], columnKey: ["_field"], valueColumn: "_value")
    |> map(
        fn: (r) =>
            ({
                "0": r.a_field,
                "t0": getTime(field: "a_field", deviceId: r.deviceID),
                "1": r.another_field,
                "t1": getTime(field: "another_field", deviceId: r.deviceID),
                _d: r.deviceId,
            }),
    )
    |> drop(columns: ["_start", "_stop", "_measurement"])
    |> yield(name: "result")
1 Like