How to use Jsonata on my case?

Hi everyone,

I'm a Node-RED beginner, trying to use it in my company.

On one of my project, i have to get data from a web service and draw a chart from it.
This is what i have.

[
   [
      [
         "01/09/2020",
         "ONE",
         "INFO1",
         1349
      ],
      [
         "02/09/2020",
         "ONE",
         "INFO1",
         1286
      ],
      [
         "03/09/2020",
         "ONE",
         "INFO1",
         1267
      ]
]
]

This json seems to be valid ( Verified on https://jsonformatter.curiousconcept.com/#).

I've learned that i could use Jsonata in this thread and ideally, i want to use it.

First and fourth informations are useful on my case, so i tried some combinations as follow ,

[{
    "series": ["Field1"],
    "labels": ["Field1"],
    "data": $$.[{
        "x": [0],
        "y": [3])
    }]
}]

but it gives me

[
    {
        "series": [
            "Field1"
        ],
        "labels": [
            "Field1"
        ],
        "data": [
            {
                "x": [
                    0
                ],
                "y": [
                    3
                ]
            }
        ]
    }
]

Can please someone show me the way to achieve this ?

Regards,

Pierre

Show us the input data when directed to a debug node (with set to display complete msg). Is the example data coming inside msg.payload?

Hi, thanks for your help.

Json data is on

msg.payload

This is what i have when wiring this to a

debug node

with ouput set to

complete msg object

{"_msgid":"fdde3ae.9080ac8","payload":[[["01/09/2020","ONE","INFO1",1349],["02/09/2020","ONE","INFO1",1286],["03/09/2020","ONE","INFO1",1267]]],"topic":[{"series":"series A","labels":"Label b","data":[{}]}]}

And what would be the desired outcome like?

Sorry I'm going offline for today but I'm just asking the questions likely needed to help with understanding the problem to someone else that might check the topic before I can take a look.

I'll try to summon @janvda who's been keen to answering questions on JSONata. :wink:

1 Like

I might suggest playing with the jsonata exerciser tool when you want to transform data with JSONata. Your use case is a bit particular in the sense that your data is at the end of the day just a nested array (yes, arrays are valid json). I think you will need to iterate over the array to generate an object with the desired format for the chart. Probably JSONata will not be the best option.

Thank you this tool Andrei. :+1:
As per your remark, i did the following and it works like a charm.
I do have a nice graph :slight_smile:

var m={}
m.labels = []
m.series = ['Foo']
m.data = [];
msg.payload.forEach(function (innerarray,index) {
    innerarray.forEach(function (value, index) {
        m.labels.push(value[0]);
        m.data.push(value[3]);
    });
});
return {payload:[m]};

{"payload":[{"labels":["01/09/2020","02/09/2020","03/09/2020"],"series":["Foo"],"data":[1500,1100,800]}],"_msgid":"1afe72d3.c1925d"}

I'm sure i'll find a way to use JSONata one day.
Nodered is such an amazing tool, so much things to learn.
Thank you all for your quick support.

2 Likes

thank you for your quick support and inspiration

Well done ! The code in the function is perfect (tested here). Bookmarked for my own reference.

I know a bit late:

The jsonata solution

The jsonata expression:

{ "payload" : 
    [ 
        { 
          "labels" : payload[0].$[0],
          "series" : [ "Foo"],
          "data"   : payload[0].$[3]
        }
    ]
}
4 Likes

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