Is this possible in InfluxDB? (lookup value in an array)

Fluxlang
1

Greetings to everyone.

I have two queries: one for temperature and one for millivolts:

Query A:

from(bucket: "mydata")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "IBQFurnaceZoneData")
  |> filter(fn: (r) => r["_field"] == "temperature")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")

Query B:

from(bucket: "mydata")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "IBQFurnaceZoneData")
  |> filter(fn: (r) => r["_field"] == "millivolts")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")

Let’s say query A results in 1450 and query B results in 1012

The matrix shown below references temperature vs %C to get the millivolts, but in my case, I have the temperature and the millivolts, and I need %C. Can I use the values returned by Query A and B to be “paired” / matched against the matrix, and the %C value of 0.15 would be returned?

image

Could I do something like the array shown below? If yes, what would be the function to use this array as a sort of lookup table to find percent_carbon (if the other two values are known)?

import "experimental/array"

a = [
    {temperature: 1400, percent_carbon: 0.05, millivolts: 957},
    {temperature: 1400, percent_carbon: 0.10, millivolts: 989},
    {temperature: 1400, percent_carbon: 0.15, millivolts: 1007},
    {temperature: 1400, percent_carbon: 0.20, millivolts: 1021},
    {temperature: 1425, percent_carbon: 0.05, millivolts: 959},
    {temperature: 1425, percent_carbon: 0.10, millivolts: 991},
    {temperature: 1425, percent_carbon: 0.15, millivolts: 1010},
    {temperature: 1425, percent_carbon: 0.20, millivolts: 1024},
    {temperature: 1450, percent_carbon: 0.05, millivolts: 961},
    {temperature: 1450, percent_carbon: 0.10, millivolts: 993},
    {temperature: 1450, percent_carbon: 0.15, millivolts: 1012},
    {temperature: 1450, percent_carbon: 0.20, millivolts: 1026},
    {temperature: 1475, percent_carbon: 0.05, millivolts: 963},
    {temperature: 1475, percent_carbon: 0.10, millivolts: 996},
    {temperature: 1475, percent_carbon: 0.15, millivolts: 1015},
    {temperature: 1475, percent_carbon: 0.20, millivolts: 1029},
]
2

Just wanted to bump this. I have searched for many possible solutions and cannot find anything, but it seems to simple (given 2 values, lookup the 3rd value in an arry).

3

@grant1 You can use array.filter():

import "array"

a = [
    {temperature: 1400, percent_carbon: 0.05, millivolts: 957},
    {temperature: 1400, percent_carbon: 0.10, millivolts: 989},
    {temperature: 1400, percent_carbon: 0.15, millivolts: 1007},
    {temperature: 1400, percent_carbon: 0.20, millivolts: 1021},
    {temperature: 1425, percent_carbon: 0.05, millivolts: 959},
    {temperature: 1425, percent_carbon: 0.10, millivolts: 991},
    {temperature: 1425, percent_carbon: 0.15, millivolts: 1010},
    {temperature: 1425, percent_carbon: 0.20, millivolts: 1024},
    {temperature: 1450, percent_carbon: 0.05, millivolts: 961},
    {temperature: 1450, percent_carbon: 0.10, millivolts: 993},
    {temperature: 1450, percent_carbon: 0.15, millivolts: 1012},
    {temperature: 1450, percent_carbon: 0.20, millivolts: 1026},
    {temperature: 1475, percent_carbon: 0.05, millivolts: 963},
    {temperature: 1475, percent_carbon: 0.10, millivolts: 996},
    {temperature: 1475, percent_carbon: 0.15, millivolts: 1015},
    {temperature: 1475, percent_carbon: 0.20, millivolts: 1029},
]

// Filter the array and return the first element (record) from the filtered array
b = (a |> array.filter(fn: (x) => x.temperature == 1450 and x.millivolts == 961))[0]

// Return the the percent_carbon property of the b record
c = b.percent_carbon
4

Thank you @scott Super helpful and easy to understand. Almost there…

Just a few more follow-up questions…

  1. Pasting the above (and me having to use import “experimental/array”), I get this error:
 error @26:5-26:21: expected stream[A] but found float (argument tables)

image
image1451Ă—783 81.8 KB

image
image1417Ă—451 74.7 KB

  1. Once the above is resolved, and I want to pass in an actual temperature from this query (which only returns values between 1400 and 1475, rounded to the nearest 25)…
