SQL Statement to extract Data from a nestet JSON Array

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

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.

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.