Reformat data from data export. Possibly jsonata?

I built a dashboard some months ago to analyse local covid data and display it in a way which was quick & easy to digest by my family.
But in the past 6 months, PHE (Public Heath England) have changed the format of their data 3 times now :rage: :rage: :rage: causing me to change chart formats, calculations etc, which is quite a lot of work, testing etc.
So instead of changing my flow around, I think it would be probably easier to change the incoming data format to the previous format, before feeding it into my flow.
However, easier said than done!

My local data covers 39 smaller areas (MSOA's) which I analyse individually, and the 'new' format can be seen from this small flow;

[{"id":"598b83c3.a07d0c","type":"inject","z":"1aed9202.b5cf2e","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":140,"y":600,"wires":[["a13779db.f16d38"]]},{"id":"a13779db.f16d38","type":"http request","z":"1aed9202.b5cf2e","name":"","method":"GET","ret":"txt","paytoqs":"ignore","url":"https://api.coronavirus.data.gov.uk/v2/data?areaType=msoa&areaCode=E08000017&metric=newCasesBySpecimenDateRollingSum&format=json","tls":"","persist":false,"proxy":"","authType":"","x":310,"y":600,"wires":[["658e03b2.14f1cc"]]},{"id":"658e03b2.14f1cc","type":"json","z":"1aed9202.b5cf2e","name":"","property":"payload","action":"","pretty":false,"x":470,"y":600,"wires":[["66470546.8437dc"]]},{"id":"66470546.8437dc","type":"debug","z":"1aed9202.b5cf2e","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":640,"y":600,"wires":[]}]

..which comprises an array of 1482 objects.

I would like to get that data into a single array of 39 objects, each object representing the complete data for that smaller area (MSOA), so for example 'Moorends' or 'Rossington', etc, so each object in the array would look like this;

{
   "name":"Moorends",
   "cases":[
      {
         "date":"2020-11-14",
         "rollingSum":11
      },
      {
         "date":"2020-11-21",
         "rollingSum":15
      },
      {
         "date":"2020-11-28",
         "rollingSum":5
      }
   ]
}
  1. The 'cases' arrays contain about 38 objects each - I've deleted some for ease of displaying in the example above.
  2. "rollingSum" has now been renamed "newCasesBySpecimenDateRollingSum:"
  3. I don't need any of the other data.

I've been trying to do this with jsonata all night, but all I've managed so far is a headache! Any help would be appreciated.

Maybe this jsonata expression for filtering the cases for "Moorends"

{
    "areaName" : "Moorends",  
    "cases" : **[areaName = 'Moorends'].{"date": $.date, "rollingSum": $.newCasesBySpecimenDateRollingSum  }
}

Test Flow:

[{"id":"598b83c3.a07d0c","type":"inject","z":"b8827a7d.94516","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":200,"y":260,"wires":[["a13779db.f16d38"]]},{"id":"a13779db.f16d38","type":"http request","z":"b8827a7d.94516","name":"","method":"GET","ret":"txt","paytoqs":"ignore","url":"https://api.coronavirus.data.gov.uk/v2/data?areaType=msoa&areaCode=E08000017&metric=newCasesBySpecimenDateRollingSum&format=json","tls":"","persist":false,"proxy":"","authType":"","x":370,"y":260,"wires":[["658e03b2.14f1cc"]]},{"id":"658e03b2.14f1cc","type":"json","z":"b8827a7d.94516","name":"","property":"payload","action":"","pretty":false,"x":530,"y":260,"wires":[["adf97fe4.545f38"]]},{"id":"66470546.8437dc","type":"debug","z":"b8827a7d.94516","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":880,"y":260,"wires":[]},{"id":"adf97fe4.545f38","type":"change","z":"b8827a7d.94516","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"{\t    \"areaName\" : \"Moorends\",  \t    \"cases\" : **[areaName = 'Moorends'].{\"date\": $.date, \"rollingSum\": $.newCasesBySpecimenDateRollingSum  }\t}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":700,"y":260,"wires":[["66470546.8437dc"]]}]

Or this which may be a little faster you can enter search term as msg.search

[{"id":"a10c485a.fa75c","type":"inject","z":"8d22ae29.7df6d","name":"","props":[{"p":"payload"},{"p":"search","v":"Moorends","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":90,"y":2080,"wires":[["f22c013f.dc0f6"]]},{"id":"f22c013f.dc0f6","type":"http request","z":"8d22ae29.7df6d","name":"","method":"GET","ret":"obj","paytoqs":"ignore","url":"https://api.coronavirus.data.gov.uk/v2/data?areaType=msoa&areaCode=E08000017&metric=newCasesBySpecimenDateRollingSum&format=json","tls":"","persist":false,"proxy":"","authType":"","x":260,"y":2080,"wires":[["2dea39e9.234336"]]},{"id":"2dea39e9.234336","type":"change","z":"8d22ae29.7df6d","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"\t{\t   \"name\":search,\t   \"cases\":$map(\t   $filter(\t       payload.body,\t       function($v){$v.areaName = search}\t),\t   function($v){\t       {\t           \"date\":$v.date,\t           \"rollingSum\":$v.newCasesBySpecimenDateRollingSum\t       } \t}\t)}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":420,"y":2140,"wires":[["ae47bbde.84a0a"]]},{"id":"ae47bbde.84a0a","type":"debug","z":"8d22ae29.7df6d","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":590,"y":2080,"wires":[]}]

Sorry if I've been unclear, 'Moorends' was just an example of how each object in the array should be constructed.

I am trying to get an array of 39 objects, of which "Moorends" is one of the 39 objects.

So object No1 in the array would be "Moorends"
Object No2 in the array would be "Askern, Campsall & Norton"
Object No3 in the array would be "Thorne" and so on....

Ok here it is in a function node as its faster.

[{"id":"a10c485a.fa75c","type":"inject","z":"8d22ae29.7df6d","name":"","props":[{"p":"payload"},{"p":"search","v":"Moorends","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":90,"y":2080,"wires":[["f22c013f.dc0f6"]]},{"id":"f22c013f.dc0f6","type":"http request","z":"8d22ae29.7df6d","name":"","method":"GET","ret":"obj","paytoqs":"ignore","url":"https://api.coronavirus.data.gov.uk/v2/data?areaType=msoa&areaCode=E08000017&metric=newCasesBySpecimenDateRollingSum&format=json","tls":"","persist":false,"proxy":"","authType":"","x":260,"y":2080,"wires":[["4f4ae899.fee218"]]},{"id":"4f4ae899.fee218","type":"change","z":"8d22ae29.7df6d","name":"","rules":[{"t":"set","p":"start","pt":"msg","to":"","tot":"date"}],"action":"","property":"","from":"","to":"","reg":false,"x":370,"y":2040,"wires":[["7b54c27d.aa4c7c"]]},{"id":"7b54c27d.aa4c7c","type":"function","z":"8d22ae29.7df6d","name":"","func":"//msg.payload = {\"name\": msg.search,\n //           \"cases\":msg.payload.body.filter(value => value.areaName === msg.search).map(value => value = {\"date\":value.date,\"rollingSum\":value.newCasesBySpecimenDateRollingSum})};\nlet areas ={};\nfor(i=0;i<msg.payload.body.length;i++){\n    if(!areas[msg.payload.body[i].areaName]){\n    areas[msg.payload.body[i].areaName]=[];\n    }\nareas[msg.payload.body[i].areaName].push({\"date\":msg.payload.body[i].date,\"RollingSum\":msg.payload.body[i].newCasesBySpecimenDateRollingSum});\n}\nlet newareas = [];\nfor (var key in areas) {\n  newareas.push({\"name\": key,\"cases\": areas[key]});\n} \nmsg.payload= newareas;\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":470,"y":2080,"wires":[["ae47bbde.84a0a"]]},{"id":"ae47bbde.84a0a","type":"debug","z":"8d22ae29.7df6d","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"$millis()-start","statusType":"jsonata","x":690,"y":2020,"wires":[]}]
1 Like

haha :wink:

i've been at it also for a while now .. its imposible with jsonata.
its a powerful language but not many examples on their site for the advanced stuff

my very similar JS solution also :

[{"id":"598b83c3.a07d0c","type":"inject","z":"b8827a7d.94516","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":200,"y":260,"wires":[["a13779db.f16d38"]]},{"id":"a13779db.f16d38","type":"http request","z":"b8827a7d.94516","name":"","method":"GET","ret":"obj","paytoqs":"ignore","url":"https://api.coronavirus.data.gov.uk/v2/data?areaType=msoa&areaCode=E08000017&metric=newCasesBySpecimenDateRollingSum&format=json","tls":"","persist":false,"proxy":"","authType":"","x":370,"y":260,"wires":[["ca1ac848.70cfe","39a4949f.683544"]]},{"id":"66470546.8437dc","type":"debug","z":"b8827a7d.94516","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":790,"y":260,"wires":[]},{"id":"ca1ac848.70cfe","type":"debug","z":"b8827a7d.94516","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":560,"y":180,"wires":[]},{"id":"39a4949f.683544","type":"function","z":"b8827a7d.94516","name":"","func":"\nlet data = msg.payload.body\nlet areas = []\nlet result = []\n\n// get areas\ndata.forEach(el => {\n    if (!areas.includes(el.areaName))\n    areas.push(el.areaName)\n})\n\n// construct result\nareas.forEach(el => {\n    \n    let obj = { \"name\" : el, \"cases\" : [] }\n    \n    data.forEach( e => {\n        if (e.areaName == el ) obj.cases.push({\"date\": e.date, \"rollingSum\": e.newCasesBySpecimenDateRollingSum})\n    })\n    \n    result.push(obj)\n   \n})\n\n\nnode.warn(areas)\nmsg.payload = result\n\n\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":580,"y":260,"wires":[["66470546.8437dc"]]}]
2 Likes

I had done some time testing and was creating a js version when paul posted his update. so i never tried it in jsonata.

times-
version 1 elcid 350ms
version 1 unborn 1000ms
version 2 elcid 50ms
version2 unborn 50ms

1 Like

Both great solutions, tackled slightly different ways, thank you :+1:
As this will only be run once a day (and then saved to file context) timing isn't really going to be an issue here, but good to see that both are expedited smoothly (I'm going to check some of my other functions now!).

Again, thanks for the help.

And just for informtion .
Using jsonata 300ms

[{"id":"a10c485a.fa75c","type":"inject","z":"8d22ae29.7df6d","name":"","props":[{"p":"payload"},{"p":"search","v":"Moorends","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":150,"y":2040,"wires":[["f22c013f.dc0f6"]]},{"id":"f22c013f.dc0f6","type":"http request","z":"8d22ae29.7df6d","name":"","method":"GET","ret":"obj","paytoqs":"ignore","url":"https://api.coronavirus.data.gov.uk/v2/data?areaType=msoa&areaCode=E08000017&metric=newCasesBySpecimenDateRollingSum&format=json","tls":"","persist":false,"proxy":"","authType":"","x":160,"y":2080,"wires":[["4f4ae899.fee218"]]},{"id":"4f4ae899.fee218","type":"change","z":"8d22ae29.7df6d","name":"","rules":[{"t":"set","p":"start","pt":"msg","to":"","tot":"date"}],"action":"","property":"","from":"","to":"","reg":false,"x":370,"y":2040,"wires":[["a6ac79ce.49958"]]},{"id":"a6ac79ce.49958","type":"change","z":"8d22ae29.7df6d","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"$each(payload.body[*]{\t   `areaName`:$.{\"date\": date,\"rollingSum\": newCasesBySpecimenDateRollingSum}\t}\t,function($v,$k){\t{\"name\":$k,\"cases\":$v}\t})\t","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":390,"y":2140,"wires":[["ae47bbde.84a0a"]]},{"id":"ae47bbde.84a0a","type":"debug","z":"8d22ae29.7df6d","name":"","active":true,"tosidebar":true,"console":false,"tostatus":true,"complete":"payload","targetType":"msg","statusVal":"$millis()-start","statusType":"jsonata","x":610,"y":2040,"wires":[]}]
2 Likes