from(bucket: "junkbucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "TestMeasurement")
  |> filter(fn: (r) => r["_field"] == "temperature")

…how does one modify the line:

b = (a |> array.filter(fn: (x) => x.temperature == 1450 and x.millivolts == 961))[0]

so that the 1450 is in fact coming from the query? (which of course may be 1400 or 1475)

5

Also, I updated to v2.5 and can just use import “array” now, but the error message expected stream[A] but found float (argument tables) is preventing this from working.

6

@grant1 The expected stream[A] but found float (argument tables) is coming from yield(). yield() requires a stream of tables as input. You’re piping forwards a scalar float value (b.percent_carbon). You need to wrap the value in a stream of tables before you can yield. You can do this with array.from():

import "array"

// ...

array.from(rows: [{_value: c}])

I want to pass in an actual temperature from this query (which only returns values between 1400 and 1475, rounded to the nearest 25

You going to have to build in some math logic to round to the nearest 25. If you want it to round to the truly nearest 25 (both up or down), the logic is a little more complex, but still possible. You could use the following custom function:

roundToNearest = (tables=<-, x=1) =>
    tables
        |> map(fn: (r) => {
            _remainder = r._value % x
            _roundUp = if _remainder >= x / 2 then true else false
            returnValue = if _roundUp then r._value - (r._value % x) + x else r._value - (r._value % x)

            return {r with _value: returnValue}
        })

data
    |> roundToNearest(x: 25)

So your full query would look something like this:

import "array"

a = [
    {temperature: 1400, percent_carbon: 0.05, millivolts: 957},
    {temperature: 1400, percent_carbon: 0.10, millivolts: 989},
    {temperature: 1400, percent_carbon: 0.15, millivolts: 1007},
    {temperature: 1400, percent_carbon: 0.20, millivolts: 1021},
    {temperature: 1425, percent_carbon: 0.05, millivolts: 959},
    {temperature: 1425, percent_carbon: 0.10, millivolts: 991},
    {temperature: 1425, percent_carbon: 0.15, millivolts: 1010},
    {temperature: 1425, percent_carbon: 0.20, millivolts: 1024},
    {temperature: 1450, percent_carbon: 0.05, millivolts: 961},
    {temperature: 1450, percent_carbon: 0.10, millivolts: 993},
    {temperature: 1450, percent_carbon: 0.15, millivolts: 1012},
    {temperature: 1450, percent_carbon: 0.20, millivolts: 1026},
    {temperature: 1475, percent_carbon: 0.05, millivolts: 963},
    {temperature: 1475, percent_carbon: 0.10, millivolts: 996},
    {temperature: 1475, percent_carbon: 0.15, millivolts: 1015},
    {temperature: 1475, percent_carbon: 0.20, millivolts: 1029},
]

lookup = (v) => (a |> array.filter(fn: (x) => x.temperature == v))[0]

roundToNearest = (tables=<-, x=1) =>
    tables
        |> map(fn: (r) => {
            _remainder = r._value % x
            _roundUp = if _remainder >= x / 2 then true else false
            returnValue = if _roundUp then r._value - (r._value % x) + x else r._value - (r._value % x)

            return {r with _value: returnValue}
        })

data = 
    from(bucket: "junkbucket")
        |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
        |> filter(fn: (r) => r["_measurement"] == "TestMeasurement")
        |> filter(fn: (r) => r["_field"] == "temperature")

data
    |> roundToNearest(x: 25)
    |> map(fn: (r) => {
        _lookupData = lookup(v: r._value)

        return {r with percent_carbon: _lookupData.percent_carbon, millivolts: _lookupData.millivolts}
    }

Now this only uses the temperature as a lookup value so it will return the first record in the array with that temperature. Did you also want to lookup up by millivolts?

7

Thanks so much @scott

I tried many possible things over the weekend, and I was actually pretty close (I figured out that I was piping forward a scalar float, but just did not know how to wrap that value into a stream of tables).

I will test this out tonight when I am at my other test system, but your explanation is very clear.

Re: the rounding to the nearest 25 degrees, I am actually handling that in javascript via Node-RED, which is how I collect the temperature values and send into InfluxDB. I can do the same with the millivolts. But now that you have explained how to do it in Flux, I may opt for that instead.

Thanks and stay tuned!

8

Hi @scott

OK, I think we’re within striking distance…

I need to expand the full query so that we can we can look up by temperature AND by millivolts (also coming from a query; no rounding needed; the values will always be 957, 959, etc.) and receive back the corresponding percent_carbon.

Here is what I tried:

import "array"

a = [
    {temperature: 1400, percent_carbon: 0.05, millivolts: 957},
    {temperature: 1400, percent_carbon: 0.10, millivolts: 989},
    {temperature: 1400, percent_carbon: 0.15, millivolts: 1007},
    {temperature: 1400, percent_carbon: 0.20, millivolts: 1021},
    {temperature: 1425, percent_carbon: 0.05, millivolts: 959},
    {temperature: 1425, percent_carbon: 0.10, millivolts: 991},
    {temperature: 1425, percent_carbon: 0.15, millivolts: 1010},
    {temperature: 1425, percent_carbon: 0.20, millivolts: 1024},
    {temperature: 1450, percent_carbon: 0.05, millivolts: 961},
    {temperature: 1450, percent_carbon: 0.10, millivolts: 993},
    {temperature: 1450, percent_carbon: 0.15, millivolts: 1012},
    {temperature: 1450, percent_carbon: 0.20, millivolts: 1026},
    {temperature: 1475, percent_carbon: 0.05, millivolts: 963},
    {temperature: 1475, percent_carbon: 0.10, millivolts: 996},
    {temperature: 1475, percent_carbon: 0.15, millivolts: 1015},
    {temperature: 1475, percent_carbon: 0.20, millivolts: 1029},
]

lookup = (v) => (a |> array.filter(fn: (x) => x.temperature == v and x.millivolts == v))[0]

data = 
from(bucket: "junkbucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "Fake")
  |> filter(fn: (r) => r["EquipNumber"] == "A3")
  |> filter(fn: (r) => r["EquipZone"] == "chamber")
  |> filter(fn: (r) => r["MeasType"] == "actual")
  |> filter(fn: (r) => r["_field"] == "temperature" or r["_field"] == "millivolts")

data
    |> map(fn: (r) => {
        _lookupData = lookup(v: r._value)
        return {r with percent_carbon: _lookupData.percent_carbon}})

and here is the error (in the above map() function):

runtime error @34:8-36:69: map: failed to evaluate map function: cannot access element 0 of array of length 0

I tried many variations, but am clearly missing something on my single lookup function (should I have two separate ones?) and the map function (I believe it needs to reference two different lookup variables).

Many thanks in advance for your time and patience.

9

It’s because the way your array filter is built, it’s returning an empty array. There are no records in the array that have the same value (v) for both temperature and millivolts. For this to work like you’re expecting, you need to do two things:

  • Pivot the data so each field is structured as a column in each row
  • Pass the temperature and the millivolts as two separate arguments into your lookup function
import "array"

a = [
    {temperature: 1400, percent_carbon: 0.05, millivolts: 957},
    {temperature: 1400, percent_carbon: 0.10, millivolts: 989},
    {temperature: 1400, percent_carbon: 0.15, millivolts: 1007},
    {temperature: 1400, percent_carbon: 0.20, millivolts: 1021},
    {temperature: 1425, percent_carbon: 0.05, millivolts: 959},
    {temperature: 1425, percent_carbon: 0.10, millivolts: 991},
    {temperature: 1425, percent_carbon: 0.15, millivolts: 1010},
    {temperature: 1425, percent_carbon: 0.20, millivolts: 1024},
    {temperature: 1450, percent_carbon: 0.05, millivolts: 961},
    {temperature: 1450, percent_carbon: 0.10, millivolts: 993},
    {temperature: 1450, percent_carbon: 0.15, millivolts: 1012},
    {temperature: 1450, percent_carbon: 0.20, millivolts: 1026},
    {temperature: 1475, percent_carbon: 0.05, millivolts: 963},
    {temperature: 1475, percent_carbon: 0.10, millivolts: 996},
    {temperature: 1475, percent_carbon: 0.15, millivolts: 1015},
    {temperature: 1475, percent_carbon: 0.20, millivolts: 1029},
]

lookup = (t, m) => (a |> array.filter(fn: (x) => x.temperature == t and x.millivolts == m))[0]

data = 
    from(bucket: "junkbucket")
        |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
        |> filter(fn: (r) => r["_measurement"] == "Fake")
        |> filter(fn: (r) => r["EquipNumber"] == "A3")
        |> filter(fn: (r) => r["EquipZone"] == "chamber")
        |> filter(fn: (r) => r["MeasType"] == "actual")
        |> filter(fn: (r) => r["_field"] == "temperature" or r["_field"] == "millivolts")
        |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")

data
    |> map(fn: (r) => {
        _lookupData = lookup(t: r.temperature, m: r.millivolts)

        return {r with percent_carbon: _lookupData.percent_carbon}
    })
10

Thank you @scott for patiently explaining. Everything works now exactly as it should. Hopefully this thread will benefit others trying to lookup a value in an array.

To make another Star Wars reference, if you are Obi-Wan Kenobi, then I must be Jar Jar Binks. But all joking aside, I am finding Flux to be my weapon of choice for doing anything complex. It just takes time to learn and wrap one’s mind around the functions, structure, etc.

1 Like