Extract specific values from nested JSON array from Influxv2 query

General
1

First time poster so be gentle! I have scoured Google and this forum for similar issues as me but to no avail. I have had similar problems with nested JSON in the past but have worked around it because I couldn't find an answer but this time I can neither find a workaround nor an answer! Closest was this post.

I have an influx db (v2.0) which I am connected to and am trying to display the last 7 days worth of data in a line graph on a nodered dashboard. I know that Influx has a dash but I am trying to use nodered alongside it for various reasons. I had attempted to use the 'Persist' node to store data as it is collected in nodered but I kept failing to make it work. Irrespective, this issue of extracting specific values in nested JSON arrays, sometimes of variable length, keeps popping up in a variety of contexts (no pun intended!).

The image below is of the output I have from my influx query. I have a single array, which contains, 909 objects, subdivided into arrays of 9. In each object there is a key:value pair of '_value' and it is this (and ideally the '_time' key) that I would like to obtain and plot.

I have tried a variety of things from the answers of similar, but fundamentally different, threads here and have also tried writing some javascript in a function node. The problem is I don't know Javascript and am using nodered because of it's lowcode offering. Nonetheless, I am now of the belief that I need some clever javascript to help me and here I am, just a girl, sitting in front of a laptop, asking for someone to help me.

image
image432Ă—769 10.2 KB

2

Hi and welcome.

A change node with the following JSONata expression should work, untested as you did not supply any copyable data to test.

[
   {
       "labels": ["a name"],
       "series": ["a name"],
       "data":[
           [
               $$.payload[0].{"x": $._time, "y":$._value}
           ]
       ]
   }
]

this should produce a array for the dashboard chart node.

And Javascript would be

msg.payload =[
    {
        labels:["a name"],
        series:["a name"],
        data:[
            msg.payload[0].map(obj => {
                return {x: obj._time, y: obj._value}
            })
        ]
    }
]
return msg;
3

That worked!!! The JSONata expression didn't work but the fucntion did. Thank you ever so much!
Out of curiosity, for next time, how do I go about supplying copyable data...or maybe I don't understand your meaning...?

4

The JSONata had a typo it referenced _time twice, when it should have been _value for one of the references. I have fixed it in above post, please check as it may help others.

Use the debug sidebar, when you hover to right of object name, you will see some icon/buttons appear, use the copy value next to the object name to copy that object. It will now be in your clipboard.
Now if it is 900 elements long, you may wish to edit the value to say 5 elements. You then paste using the </> button, paste the code between the backticks where it says paste code here.

5

Yes, this now works, thank you again!
Also thank you for the tip re providing the values. I have attempted this below.
Not sure how many objects I have pasted here :smiley:

[{"result":"last","table":"0","_start":"2022-07-27T17:00:08.427Z","_stop":"2022-08-03T17:00:08.427Z","_time":"2022-07-28T08:10:00.000Z","_value":74,"_field":"fields_General_0_Part Count","_measurement":"mqtt_consumer","host":"ff3e54e10679","topic":"NITC/WS1/IndexG220_test"},{"result":"last","table":"0","_start":"2022-07-27T17:00:08.427Z","_stop":"2022-08-03T17:00:08.427Z","_time":"2022-07-28T08:20:00.000Z","_value":74,"_field":"fields_General_0_Part Count","_measurement":"mqtt_consumer","host":"ff3e54e10679","topic":"NITC/WS1/IndexG220_test"},{"result":"last","table":"0","_start":"2022-07-27T17:00:08.427Z","_stop":"2022-08-03T17:00:08.427Z","_time":"2022-07-28T08:30:00.000Z","_value":74,"_field":"fields_General_0_Part Count","_measurement":"mqtt_consumer","host":"ff3e54e10679","topic":"nope"},{"result":"last","table":"0","_start":"2022-07-27T17:00:08.427Z","_stop":"2022-08-03T17:00:08.427Z","_time":"2022-07-28T08:40:00.000Z","_value":74,"_field":"fields_General_0_Part Count","_measurement":"mqtt_consumer","host":"ff3e54e10679","topic":"NITC/WS1/IndexG220_test"},{"result":"last","table":"0","_start":"2022-07-27T17:00:08.427Z","_stop":"2022-08-03T17:00:08.427Z","_time":"2022-07-28T08:50:00.000Z","_value":74,"_field":"fields_General_0_Part Count","_measurement":"mqtt_consumer","host":"ff3e54e10679","topic":"NITC/WS1/IndexG220_test"},{"result":"last","table":"0","_start":"2022-07-27T17:00:08.427Z","_stop":"2022-08-03T17:00:08.427Z","_time":"2022-07-28T09:00:00.000Z","_value":74,"_field":"fields_General_0_Part Count","_measurement":"mqtt_consumer","host":"ff3e54e10679","topic":"NITC/WS1/IndexG220_test"},{"result":"last","table":"0","_start":"2022-07-27T17:00:08.427Z","_stop":"2022-08-03T17:00:08.427Z","_time":"2022-07-28T09:10:00.000Z","_value":74,"_field":"fields_General_0_Part Count","_measurement":"mqtt_consumer","host":"ff3e54e10679","topic":"NITC/WS1/IndexG220_test"},{"result":"last","table":"0","_start":"2022-07-27T17:00:08.427Z","_stop":"2022-08-03T17:00:08.427Z","_time":"2022-07-28T09:20:00.000Z","_value":74,"_field":"fields_General_0_Part Count","_measurement":"mqtt_consumer","host":"ff3e54e10679","topic":"NITC/WS1/IndexG220_test"},{"result":"last","table":"0","_start":"2022-07-27T17:00:08.427Z","_stop":"2022-08-03T17:00:08.427Z","_time":"2022-07-28T09:30:00.000Z","_value":74,"_field":"fields_General_0_Part Count","_measurement":"mqtt_consumer","host":"ff3e54e10679","topic":"NITC/WS1/IndexG220_test"},{"result":"last","table":"0","_start":"2022-07-27T17:00:08.427Z","_stop":"2022-08-03T17:00:08.427Z","_time":"2022-07-28T09:40:00.000Z","_value":74,"_field":"fields_General_0_Part Count","_measurement":"mqtt_consumer","host":"ff3e54e10679","topic":"NITC/WS1/IndexG220_test"},{"result":"last","table":"0","_start":"2022-07-27T17:00:08.427Z","_stop":"2022-08-03T17:00:08.427Z","_time":"2022-07-28T09:50:00.000Z","_value":74,"_field":"fields_General_0_Part Count","_measurement":"mqtt_consumer","host":"ff3e54e10679","topic":"NITC/WS1/IndexG220_test"},{"result":"last","table":"0","_start":"2022-07-27T17:00:08.427Z","_stop":"2022-08-03T17:00:08.427Z","_time":"2022-07-28T10:00:00.000Z","_value":74,"_field":"fields_General_0_Part Count","_measurement":"mqtt_consumer","host":"ff3e54e10679","topic":"NITC/WS1/IndexG220_test"},{"result":"last","table":"0","_start":"2022-07-27T17:00:08.427Z","_stop":"2022-08-03T17:00:08.427Z","_time":"2022-07-28T10:10:00.000Z","_value":74,"_field":"fields_General_0_Part Count","_measurement":"mqtt_consumer","host":"ff3e54e10679","topic":"NITC/WS1/IndexG220_test"},{"result":"last","table":"0","_start":"2022-07-27T17:00:08.427Z","_stop":"2022-08-03T17:00:08.427Z","_time":"2022-07-28T10:20:00.000Z","_value":74,"_field":"fields_General_0_Part Count","_measurement":"mqtt_consumer","host":"ff3e54e10679","topic":"NITC/WS1/IndexG220_test"},{"result":"last","table":"0","_start":"2022-07-27T17:00:08.427Z","_stop":"2022-08-03T17:00:08.427Z","_time":"2022-07-28T10:30:00.000Z","_value":74,"_field":"fields_General_0_Part Count","_measurement":"mqtt_consumer","host":"ff3e54e10679","topic":"NITC/WS1/IndexG220_test"},{"result":"last","table":"0","_start":"2022-07-27T17:00:08.427Z","_stop":"2022-08-03T17:00:08.427Z","_time":"2022-07-28T10:40:00.000Z","_value":0,"_field":"fields_General_0_Part Count","_measurement":"mqtt_consumer","host":"ff3e54e10679","topic":"NITC/WS1/IndexG220_test"},{"result":"last","table":"0","_start":"2022-07-27T17:00:08.427Z","_stop":"2022-08-03T17:00:08.427Z","_time":"2022-07-28T10:50:00.000Z","_value":0,"_field":"fields_General_0_Part Count","_measurement":"mqtt_consumer","host":"ff3e54e10679","topic":"NITC/WS1/IndexG220_test"},{"result":"last","table":"0","_start":"2022-07-27T17:00:08.427Z","_stop":"2022-08-03T17:00:08.427Z","_time":"2022-07-28T11:00:00.000Z","_value":0,"_field":"fields_General_0_Part Count","_measurement":"mqtt_consumer","host":"ff3e54e10679","topic":"NITC/WS1/IndexG220_test"},{"result":"last","table":"0","_start":"2022-07-27T17:00:08.427Z","_stop":"2022-08-03T17:00:08.427Z","_time":"2022-07-28T11:10:00.000Z","_value":0,"_field":"fields_General_0_Part Count","_measurement":"mqtt_consumer","host":"ff3e54e10679","topic":"NITC/WS1/IndexG220_test"},{"result":"last","table":"0","_start":"2022-07-27T17:00:08.427Z","_stop":"2022-08-03T17:00:08.427Z","_time":"2022-07-28T11:20:00.000Z","_value":1,"_field":"fields_General_0_Part Count","_measurement":"mqtt_consumer","host":"ff3e54e10679","topic":"NITC/WS1/IndexG220_test"},{"result":"last","table":"0","_start":"2022-07-27T17:00:08.427Z","_stop":"2022-08-03T17:00:08.427Z","_time":"2022-07-28T11:30:00.000Z","_value":1,"_field":"fields_General_0_Part Count","_measurement":"mqtt_consumer","host":"ff3e54e10679","topic":"NITC/WS1/IndexG220_test"},{"result":"last","table":"0","_start":"2022-07-27T17:00:08.427Z","_stop":"2022-08-03T17:00:08.427Z","_time":"2022-07-28T11:40:00.000Z","_value":2,"_field":"fields_General_0_Part Count","_measurement":"mqtt_consumer","host":"ff3e54e10679","topic":"NITC/WS1/IndexG220_test"},{"result":"last","table":"0","_start":"2022-07-27T17:00:08.427Z","_stop":"2022-08-03T17:00:08.427Z","_time":"2022-07-28T11:50:00.000Z","_value":3,"_field":"fields_General_0_Part Count","_measurement":"mqtt_consumer","host":"ff3e54e10679","topic":"NITC/WS1/IndexG220_test"},{"result":"last","table":"0","_start":"2022-07-27T17:00:08.427Z","_stop":"2022-08-03T17:00:08.427Z","_time":"2022-07-28T12:00:00.000Z","_value":3,"_field":"fields_General_0_Part Count","_measurement":"mqtt_consumer","host":"ff3e54e10679","topic":"NITC/WS1/IndexG220_test"},{"result":"last","table":"0","_start":"2022-07-27T17:00:08.427Z","_stop":"2022-08-03T17:00:08.427Z","_time":"2022-07-28T12:10:00.000Z","_value":3,"_field":"fields_General_0_Part Count","_measurement":"mqtt_consumer","host":"ff3e54e10679","topic":"NITC/WS1/IndexG220_test"}]
6

