How to get the right grouping in my JSONata code?

Question

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.

Answer 1

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:

1. We group the payload array to the expected groupings.
2. For each result object key, we map the value to the expected output.