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.

Tai · Accepted Answer · 2024-03-17 12:47:59Z

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:

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

answered Mar 17, 2024 at 12:47

Tai

261 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Gippy73 Over a year ago

Thank you so much!!! You solved my issue. I must be honest: I still don't understand (yet) the code you kindly provided, but it's because I'll have to study more and more JSONata :) Thanks again.

Tai Over a year ago

@Gippy73. Are you able to mark this as the answered so it helps others as well? Thanks.

Tai Over a year ago

We first group the array. By doing grouping, we make the value of the property we’re grouping as the key. So we do three groups (nested). Keys need to be string, so we convert IDFase integers to strings.

Tai Over a year ago

Once we have the array with the expected groups. We iterate over each item and do the mapping to convert the item object to the expected structure.

Collectives™ on Stack Overflow

How to get the right grouping in my JSONata code?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related