Union two series and force to keep the value from the first one when value exists in both

[sboris]

Hi,
I stuck with the problem of combining two datasets when there is no key field for joining. Union goes well but in the case of existence the same records in both datasets (in bold) I need to keep data from “mei” measurement.

I have two measurements:
meu,host=host1 used_percent=164.23 1606711580000
meu,host=host1 used_percent=172.01 1606711595000
meu,host=host1 used_percent=162.61 1606711599000
meu,host=host1 used_percent=172.98 1606711620000
meu,host=host1 used_percent=163.40 1606711950000
meu,host=host1 used_percent=173.77 1606711581000

mei,host=host0 used_percent=64.23 1606711580000
mei,host=host0 used_percent=72.01 1606711585000
mei,host=host0 used_percent=62.61 1606711589000
mei,host=host0 used_percent=72.98 1606711600000
mei,host=host0 used_percent=63.40 1606711900000
mei,host=host0 used_percent=73.77 1606711581000

With query of:
table1 = from(bucket: “test”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: ® => r._measurement == “meu”)

table2 = from(bucket: “test”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: ® => r._measurement == “mei”)

union(tables: [table2, table1])
|> set(key: “_measurement”, value: “mei”)
|> set(key: “host”, value: “host0”)
|> to(bucket: “test”, org: “SS”, timeColumn: “_time”)

Got the table:

_time _value _field _measurement host
2020-11-30T04:46:20Z 164.23 used_percent mei host0
…
2020-11-30T04:52:30Z 163.4 used_percent mei host0

It’s expected to keep values from “mei”, instead I’ve got values from “meu” in case when the data exists in both series.

[Anaisdg]

Hello @sboris,
Do you not get values for “meu” and “mei”?

Can you try this for me?

import "experimental/array"

table1 = array.from(rows: [
  {_time: 2020-01-01T00:00:00Z, _measurement:"meu", _field: "exampleField", _value: 3, foo: "bar"},
  {_time: 2020-01-01T00:01:00Z, _measurement:"meu", _field: "exampleField", _value: 4, foo: "bar"},
  {_time: 2020-01-01T00:02:00Z, _measurement:"meu", _field: "exampleField", _value: 1, foo: "bar"}
])

table2 = array.from(rows: [
  {_time: 2020-01-01T00:00:00Z, _measurement:"mie", _field: "exampleField", _value: 3, foo: "bar"},
  {_time: 2020-01-01T00:01:00Z, _measurement:"mie", _field: "exampleField", _value: 4, foo: "bar"},
  {_time: 2020-01-01T00:02:00Z, _measurement:"mie",  _field: "exampleField", _value: 1, foo: "bar"}
])


union(tables: [table1, table2]) 

With that example I have data with the same timestamps and different measurements and the same field and this is my output: