Help Parsing Data with JSONata

General
1

I have an array that comes in from a SQL Stored Procedure (my SQL team does not want to change their stored procedure) with each item repeated with a seperate cell for each category. Ideally the data should be in the form as follows:

[
{'Name':"A",'Passed':100,'Rejected':10},  
{'Name':"B",'Passed':92,'Rejected':0},
]

But Instead I get my data as follows:

[
{'name':"A",'Value':100,'seriesFK':"Passed"},  
{'name':"A",'Value':10,'seriesFK':"Rejected"},
{'name':"B",'Value':92,'seriesFK':"Passed"}
]

I have tried using JSONata to transform the data but I am getting really stuck.

[
$[
seriesFk ='Passed']
{'Name':name,'Passed':value},

$[seriesFk ='Rejected']
{'Name':name,'Scrapped':value},

$[seriesFk ='Target']
{'Name':name,'Target':value}
]

Attached is my flow, with the sample data from the SQL query as a function node to inject for testing:

[{"id":"228dc89e.6cb1f8","type":"debug","z":"9ae8b9db.250e78","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":767.0764427185059,"y":630.1527519226074,"wires":[]},{"id":"e53fc458.989918","type":"switch","z":"9ae8b9db.250e78","name":"","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"\t[\t$[\tseriesFk ='Passed']\t{'Name':name,'Passed':value},\t\t$[seriesFk ='Rejected']\t{'Name':name,'Scrapped':value},\t\t$[seriesFk ='Target']\t{'Name':name,'Target':value}\t]\t","vt":"jsonata"}],"checkall":"true","repair":false,"outputs":1,"x":534.0729522705078,"y":735.8437843322754,"wires":[["228dc89e.6cb1f8"]]},{"id":"fdb0c14c.a1049","type":"template","z":"9ae8b9db.250e78","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"\n  [\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"Last Shift\",\n      \"sort\": -1,\n      \"value\": 1747,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Passed\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"9 to 10\",\n      \"sort\": 9,\n      \"value\": 367,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Passed\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"10 to 11\",\n      \"sort\": 10,\n      \"value\": 374,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Passed\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"11 to 12\",\n      \"sort\": 11,\n      \"value\": 347,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Passed\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"12 to 13\",\n      \"sort\": 12,\n      \"value\": 370,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Passed\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"13 to 14\",\n      \"sort\": 13,\n      \"value\": 374,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Passed\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"14 to 15\",\n      \"sort\": 14,\n      \"value\": 323,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Passed\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"Last Shift\",\n      \"sort\": -1,\n      \"value\": 12,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Rejected\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"9 to 10\",\n      \"sort\": 9,\n      \"value\": 1,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Rejected\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"10 to 11\",\n      \"sort\": 10,\n      \"value\": 1,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Rejected\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"11 to 12\",\n      \"sort\": 11,\n      \"value\": 1,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Rejected\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"12 to 13\",\n      \"sort\": 12,\n      \"value\": 9,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Rejected\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"13 to 14\",\n      \"sort\": 13,\n      \"value\": 1,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Rejected\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"14 to 15\",\n      \"sort\": 14,\n      \"value\": 2,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Rejected\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"Last Shift\",\n      \"sort\": -1,\n      \"value\": 300,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Target\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"9 to 10\",\n      \"sort\": 9,\n      \"value\": 300,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Target\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"10 to 11\",\n      \"sort\": 10,\n      \"value\": 300,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Target\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"11 to 12\",\n      \"sort\": 11,\n      \"value\": 300,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Target\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"12 to 13\",\n      \"sort\": 12,\n      \"value\": 300,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Target\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"13 to 14\",\n      \"sort\": 13,\n      \"value\": 300,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Target\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"14 to 15\",\n      \"sort\": 14,\n      \"value\": 300,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Target\"\n    }\n  ]","output":"json","x":271.06945037841797,"y":740.0521192550659,"wires":[["e53fc458.989918"]]},{"id":"1faa8995.2b67b6","type":"inject","z":"9ae8b9db.250e78","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":87.07638549804688,"y":734.4167022705078,"wires":[["fdb0c14c.a1049"]]}]

Anyone who could point me to model flow or additional information it would be greatly appreciated. Or I would not look a gift horse in the mouth if someone made me a flow. The help on this board is amazing so I do not want to abuse the kind souls that help so many people out.

Regards,
Jon

2

The result you show does not seem to agree with the input data. Should it not be 100 rejected for A in the result? Or Value should be 10 in the input data for Rejected.
Is the seriesFK field of any consequence?

3

Edited First Post: You are 100% correct. Sorry for the typo.

The seriesFK gives me what the context of the value.

My Sample Data Set is Actually as follows:

 [
    {"name": "Last Shift","sort": -1,"value": 1747,"seriesFk": "Passed"},
    {"name": "9 to 10","sort": 9,"value": 367,"seriesFk": "Passed"},
    {"name": "10 to 11", "sort": 10,"value": 374,"seriesFk": "Passed"},
    {"name": "11 to 12","sort": 11,"value": 347,"seriesFk": "Passed"},
    {"name": "12 to 13","sort": 12,"value": 370"seriesFk": "Passed"},
    {"name": "13 to 14","sort": 13,"value": 374,"seriesFk": "Passed"},
    {"name": "14 to 15","sort": 14,"value": 323,"seriesFk": "Passed"},

    {"name": "Last Shift","sort": -1,"value": 12"seriesFk": "Rejected"},
    {"name": "9 to 10", "sort": 9,"value": 1,"seriesFk": "Rejected"},
    {"name": "10 to 11","sort": 10,"value": 1,"seriesFk": "Rejected"},
    {"name": "11 to 12","sort": 11,"value": 1,"seriesFk": "Rejected"},
    {"name": "12 to 13","sort": 12,"value": 9,"seriesFk": "Rejected"},
    {"name": "13 to 14","sort": 13,"value": 1,"seriesFk": "Rejected"},
    {"name": "14 to 15","sort": 14,"value": 2,"seriesFk": "Rejected"},
    
    {"name": "Last Shift","sort": -1,"value": 300,"seriesFk": "Target"},
    {"name": "9 to 10","sort": 9,"value": 300,"seriesFk": "Target"},
    {"name": "10 to 11","sort": 10,"value": 300,"seriesFk": "Target"},
    {"name": "11 to 12","sort": 11,"value": 300,"seriesFk": "Target"},
    {"name": "12 to 13","sort": 12,"value": 300,"seriesFk": "Target"},
    {"name": "13 to 14","sort": 13,"value": 300,"seriesFk": "Target"},
    {"name": "14 to 15","sort": 14,"value": 300,"seriesFk": "Target"}
  ]

Regards,
Jon

4

And what should that produce?
Can you post a mini-flow that generates the input data? Then we can experiment with it.

5

At the end of the day, I want to make that data above into a dashboard table that takes that table from an SQL query and turns it into a table like this.

Not sure why this table does not match the data above
image

I would remove the sort and the target from the table. The target will then be re-used to color code the passed column red/green based on above and below the target.

Here is my sample flow with the data in template block.

[{"id":"228dc89e.6cb1f8","type":"debug","z":"9ae8b9db.250e78","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","x":767.0764427185059,"y":630.1527519226074,"wires":[]},{"id":"e53fc458.989918","type":"switch","z":"9ae8b9db.250e78","name":"","property":"payload","propertyType":"msg","rules":[{"t":"eq","v":"\t[\t$[\tseriesFk ='Passed']\t{'Name':name,'Passed':value},\t\t$[seriesFk ='Rejected']\t{'Name':name,'Scrapped':value},\t\t$[seriesFk ='Target']\t{'Name':name,'Target':value}\t]\t","vt":"jsonata"}],"checkall":"true","repair":false,"outputs":1,"x":534.0729522705078,"y":735.8437843322754,"wires":[["228dc89e.6cb1f8"]]},{"id":"fdb0c14c.a1049","type":"template","z":"9ae8b9db.250e78","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"\n  [\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"Last Shift\",\n      \"sort\": -1,\n      \"value\": 1747,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Passed\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"9 to 10\",\n      \"sort\": 9,\n      \"value\": 367,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Passed\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"10 to 11\",\n      \"sort\": 10,\n      \"value\": 374,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Passed\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"11 to 12\",\n      \"sort\": 11,\n      \"value\": 347,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Passed\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"12 to 13\",\n      \"sort\": 12,\n      \"value\": 370,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Passed\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"13 to 14\",\n      \"sort\": 13,\n      \"value\": 374,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Passed\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"14 to 15\",\n      \"sort\": 14,\n      \"value\": 323,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Passed\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"Last Shift\",\n      \"sort\": -1,\n      \"value\": 12,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Rejected\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"9 to 10\",\n      \"sort\": 9,\n      \"value\": 1,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Rejected\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"10 to 11\",\n      \"sort\": 10,\n      \"value\": 1,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Rejected\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"11 to 12\",\n      \"sort\": 11,\n      \"value\": 1,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Rejected\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"12 to 13\",\n      \"sort\": 12,\n      \"value\": 9,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Rejected\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"13 to 14\",\n      \"sort\": 13,\n      \"value\": 1,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Rejected\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"14 to 15\",\n      \"sort\": 14,\n      \"value\": 2,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Rejected\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"Last Shift\",\n      \"sort\": -1,\n      \"value\": 300,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Target\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"9 to 10\",\n      \"sort\": 9,\n      \"value\": 300,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Target\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"10 to 11\",\n      \"sort\": 10,\n      \"value\": 300,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Target\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"11 to 12\",\n      \"sort\": 11,\n      \"value\": 300,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Target\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"12 to 13\",\n      \"sort\": 12,\n      \"value\": 300,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Target\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"13 to 14\",\n      \"sort\": 13,\n      \"value\": 300,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Target\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"14 to 15\",\n      \"sort\": 14,\n      \"value\": 300,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Target\"\n    }\n  ]","output":"json","x":271.06945037841797,"y":740.0521192550659,"wires":[["e53fc458.989918"]]},{"id":"1faa8995.2b67b6","type":"inject","z":"9ae8b9db.250e78","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":87.07638549804688,"y":734.4167022705078,"wires":[["fdb0c14c.a1049"]]}]
6

There is probably a way of doing it in JSONata, but not being very experience in that I would use a function to do it. Something like this

[{"id":"957a1ee5.52222","type":"debug","z":"cfa39195.99bef","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","x":554.0000610351562,"y":608,"wires":[]},{"id":"7dba9d6f.ff0644","type":"template","z":"cfa39195.99bef","name":"","field":"payload","fieldType":"msg","format":"handlebars","syntax":"mustache","template":"\n  [\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"Last Shift\",\n      \"sort\": -1,\n      \"value\": 1747,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Passed\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"9 to 10\",\n      \"sort\": 9,\n      \"value\": 367,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Passed\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"10 to 11\",\n      \"sort\": 10,\n      \"value\": 374,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Passed\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"11 to 12\",\n      \"sort\": 11,\n      \"value\": 347,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Passed\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"12 to 13\",\n      \"sort\": 12,\n      \"value\": 370,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Passed\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"13 to 14\",\n      \"sort\": 13,\n      \"value\": 374,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Passed\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"14 to 15\",\n      \"sort\": 14,\n      \"value\": 323,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Passed\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"Last Shift\",\n      \"sort\": -1,\n      \"value\": 12,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Rejected\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"9 to 10\",\n      \"sort\": 9,\n      \"value\": 1,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Rejected\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"10 to 11\",\n      \"sort\": 10,\n      \"value\": 1,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Rejected\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"11 to 12\",\n      \"sort\": 11,\n      \"value\": 1,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Rejected\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"12 to 13\",\n      \"sort\": 12,\n      \"value\": 9,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Rejected\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"13 to 14\",\n      \"sort\": 13,\n      \"value\": 1,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Rejected\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"14 to 15\",\n      \"sort\": 14,\n      \"value\": 2,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Rejected\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"Last Shift\",\n      \"sort\": -1,\n      \"value\": 300,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Target\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"9 to 10\",\n      \"sort\": 9,\n      \"value\": 300,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Target\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"10 to 11\",\n      \"sort\": 10,\n      \"value\": 300,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Target\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"11 to 12\",\n      \"sort\": 11,\n      \"value\": 300,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Target\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"12 to 13\",\n      \"sort\": 12,\n      \"value\": 300,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Target\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"13 to 14\",\n      \"sort\": 13,\n      \"value\": 300,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Target\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"14 to 15\",\n      \"sort\": 14,\n      \"value\": 300,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Target\"\n    }\n  ]","output":"json","x":267.9930725097656,"y":608.8993530273438,"wires":[["fc1efccb.dd7608"]]},{"id":"7dd99eab.2f7868","type":"inject","z":"cfa39195.99bef","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":113,"y":608.2639770507812,"wires":[["7dba9d6f.ff0644"]]},{"id":"fc1efccb.dd7608","type":"function","z":"cfa39195.99bef","name":"Map","func":"let results = {}\nfor (let i=0; i<msg.payload.length; i++) {\n    let record = msg.payload[i]\n    if (!results[record.name]) {\n        results[record.name] = {name: record.name, Passed: 0, Rejected: 0, Target: 0}\n        //node.warn(`added ${record.name}`)\n    }\n    //node.warn(` Fk: ${record.seriesFk}, value ${record.value}`)\n    results[record.name][record.seriesFk] = record.value\n}\n// convert results to an array\nmsg.payload = []\nfor (var property in results) {\n    if (results.hasOwnProperty(property)) {\n        msg.payload.push(results[property])\n    }\n}\nreturn msg;","outputs":1,"noerr":0,"x":401,"y":609,"wires":[["957a1ee5.52222"]]}]
1 Like
7

Thank you so much. I could have never written that JavaScript.

Regards,
Jon

8

Glad to be of help, but please look at it and make sure you understand what it is doing. Then I won't need to help the next time (or at least not help as much) :slight_smile:

9

I have looked at it and added comments to the code. You are an amazing :man_teacher: (teacher)

//  Creates an empty Object
let results = {}

/*  
    Iterate through the whole payload and look at the field called name. 
    If it does not exist, create a blank record pre-populated with the name value and sort value of the record and zero the fields.
    If it does exist, do nothing.
*/
for (let i=0; i<msg.payload.length; i++) {
    let record = msg.payload[i]
    if (!results[record.name]) {
        results[record.name] = {name: record.name, Passed: 0, Rejected: 0, Target: 0, sort: record.sort}
        //node.warn(`added ${record.name}`)
    }
    //node.warn(` Fk: ${record.seriesFk}, value ${record.value}`)
    //This is the genius, maps the value of the seriesFk to the correct name and records the value.
    results[record.name][record.seriesFk] = record.value
}
// convert results to an array
msg.payload = []
for (var property in results) {
    if (results.hasOwnProperty(property)) {
        msg.payload.push(results[property])
    }
}
return msg;
10

I suspect there may be a much neater way of doing it using map or something, that someone who knows js inside out could produce.
I initially looked at building it directly in the array but decided it was easier to do the way I have.
In addition the code may be a bit delicate, it won't cope well with malformed data. If there is a possibility of that you may need to put in some stuff that validates the data content.

11

If you are curious about the jsonata version:

[{"id":"d5f395c6.042fe8","type":"template","z":"a0840c7b.5ead88","name":"","field":"payload","fieldType":"msg","format":"json","syntax":"mustache","template":"[\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"Last Shift\",\n      \"sort\": -1,\n      \"value\": 1747,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Passed\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"9 to 10\",\n      \"sort\": 9,\n      \"value\": 367,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Passed\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"10 to 11\",\n      \"sort\": 10,\n      \"value\": 374,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Passed\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"11 to 12\",\n      \"sort\": 11,\n      \"value\": 347,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Passed\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"12 to 13\",\n      \"sort\": 12,\n      \"value\": 370,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Passed\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"13 to 14\",\n      \"sort\": 13,\n      \"value\": 374,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Passed\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"14 to 15\",\n      \"sort\": 14,\n      \"value\": 323,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Passed\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"Last Shift\",\n      \"sort\": -1,\n      \"value\": 12,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Rejected\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"9 to 10\",\n      \"sort\": 9,\n      \"value\": 1,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Rejected\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"10 to 11\",\n      \"sort\": 10,\n      \"value\": 1,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Rejected\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"11 to 12\",\n      \"sort\": 11,\n      \"value\": 1,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Rejected\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"12 to 13\",\n      \"sort\": 12,\n      \"value\": 9,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Rejected\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"13 to 14\",\n      \"sort\": 13,\n      \"value\": 1,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Rejected\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"14 to 15\",\n      \"sort\": 14,\n      \"value\": 2,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Rejected\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"Last Shift\",\n      \"sort\": -1,\n      \"value\": 300,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Target\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"9 to 10\",\n      \"sort\": 9,\n      \"value\": 300,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Target\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"10 to 11\",\n      \"sort\": 10,\n      \"value\": 300,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Target\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"11 to 12\",\n      \"sort\": 11,\n      \"value\": 300,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Target\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"12 to 13\",\n      \"sort\": 12,\n      \"value\": 300,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Target\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"13 to 14\",\n      \"sort\": 13,\n      \"value\": 300,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Target\"\n    },\n    {\n      \"dataSetPk\": 1,\n      \"name\": \"14 to 15\",\n      \"sort\": 14,\n      \"value\": 300,\n      \"reasonFK\": 1,\n      \"seriesFk\": \"Target\"\n    }\n]","output":"json","x":360,"y":140,"wires":[["e2ee0f11.7c3548"]]},{"id":"d8024ee8.5656b","type":"inject","z":"a0840c7b.5ead88","name":"","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"","once":false,"onceDelay":0.1,"x":180,"y":140,"wires":[["d5f395c6.042fe8"]]},{"id":"e2ee0f11.7c3548","type":"change","z":"a0840c7b.5ead88","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"payload.(\t       $name := $.name;\t       {\t           $.name: {\t               'sort': sort,\t               'name': name,\t               'Passed': $$.payload[name=$name and seriesFk='Passed'].value,\t               'Rejected': $$.payload[name=$name and seriesFk='Rejected'].value,\t               'Target': $$.payload[name=$name and seriesFk='Target'].value\t           }\t       }\t) ~> $merge ~> $each(function($v){$v})","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":560,"y":140,"wires":[["356c59fd.7aa5e6"]]},{"id":"356c59fd.7aa5e6","type":"debug","z":"a0840c7b.5ead88","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","x":750,"y":140,"wires":[]}]
1 Like
12

Works like a charm. I did not really need it as an array, I am fine with it as JSON object. So I deleted the $each that converted it to an array.

I am not sure how you figured that out, but the method is ingenuous.:thinking:

Sorry Collin, though I was going to use your method, @nlecaude solved the stated topic.

This forum is the best!!!!
Regards,
Jon

1 Like
13

No apologies necessary, now not only have you learnt something about javascript but both of us have learnt more about JSONata, which is the best outcome.

2 Likes
14

And keep in mind a few things:
While I really enjoy JSONata it's not always the best solution.
It's slower than Javascript so if your flow relies on speed it's sometimes best to go the JavaScript route.
Also in some cases it can make the code less readable depending on the complexity of the expression. The problem you posted however is a nice demonstration of the power of JSONata to manipulate incoming JSON structures. As Colin mentioned, another alternative would have been to use map and filter in JavaScript which are also present in JSONata (whenever you something like payload.(x*x) it's the equivalent of map and something like payload[name='foo'] is the equivalent of filter.

Happy coding !