Get JSON data from influxdb 2.0

InfluxDB 2
, , ,
1

I am using python language for getting data. I am following documentation as mentioned in UI and documentation as well. I am trying to get json data without manual loop perform by python.
Here is an example -
If measurement - test
fields are f1,f2 and values are 10, 20 and 50, 60 respectively.
I want data like -
[{“time”:, “f1”:10, “f2”:20},{“time”:, “f1”:50, “f2”:60}]
I am using -
|> map(fn: (r) => ({ r with
jsonStr: string(v: json.encode(v: {“OriginalTime”:r.OriginalTime,“time”:r._time}))}))
as mentioned in documentation. but its not working.
Is there any way to get data like my desired json output by FluxQL or Python(Without loop)?
It will be helpful for me.

Just FYI - I got an email from Harrison(InfluxDB Data) for feed back call. I replied but no one get back.

2

Welcome to the forum.

Your post is bit hard to read from the formatting, can you edit it and put the code inside a code block </>

Yes. Pretty much any language can query from influxdb using a standard web rest api call.

  1. From python you’d declare something to hold the result, either an array, reference or list object.
  2. You construct the flux query similar to what you’ve already got.
  3. You then send the http request to the influxdb , which you’ll need to create a token for your script to use.
  4. The result you get back (should be http return code 200) will contain a json response.
    Most languages now have a built in json parser that will convert it into a datastructure that’s native for that language.
  5. And away you go from there.

There’s even a python library that abstracts these most of these details away too, https://docs.influxdata.com/influxdb/cloud/api-guide/client-libraries/python/

Paste the code you’ve written so far, and what errors you’re getting and someone may be able to spot what’s up.

3

Hi,
Thank you for the reply.

But my concern, is that possible to get json data directly from influxdb using flux language(without any http post) or python (without any loop).

My Python Code is like -

from db.db_conf import INFLUX_CLIENT
from conf.settings import DB_ORG

query = 'from(bucket: "SENSOR_DB") |> range(start: 2020-06-30T15:13:17.056Z, stop: 2021-06-30T16:13:17.056Z) |> filter(fn: (r) => r["_measurement"] == "sensor_data_4512545") |> yield()'

result = INFLUX_CLIENT.query_api().query(query, org=DB_ORG)

print(result)

Result that im getting is -

image
image913×226 8.75 KB

Where as influxdb 1.8 using influxql and Python influxdb library query like -

query = 'select * from sensor_data_456212'
result = client.query(query)
print(list(result))

I was getting like -
[{‘time’: ‘2021-05-03 10:05:10’,‘t1’:9},{‘time’: ‘2021-05-03 10:09:15’,‘t1’:50}]

So, i am expecting the same from Flux query and Python influxdb client as suggested.

Is it possible to get data like above json structure?

4

If its not understandable from above reply. Can we arrange a short call, so that i can show you what im facing.

5

@debnath no, the Flux query system returns results as formatted CSV, which the influxdb-client-python library then parses into FluxTable objects. If you need to pass results as JSON to some other system, you’ll need to convert formats in your code after receiving the response.

6

There are some examples of using the different formats here: GitHub - influxdata/influxdb-client-python: InfluxDB 2.0 python client

7

Thank you @dan-moran . Appreciated.

I have a little request that, if in future flux can export to json directly from influxdb without using http post, please let me know.

Because if i looped it from object to json or csv to json in python, it will be overhead for the application. Im building a restapi in flux which only return json will be use in our IOT platform where milisec. latency will cause trouble.

8

Hi @dan-moran ,

I came up with this solution which meets my requirements.

Here is the code which is giving me a json response with python library -

from db.db_conf import INFLUX_CLIENT
from conf.settings import DB_ORG
import pandas as pd

query = '''
from(bucket: "SENSOR_DB") 
    |> range(start: 2020-06-30T15:13:17.056Z, stop: 2021-06-30T16:13:17.056Z) 
    |> filter(fn: (r) => r["_measurement"] == "sensor_data_fb9a8") 
    |> keep(columns: ["_time", "_field", "_value"])
    |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value") 
    |> yield()
    '''
df = INFLUX_CLIENT.query_api().query_data_frame(query, org=DB_ORG)
print(df.to_json(orient='records'))

Which is giving result like -

[{
	"_time": 1614253728439,
	"OriginalTime": 1614253719434,
	"assetId": "0",
	"featureName": "testFeature",
	"sensorId": "601804",
	"sensortest01": "50",
	"sensortest02": "50"
}, {
	"_time": 1614254044553,
	"OriginalTime": 1614254035648,
	"assetId": "0",
	"featureName": "testFeature",
	"sensorId": "601805",
	"sensortest01": "jhghj",
	"sensortest02": "ghjgjgh"
}]

It might help others who are looking for this kinf of solution.

1 Like