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

**URL:** <https://discourse.nodered.org/t/jsonata-in-node-red-not-working-as-for-example-on-try-jasonata-or-in-the-node-red-test-ui/49780>\
**Category:** General\
**Created:** [16 August 2021 09:30 UTC](https://discourse.nodered.org/t/jsonata-in-node-red-not-working-as-for-example-on-try-jasonata-or-in-the-node-red-test-ui/49780 "2021-08-16T09:30:45Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![aschaepper](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/aschaepper/32/35178_2.png) [@aschaepper](https://discourse.nodered.org/u/aschaepper)\
**Post date:** [16 August 2021 09:30 UTC](https://discourse.nodered.org/t/jsonata-in-node-red-not-working-as-for-example-on-try-jasonata-or-in-the-node-red-test-ui/49780/1 "2021-08-16T09:30:45Z")

</div>

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:

```auto
[
    {
        "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:

```auto

[{
"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/](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:

> <https://github.com/node-red/node-red-dashboard/blob/master/Charts.md#line-charts-1>

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.

```auto
[
    {
        "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!

---

<div class="post-metadata">

**Author:** ![E1cid](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/e1cid/32/77971_2.png) [@E1cid](https://discourse.nodered.org/u/E1cid)\
**Post date:** [16 August 2021 09:56 UTC](https://discourse.nodered.org/t/jsonata-in-node-red-not-working-as-for-example-on-try-jasonata-or-in-the-node-red-test-ui/49780/2 "2021-08-16T09:56:43Z")

</div>

Try this

```auto

[{
"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 -

```auto
{
  "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
    }
  ]
}

```

---

<div class="post-metadata">

**Author:** ![aschaepper](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/aschaepper/32/35178_2.png) [@aschaepper](https://discourse.nodered.org/u/aschaepper)\
**Post date:** [16 August 2021 10:56 UTC](https://discourse.nodered.org/t/jsonata-in-node-red-not-working-as-for-example-on-try-jasonata-or-in-the-node-red-test-ui/49780/3 "2021-08-16T10:56:45Z")

</div>

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

Greetings

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [30 August 2021 10:57 UTC](https://discourse.nodered.org/t/jsonata-in-node-red-not-working-as-for-example-on-try-jasonata-or-in-the-node-red-test-ui/49780/4 "2021-08-30T10:57:36Z")

</div>

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