Jsonata in Node-Red not working as for example on try.jasonata or in the Node-Red Test UI

General
1

Hello together

I'm currently working with jsonata to transform my SQL Input. I'm now at a point where I'm really lost and have no Idea why its not working.

I have this json file comming from the SQL output node:

[
    {
        "ActivePowerTotal": 0,
        "ValueIndex": 1,
        "EventProcessedUtcTime": "2021-08-12T10:11:01.8773366Z",
        "PartitionId": 0,
        "EventEnqueuedUtcTime": "2021-08-12T10:11:01.7210000Z"
    },
    {
        "ActivePowerTotal": 0,
        "ValueIndex": 10,
        "EventProcessedUtcTime": "2021-08-12T09:26:27.6910525Z",
        "PartitionId": 0,
        "EventEnqueuedUtcTime": "2021-08-12T08:28:56.2060000Z"
    }
]

And I want to transform it with this jsonata:


[{
"series" : ["A"],
"data" : [
    $$.[{
        "ActivePowerTotal" : ActivePowerTotal,
        "EventProcessedUtcTime" : EventProcessedUtcTime
    }]
],
"labels": [""]
}]

Of course, this is just my try to do it. However, I can put this stuff on https://try.jsonata.org/ and see that it works, it also works in the test from the jsonata / modify node. In the Node-Red debug output the structure is there, but the data is not filled.

What I do is basically get some Data from a Azure IoT Hub, Persist it in a SQL Database and then put it to a node-red dashboard chart. According to this github page:

I have to comply with the "series", "data" and "labels" structure. So this is the reason why I would like to have my json look like this and opted for the jsonata possibility in Node-Red.

I also tried out simpler queries, the simplest one was just one variable:
ActivePowerTotal

Doesn't work at all, even if the most samples online which explain jsonata say this should work. There are queries which work in the Node-RED test, but not if I execute the flow. Some doesnt work at all.

Now my question, is the jsonata implementation in Node-Red just bad and I should go for javascript to manipulate it?

I created a sample flow if you would be interessted in testing it / helping out.

[
    {
        "id": "267fb84918afb1f4",
        "type": "tab",
        "label": "Flow 2",
        "disabled": false,
        "info": ""
    },
    {
        "id": "9b9d0364623468cf",
        "type": "inject",
        "z": "267fb84918afb1f4",
        "name": "Inject Sample Data",
        "props": [
            {
                "p": "payload"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "[{\"ActivePowerTotal\":0,\"ValueIndex\":1,\"EventProcessedUtcTime\":\"2021-08-12T10:11:01.8773366Z\",\"PartitionId\":0,\"EventEnqueuedUtcTime\":\"2021-08-12T10:11:01.7210000Z\"},{\"ActivePowerTotal\":0,\"ValueIndex\":10,\"EventProcessedUtcTime\":\"2021-08-12T09:26:27.6910525Z\",\"PartitionId\":0,\"EventEnqueuedUtcTime\":\"2021-08-12T08:28:56.2060000Z\"}]",
        "payloadType": "json",
        "x": 230,
        "y": 100,
        "wires": [
            [
                "e6dc749841dc0df6"
            ]
        ]
    },
    {
        "id": "e6dc749841dc0df6",
        "type": "change",
        "z": "267fb84918afb1f4",
        "name": "Convert to Chart format",
        "rules": [
            {
                "t": "set",
                "p": "payload",
                "pt": "msg",
                "to": "[{\t\"series\" : [\"A\"],\t\"data\" : [\t    $$.[{\t        \"ActivePowerTotal\" : ActivePowerTotal,\t        \"EventProcessedUtcTime\" : EventProcessedUtcTime\t    }]\t],\t\"labels\": [\"\"]\t}]",
                "tot": "jsonata"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 510,
        "y": 100,
        "wires": [
            [
                "3e375e2c8de3bbbd",
                "9cdfef40da59c8c9"
            ]
        ]
    },
    {
        "id": "fa501c7d8d70b735",
        "type": "debug",
        "z": "267fb84918afb1f4",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 1250,
        "y": 100,
        "wires": []
    },
    {
        "id": "3e375e2c8de3bbbd",
        "type": "json",
        "z": "267fb84918afb1f4",
        "name": "Convert to json to prevent debugger ui errors",
        "property": "payload",
        "action": "str",
        "pretty": false,
        "x": 920,
        "y": 100,
        "wires": [
            [
                "fa501c7d8d70b735"
            ]
        ]
    },
    {
        "id": "9cdfef40da59c8c9",
        "type": "debug",
        "z": "267fb84918afb1f4",
        "name": "",
        "active": true,
        "tosidebar": true,
        "console": false,
        "tostatus": false,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "",
        "statusType": "auto",
        "x": 1250,
        "y": 180,
        "wires": []
    }
]

I'm on nodered 2.0.5 on a azure ubuntu machine.

Thanks for you help!

2

Try this


[{
"series" : ["A"],
"data" : [
    [$$.payload.{
        "y" : $.ActivePowerTotal,
        "x" : $.EventProcessedUtcTime
    }]
],
"labels": [""]
}]

$$ will reference the msg which is an object, you require payload which is an array in that object.
Also when you surround a mapped array with [ ] , it will force the array if only one output, so you need to add another [ ] around the expression, as first is taken to force array.
It is good practice use $ to reference each scope of the map loop.
The json you should of used in the JSONata Exerciser is -

{
  "payload": [
    {
      "ActivePowerTotal": 0,
      "EventEnqueuedUtcTime": "2021-08-12T10:11:01.7210000Z",
      "EventProcessedUtcTime": "2021-08-12T10:11:01.8773366Z",
      "PartitionId": 0,
      "ValueIndex": 1
    },
    {
      "ActivePowerTotal": 0,
      "EventEnqueuedUtcTime": "2021-08-12T08:28:56.2060000Z",
      "EventProcessedUtcTime": "2021-08-12T09:26:27.6910525Z",
      "PartitionId": 0,
      "ValueIndex": 10
    }
  ]
}
2 Likes
3

Amazing it works! Thanks a lot for your explanation, makes total sense but wasnt that obvious to me in the first place.

Greetings

4

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