I have an influxdb database getting data from an iobroker datapoint.
I do get results as a nested JSON Array by appliing the following
select * from “
I do get the result see attachment.
My question is, how has a sql command look like to receive f.e. only the value of EnergyPToday0, of course the * inside the statement has to be modified, but how is the syntax.
Tried several things nothing is working, help would be great.
Hello @atbh,
Welcome! I’m not sure. I’m a little confused what your question is.
If you’re asking how to retrieve only one field you can do the following:
SELECT <field_key>[,<field_key>,<tag_key>] FROM <measurement_name>[,<measurement_name>]
Or for example:
> SELECT "water_level" FROM "h2o_feet" WHERE time = '2016-07-31T20:07:00Z' OR time = '2016-07-31T23:07:17Z'
where water_level is the field and h20_feet is the measurement.
Does that help?
So you might want something like.
select “EnergypToday0” from "mqtt…
/?
What version of InfluxDB are you using? Your output looks odd to me. Thanks.
Thanks for help … of course Your proposal would be a normal sql statement, but please take into consideration, the measurement is a nested JSON Array like
{"ENERGY":"EnergyPToday0":1.197,"EnergyMToday0":0.000,"EnergyPYesterday0":0.468,"EnergyMYesterday0":0.000,"EnergyPTotal0":228.902,"EnergyMTotal0":0.000,"PowerPToday0":767.8,"PowerMToday0":0.0,"PowerPYesterday0":972.8,"PowerMYesterday0":0.0,"PowerPTotal0":3501.9,"PowerMTotal0":0.0,"Current0":1.269,"Power0":222.3,"Voltage":236.31},"AIR":{"Temp":null,"Humi":null,"Baro":null,"Gas":null,"TempDew":null}
therefore to use a normal field operator won’t work. Somehow it has to have a special form, as I mentioned I was trying several things like
Select $.ENERGY.EnergyToday0 from “mqtt.0. …SENSOR” resulting in an empty set
Don’t know how a statement should look like, to access fields inside the { … JSON Array … }
Feel free to ask, finding the solution would be really great form me.
Thanks
Solved:
I found a solution just by picing my corresponding value out of the JSON array and writing this value directly into a new datapoint inside the iobroker. I’ve created this with JavaScript inside the iobroker.
Thanks for helping.
