# SQL Statement to extract Data from a nestet JSON Array

**URL:** <https://community.influxdata.com/t/sql-statement-to-extract-data-from-a-nestet-json-array/28064>\
**Category:** Systems\
**Created:** [January 9, 2023, 3:03pm UTC](https://community.influxdata.com/t/sql-statement-to-extract-data-from-a-nestet-json-array/28064 "2023-01-09T15:03:52Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![atbh](https://avatars.discourse-cdn.com/v4/letter/a/9fc29f/32.png) [@atbh](https://community.influxdata.com/u/atbh)\
**Post date:** [January 9, 2023, 3:03pm UTC](https://community.influxdata.com/t/sql-statement-to-extract-data-from-a-nestet-json-array/28064/1 "2023-01-09T15:03:52Z")

</div>

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.

 ![JSON Array](https://us1.discourse-cdn.com/flex023/uploads/influxdata/original/2X/6/6768af8c5a91b856a2ffc1157f70775dbf50f0e2.jpeg)

---

<div class="post-metadata">

**Author:** ![Anaisdg](https://sea1.discourse-cdn.com/flex023/user_avatar/community.influxdata.com/anaisdg/32/6401_2.png) [@Anaisdg](https://community.influxdata.com/u/Anaisdg)\
**Post date:** [January 9, 2023, 8:25pm UTC](https://community.influxdata.com/t/sql-statement-to-extract-data-from-a-nestet-json-array/28064/2 "2023-01-09T20:25:12Z")

</div>

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:

```auto
SELECT <field_key>[,<field_key>,<tag_key>] FROM <measurement_name>[,<measurement_name>]

```

Or for example:

```auto
> 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.

---

<div class="post-metadata">

**Author:** ![atbh](https://avatars.discourse-cdn.com/v4/letter/a/9fc29f/32.png) [@atbh](https://community.influxdata.com/u/atbh)\
**Post date:** [January 10, 2023, 9:00am UTC](https://community.influxdata.com/t/sql-statement-to-extract-data-from-a-nestet-json-array/28064/3 "2023-01-10T09:00:11Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![atbh](https://avatars.discourse-cdn.com/v4/letter/a/9fc29f/32.png) [@atbh](https://community.influxdata.com/u/atbh)\
**Post date:** [January 17, 2023, 9:56am UTC](https://community.influxdata.com/t/sql-statement-to-extract-data-from-a-nestet-json-array/28064/4 "2023-01-17T09:56:22Z")

</div>

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.