How to filter records by any specific field value and to apply aggregation function to other fields of filtered records?


I am trying to query inflxudb on the basis of a field value but in the output, it drops other fields values.

from(bucket: "db1/autogen")
  |> range(start: dashboardTime)
  |> filter(fn: (r) => 
  	r._measurement == "trade" and 
    r._field == "orderId" and 
    r._value == "54324")
  |> yield()

In the output, I see only orderId field along with tags. How can I get all other fields of filtered records?


You’ll have to remove the r._field == "orderId" predicate. Each _field key-value pair in InfluxDB is stored in an individual “record” or “row.” A record can have multiple tags, but it only ever has one _field and one _value. You can have multiple records with the same timestamp, each for a different field.

I think in this case, orderId would be better as a tag rather than a field. Especially if you have multiple fields associated with each orderId. The downside here is that this will increase series cardinality, but that may not be a big issue.


@Digvijay_Richhariya I think that you may find the pivot function useful:

  |> range(start: dashboardTime)
  |> filter(fn: (r) => r._measurement == "trade") 
  |> pivot(rowKey: ["_time"], columnKey: ["_field"], valueColumn: "_value")

take a look at that output and make sure you understand it, then you should be able to add:
filter(fn: (r) => r.orderID == "54324")

this will line up your results similarly to how they were written in.


@scott Thanks, it will help me to re-think about my current message structure. I am not very comfortable in making orderId as tag as it’s unique for each order and we are having hardly 3-5 events for each order.


Thanks @aanthony1243, I will surely try this.