Equivalent for SELECT <certain columns> in Flux langauage

Hello,
I have started to use Influxdb 2.0 and read Flux documentation that is avalable. But I am stuck on what would be equivalent flux query for SELECT username FROM Planning ?
Below is my measurement data csv equivalent content:

#datatype measurement,tag,tag,tag,tag,double,double
Planning,Month,SprintName,Duration,username,Planned_Est_Efforts,Expected_Est_Efforts
Planning,Jun,Sprint 1,2 Weeks,abcd,64.23,72
Planning,Jun,Sprint 1,2 Weeks,xyz,65,72

In the flux Documentation present filter() function is the equivalent of both SELECT and WHERE clauses, but examples given are equivalent of WHERE clauses, and I could not find on SELECT .
Can please some one tell me how can i extract values of a particular field. In this example : I would like to get all values of username.

Query :

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

Thank You in Advance!

Hello @altafs1,

Simply add and “and” to your filter:

from(bucket: "Sprint")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "Planning" and  r["username"] == "abcd"  )
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")

or pipe forward to a second filter. These are equivalent:

from(bucket: "Sprint")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "Planning")
  |> filter(fn: (r) =>  r["username"] == "abcd"  )
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")

Hello @Anaisdg
Thank You for yoour reply.I want end output to display all usernames. I dont want to put any condition or restrict to a particular user “abcd”.Please can you tell me how can i display all usernames only.
End output expected:
abcd
xyz

Hello @altafs1,
So you want to list all of the values for username?

Hello @Anaisdg ,
Yes correct. Please can you let me know, how can I write a query for it

Thank you

Hello @altafs1
You can list all tag values like so:
https://docs.influxdata.com/influxdb/cloud/query-data/flux/explore-schema/#list-tag-values

You can also filter with regex:

from(bucket: "Sprint")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "Planning")
  |> filter(fn: (r) =>  r["username"]  =~ /.*/)
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> yield(name: "mean")

https://docs.influxdata.com/influxdb/cloud/query-data/flux/regular-expressions/#use-a-regex-to-filter-by-tag-value

Hello @Anaisdg ,

I just want to get only values of “username”. I do not want to see other fields/values etc…

Example:

SELECT username FROM Planning
 username     
--------------
 abcd
 xyz

How can i achieve the above output only from flux.I do not want to see any other values or fields like Month, Duration, etc…
With the query that you gave it gives all other fields also which i do not want.I just want to see output as uername and all ther username values.

Thank you.

If username is a regular field you can try

|> filter(fn: (r) => r["_field"] == "username" )

You can also have a look at keep() function. But filtering first should be more efficient than throwing away later.

Hello @ypnos ,
I have username as a tag name. Please see below csv format:

#datatype measurement,tag,tag,tag,tag,double,double
Planning,Month,SprintName,Duration,username,Planned_Est_Efforts,Expected_Est_Efforts
Planning,Jun,Sprint 1,2 Weeks,abcd,64.23,72
Planning,Jun,Sprint 1,2 Weeks,xyz,65,72

I would just like to have an end output as :

 username     
--------------
 abcd
 xyz

I would like have a variable named names and inside that i would like to have a flux query which executes and return all usernames.
I am just not able to see how can i extract particular field values.
Any inputs would be helpful.
Thanks

Hello ,
I made some progress and have added below query and it outputs data:

from(bucket: "Sprint")
  |> range(start: -10h)
  |> filter(fn: (r) => r["_measurement"] == "Estimate")
  |> filter(fn: (r) => r["_field"] == "username" )
  |> keep(columns: ["_value"])
  |> yield(name: "mean")

csv data:

#datatype measurement,tag,tag,tag,string,double,double
Estimate,Month,SprintName,Duration,username,Planned_Est_Efforts,Expected_Est_Efforts
Estimate,Jun,Sprint 1,2 Weeks,abc,64.23,72
Estimate,Jun,Sprint 1,2 Weeks,def,72.01,72
Estimate,Jun,Sprint 1,2 Weeks,xyz,73.77,72

It produces the below output:

_value:string
----------------------
                xyz

But the above output does not display other usernames like abc, def.It is just displaying the last value
Please can anyone tell me what I am missing here.

Thanks in Advance!

Hello @altafs1 have you tried schema.tagValues() function shared above?

Thank you that works

1 Like