Given an example JSON data like this:
{
"payload":
[
{
"lspf_numlot": "22701-15",
"prop_attrezzatura": "OP5 ",
"proposta_data_inizio": "2024-03-01T09:55:00.000Z",
"proposta_data_fine": "2024-03-01T16:55:00.000Z",
"IDFase": 11023
},
{
"lspf_numlot": "22701-15",
"prop_attrezzatura": "CP8 ",
"proposta_data_inizio": "2024-03-01T16:57:00.000Z",
"proposta_data_fine": "2024-03-02T16:57:00.000Z",
"IDFase": 11024
},
{
"lspf_numlot": "22701-15",
"prop_attrezzatura": "BU6 ",
"proposta_data_inizio": "2024-03-03T13:57:00.000Z",
"proposta_data_fine": "2024-03-03T16:57:00.000Z",
"IDFase": 11026
},
{
"lspf_numlot": "22701-15",
"prop_attrezzatura": "SPROMBA1 ",
"proposta_data_inizio": "2024-03-03T13:57:00.000Z",
"proposta_data_fine": "2024-03-03T16:57:00.000Z",
"IDFase": 11026
},
{
"lspf_numlot": "22701-15",
"prop_attrezzatura": "GPROMMCP3 ",
"proposta_data_inizio": "2024-03-03T13:57:00.000Z",
"proposta_data_fine": "2024-03-03T16:57:00.000Z",
"IDFase": 11026
},
{
"lspf_numlot": "03641-01",
"prop_attrezzatura": "BL5 ",
"proposta_data_inizio": "2024-02-27T08:16:00.000Z",
"proposta_data_fine": "2024-02-27T12:11:00.000Z",
"IDFase": 36148
},
{
"lspf_numlot": "03641-01",
"prop_attrezzatura": "BU6 ",
"proposta_data_inizio": "2024-02-27T14:22:00.000Z",
"proposta_data_fine": "2024-02-27T19:26:00.000Z",
"IDFase": 36149
}
]
}
I'd like to get something like this:
{
"dataItems": [
{
"group": "22701-15",
"data": [
{
"label": "11023",
"data": [
{
"timeRange": [
"2024-03-01T09:55:00.000Z",
"2024-03-01T16:55:00.000Z"
],
"val": "OP5"
}
]
},
{
"label": "11024",
"data": [
{
"timeRange": [
"2024-03-01T16:57:00.000Z",
"2024-03-02T16:57:00.000Z"
],
"val": "CP8"
}
]
},
{
"label": "11026",
"data": [
{
"timeRange": [
"2024-03-03T13:57:00.000Z",
"2024-03-03T16:57:00.000Z"
],
"val": "BU6"
},
{
"timeRange": [
"2024-03-03T13:57:00.000Z",
"2024-03-03T16:57:00.000Z"
],
"val": "SPROMBA1"
}
]
},
{
"label": "11027",
"data": [
{
"timeRange": [
"2024-03-03T13:57:00.000Z",
"2024-03-03T16:57:00.000Z"
],
"val": "SPROMBA1"
}
]
},
{
"label": "11028",
"data": [
{
"timeRange": [
"2024-03-03T13:57:00.000Z",
"2024-03-03T16:57:00.000Z"
],
"val": "GPROMMCP3"
}
]
}
]
},
{
"group": "03641-01",
"data": [
{
"label": "36148",
"data": [
{
"timeRange": [
"2024-02-27T08:16:00.000Z",
"2024-02-27T12:11:00.000Z"
],
"val": "BL5"
}
]
},
{
"label": "36149",
"data": [
{
"timeRange": [
"2024-02-27T14:22:00.000Z",
"2024-02-27T19:26:00.000Z"
],
"val": "BU6"
}
]
}
]
}
]
}
The best solution I've been able to create is this:
payload{
"dataItems": [
$.{
"group": $.lspf_numlot,
"data": [
{
"label": $string($.IDFase),
"data": [
{
"timeRange": [
$.proposta_data_inizio,
$.proposta_data_fine
],
"val": $trim($.prop_attrezzatura)
}
]
}
]
}
]
}
Which gives me back this data:
{ "payload": {
"dataItems": [
{
"group": "33001-07",
"data": [
{
"label": "27486",
"data": [
{
"timeRange": [
"2024-02-16T08:00:00.000Z",
"2024-02-16T10:00:00.000Z"
],
"val": "BL5"
}
]
}
]
},
{
"group": "33001-07",
"data": [
{
"label": "27487",
"data": [
{
"timeRange": [
"2024-02-16T14:00:00.000Z",
"2024-02-16T16:00:00.000Z"
],
"val": "OP5"
}
]
}
]
},
{
"group": "33001-07",
"data": [
{
"label": "27488",
"data": [
{
"timeRange": [
"2024-02-16T17:00:00.000Z",
"2024-02-16T19:00:00.000Z"
],
"val": "BU6"
}
]
}
]
},
{
"group": "33001-08",
"data": [
{
"label": "27489",
"data": [
{
"timeRange": [
"2024-02-16T11:00:00.000Z",
"2024-02-16T13:00:00.000Z"
],
"val": "CP8"
}
]
}
]
}
]
}
}
I'd need to group by lspf_numlot, then by IDFase and finally by prop_attrezzatura (as per the example above). I've read the whole documentation of JSONata (https://docs.jsonata.org/) but didn't understand how to create such a nested grouping like that. I guess that some kind of decomposition (map?) could be needed, but I haven't been able to understand how to achieve that.
I hope the following expression gives you the expected result:
{"dataItems": $each(payload {
lspf_numlot: ${
IDFase & '': ${
prop_attrezzatura: ${
"val": prop_attrezzatura,
"timeRange": [proposta_data_inizio, proposta_data_fine]
}
}
}
}, function($v, $k) {
{
"group": $k,
"data": $each($v, function($vv, $kk) {
{
"label": $kk,
"data": $each($vv, function($vvv, $kkk) {
{
"val": $kkk,
"timeRange": $vvv.timeRange
}
})[]
}
})
}
})
}
The result is following:
{
"dataItems": [
{
"group": "22701-15",
"data": [
{
"label": "11023",
"data": [
{
"val": "OP5 ",
"timeRange": [
"2024-03-01T09:55:00.000Z",
"2024-03-01T16:55:00.000Z"
]
}
]
},
{
"label": "11024",
"data": [
{
"val": "CP8 ",
"timeRange": [
"2024-03-01T16:57:00.000Z",
"2024-03-02T16:57:00.000Z"
]
}
]
},
{
"label": "11026",
"data": [
{
"val": "BU6 ",
"timeRange": [
"2024-03-03T13:57:00.000Z",
"2024-03-03T16:57:00.000Z"
]
},
{
"val": "SPROMBA1 ",
"timeRange": [
"2024-03-03T13:57:00.000Z",
"2024-03-03T16:57:00.000Z"
]
},
{
"val": "GPROMMCP3 ",
"timeRange": [
"2024-03-03T13:57:00.000Z",
"2024-03-03T16:57:00.000Z"
]
}
]
}
]
},
{
"group": "03641-01",
"data": [
{
"label": "36148",
"data": [
{
"val": "BL5 ",
"timeRange": [
"2024-02-27T08:16:00.000Z",
"2024-02-27T12:11:00.000Z"
]
}
]
},
{
"label": "36149",
"data": [
{
"val": "BU6 ",
"timeRange": [
"2024-02-27T14:22:00.000Z",
"2024-02-27T19:26:00.000Z"
]
}
]
}
]
}
]
}
How this works:
payload array to the expected groupings.