1

My data looks something like this --

enter image description here

I want to create a FLUX query which results into --

SELECT UUID, CUST_ID, PO_NO, TIMESTAMP FROM CUST_PO_DTLS WHERE PO_NO = ?

In my Influx DB bucket, UUID, CUST_ID, PO_NO are all fields.

Here, I explicitly mention the columns and need those columns for further processing.

So far, I could not find a way to select all fields but filter with one fields.

Here is my flux query --

import "influxdata/influxdb/schema"
from(bucket: "sap")
  |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
  |> filter(fn: (r) =>
    r._measurement == "cpd"
  )
  |> filter(fn: (r) => r["_field"] =~ /^(UUID|CUST_ID|PO_NO| STATUS)$/)
  |> filter(fn: (r) => r["_value"] =="PO_1")
  |> schema.fieldsAsCols()

However, I only get 1 column returned with above query (PO_NO). How do I still get the remaining columns for my processing ?

Rahul Bhardwaj
  • 2,303
  • 1
  • 17
  • 28

1 Answers1

2
  |> filter(fn: (r) => r["_field"] =~ /^(UUID|CUST_ID|PO_NO| STATUS)$/)
  |> filter(fn: (r) => r["_value"] =="PO_1")

These two lines are self-conflicting. It's trying to have 4 specified fields in the resultset but the next line restricts the field has to be "PO_NO" because only field "PO_NO" has the value of "PO_1".

You might need to modify the schema a little bit. That is, move "PO_NO" from field into tag.

New query could be:

import "influxdata/influxdb/schema"
from(bucket: "sap")
  |> range(start: v.timeRangeStart, stop:v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "cpd")
  |> filter(fn: (r) => r.PO_NO== "PO_1")
  |> filter(fn: (r) => r["_field"] =~ /^(UUID|CUST_ID|STATUS)$/)
  |> schema.fieldsAsCols()

btw, looks like you are using InfluxDB for traditional Purchase Order query. This might not be the best database for you as InfluxDB deals with the time-series data the best. Why not just use traditional RDBMS?

Munin
  • 1,576
  • 2
  • 19
  • thank you for the response. However, the above query returns just 1 result and not 2 as expected for PO_1. We have multiple use case for InfluxDB but need transactional data just for some UI. – Rahul Bhardwaj Sep 20 '22 at 05:18