That's about 30 element, better than 900.
You seem to have copied msg.payload[0] not msg.payload.
Your payload array should look like this, if only 4 elements.

[
   [
       {
           "result":"last",
           "table":"0",
           "_start":"2022-07-27T17:00:08.427Z",
           "_stop":"2022-08-03T17:00:08.427Z",
           "_time":"2022-07-28T08:10:00.000Z",
           "_value":74,
           "_field":"fields_General_0_Part Count",
           "_measurement":"mqtt_consumer",
           "host":"ff3e54e10679",
           "topic":"NITC/WS1/IndexG220_test"
   },
       {
           "result":"last",
           "table":"0",
           "_start":"2022-07-27T17:00:08.427Z",
           "_stop":"2022-08-03T17:00:08.427Z",
           "_time":"2022-07-28T08:20:00.000Z",
           "_value":74,
           "_field":"fields_General_0_Part Count",
           "_measurement":"mqtt_consumer",
           "host":"ff3e54e10679",
           "topic":"NITC/WS1/IndexG220_test"
   },
       {
           "result":"last",
           "table":"0",
           "_start":"2022-07-27T17:00:08.427Z",
           "_stop":"2022-08-03T17:00:08.427Z",
           "_time":"2022-07-28T08:30:00.000Z",
           "_value":74,
           "_field":"fields_General_0_Part Count",
           "_measurement":"mqtt_consumer",
           "host":"ff3e54e10679",
           "topic":"nope"
   },
       {
           "result":"last",
           "table":"0",
           "_start":"2022-07-27T17:00:08.427Z",
           "_stop":"2022-08-03T17:00:08.427Z",
           "_time":"2022-07-28T08:40:00.000Z",
           "_value":74,
           "_field":"fields_General_0_Part Count",
           "_measurement":"mqtt_consumer",
           "host":"ff3e54e10679",
           "topic":"NITC/WS1/IndexG220_test"
      }
   ]
]

I would suggest you read the docs as there is plenty of info there that will make your life easier using Node-red, also watch the videos, as they to are informative. Well worth the time.

7

Man I feel like such a donut! I have read docs and watched many a video but still learning, thank you for your patience! I think my missing link here is basic knowledge of javascript for execution in nodered but for here it's experience of posting in forums. I have no idea how you got that printed out so pretty unless you manually did it...which again, is a thanks for your patience :smiley:

8

i pasted it in a change node using the json {} option then i used the format json button top right, which will pretty a valid json string.

Javascript is not that important with node-red, but some understanding will help. The important thing is to understand how node-red sends messages, and an understanding of javascript objects and json string, which are similar but not the same thing.

1 Like
9

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.