Puzzling JSONATA problem in Change Node

I am trying to change values in an array, inside a JSON-object acquired from an API.

The JSON is formatted like so:

{
  "records": [
    {
      "HourUTC": "2022-11-14T20:00:00",
      "SpotPriceDKK": 1039.25
    },
    {
      "HourUTC": "2022-11-14T21:00:00",
      "SpotPriceDKK": 1172.170044
    }
  ]
}

I simply want to divide all "SpotPriceDKK" values with 1000. Preferably with JSONATA inside the Change Node.

Having tried countless methods for countless hours with no success I now turn to the community - that's you :wink:

Trying to write the result of a valid(?) JSONATA query back to the object/array either erases the array or does nothing.

I have tested the query in the query editor and with JSONata Exerciser with the expected result, but it does not work in the Change-Node. I have no idea what to do.

Flow example:

[
    {
        "id": "80adc07bb87a9ab7",
        "type": "change",
        "z": "b63989b9c24c3d01",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "payload.records.SpotPriceDKK",
                "pt": "msg",
                "to": "records.(SpotPriceDKK/1000)",
                "tot": "jsonata"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 880,
        "y": 240,
        "wires": [
            [
                "33c6fd6571704f46"
            ]
        ]
    },
    {
        "id": "33c6fd6571704f46",
        "type": "debug",
        "z": "b63989b9c24c3d01",
        "name": "CHANGE SpotPriceDKK ONLY payload",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": true,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "payload",
        "statusType": "auto",
        "x": 1280,
        "y": 240,
        "wires": []
    },
    {
        "id": "272666c87d930046",
        "type": "change",
        "z": "b63989b9c24c3d01",
        "name": "",
        "rules": [
            {
                "t": "set",
                "p": "payload.records",
                "pt": "msg",
                "to": "records.{\"HourUTC\": HourUTC, \"SpotPriceDKK\": (SpotPriceDKK / 1000) }",
                "tot": "jsonata"
            }
        ],
        "action": "",
        "property": "",
        "from": "",
        "to": "",
        "reg": false,
        "x": 830,
        "y": 80,
        "wires": [
            [
                "243b1854f0f67142"
            ]
        ]
    },
    {
        "id": "bb7f5f9af2a00fea",
        "type": "inject",
        "z": "b63989b9c24c3d01",
        "name": "",
        "props": [
            {
                "p": "payload"
            }
        ],
        "repeat": "",
        "crontab": "",
        "once": false,
        "onceDelay": 0.1,
        "topic": "",
        "payload": "{\"total\":24,\"filters\":\"{\\\"PriceArea\\\":[\\\"DK1\\\"]}\",\"sort\":\"HourUTC asc\",\"dataset\":\"Elspotprices\",\"records\":[{\"HourUTC\":\"2022-11-14T22:00:00\",\"SpotPriceDKK\":1097.709961},{\"HourUTC\":\"2022-11-14T23:00:00\",\"SpotPriceDKK\":1013.090027},{\"HourUTC\":\"2022-11-15T00:00:00\",\"SpotPriceDKK\":958.710022},{\"HourUTC\":\"2022-11-15T01:00:00\",\"SpotPriceDKK\":850.039978},{\"HourUTC\":\"2022-11-15T02:00:00\",\"SpotPriceDKK\":728.280029},{\"HourUTC\":\"2022-11-15T03:00:00\",\"SpotPriceDKK\":412.970001},{\"HourUTC\":\"2022-11-15T04:00:00\",\"SpotPriceDKK\":230.960007},{\"HourUTC\":\"2022-11-15T05:00:00\",\"SpotPriceDKK\":938.409973},{\"HourUTC\":\"2022-11-15T06:00:00\",\"SpotPriceDKK\":815.75},{\"HourUTC\":\"2022-11-15T07:00:00\",\"SpotPriceDKK\":932.309998},{\"HourUTC\":\"2022-11-15T08:00:00\",\"SpotPriceDKK\":729.02002},{\"HourUTC\":\"2022-11-15T09:00:00\",\"SpotPriceDKK\":765.320007},{\"HourUTC\":\"2022-11-15T10:00:00\",\"SpotPriceDKK\":707.369995},{\"HourUTC\":\"2022-11-15T11:00:00\",\"SpotPriceDKK\":665.27002},{\"HourUTC\":\"2022-11-15T12:00:00\",\"SpotPriceDKK\":624.590027},{\"HourUTC\":\"2022-11-15T13:00:00\",\"SpotPriceDKK\":616.030029},{\"HourUTC\":\"2022-11-15T14:00:00\",\"SpotPriceDKK\":519.559998},{\"HourUTC\":\"2022-11-15T15:00:00\",\"SpotPriceDKK\":417.730011},{\"HourUTC\":\"2022-11-15T16:00:00\",\"SpotPriceDKK\":636.119995},{\"HourUTC\":\"2022-11-15T17:00:00\",\"SpotPriceDKK\":309.359985},{\"HourUTC\":\"2022-11-15T18:00:00\",\"SpotPriceDKK\":207.080002},{\"HourUTC\":\"2022-11-15T19:00:00\",\"SpotPriceDKK\":174.199997},{\"HourUTC\":\"2022-11-15T20:00:00\",\"SpotPriceDKK\":140.059998},{\"HourUTC\":\"2022-11-15T21:00:00\",\"SpotPriceDKK\":132.699997}]}",
        "payloadType": "json",
        "x": 410,
        "y": 80,
        "wires": [
            [
                "272666c87d930046",
                "80adc07bb87a9ab7",
                "09f3495a0899eb34",
                "a9912c9e488e6c29",
                "6e499cd69d1a9087"
            ]
        ]
    },
    {
        "id": "243b1854f0f67142",
        "type": "debug",
        "z": "b63989b9c24c3d01",
        "name": "CHANGE ARRAY payload",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": true,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "payload",
        "statusType": "auto",
        "x": 1240,
        "y": 80,
        "wires": []
    },
    {
        "id": "09f3495a0899eb34",
        "type": "debug",
        "z": "b63989b9c24c3d01",
        "name": "ORIGINAL msg.payload",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": true,
        "complete": "payload",
        "targetType": "msg",
        "statusVal": "payload",
        "statusType": "auto",
        "x": 470,
        "y": 160,
        "wires": []
    },
    {
        "id": "a9912c9e488e6c29",
        "type": "debug",
        "z": "b63989b9c24c3d01",
        "name": "records.(SpotPriceDKK/1000)",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": true,
        "complete": "records.(SpotPriceDKK/1000)",
        "targetType": "jsonata",
        "statusVal": "records.(SpotPriceDKK/1000)",
        "statusType": "auto",
        "x": 850,
        "y": 300,
        "wires": []
    },
    {
        "id": "6e499cd69d1a9087",
        "type": "debug",
        "z": "b63989b9c24c3d01",
        "name": "records.{\"HourUTC\": HourUTC, \"SpotPriceDKK\": (SpotPriceDKK / 1000) }",
        "active": false,
        "tosidebar": true,
        "console": false,
        "tostatus": true,
        "complete": "records.{\"HourUTC\": HourUTC, \"SpotPriceDKK\": (SpotPriceDKK / 1000) }",
        "targetType": "jsonata",
        "statusVal": "records.(SpotPriceDKK/1000)",
        "statusType": "auto",
        "x": 980,
        "y": 140,
        "wires": []
    }
]

Please advise!

Try this

$$.payload ~> |$.records|{"SpotPriceDKK": ($.SpotPriceDKK / 1000)}|

Uses the JSONata Transform operator

As to exerciser have you set the json to payload as below


I think your first change node would have also work if you added payload.
e.g.
$$.payload.records.{"HourUTC": HourUTC, "SpotPriceDKK": (SpotPriceDKK / 1000) }

1 Like