Split items from nested arrays into individual objects

[Chris-Fri]

Dear all, I have the following json where each entry from "myItems" has an id (e.g. "a3/2820") and an attribute that in turn contains arrays of 2 (or n) items:

{
  "data": {
    "myItems": [
      {
        "id": "a3/2820",
        "usedFor": [
          {
            "id": [
              "a6/868",
              "a6/489"
            ],
            "localIdentifier": [
              "1",
              "2"
            ],
            "participatesIn": {
              "id": [
                "a2/761",
                "a2/557"
              ],
              "title": [
                "someTitle1",
                "someTitle2"
              ]
            }
          }
        ]
      },
      {
        "id": "a3/3002",
        "usedFor": [
          {
            "id": [
              "a6/870",
              "a6/490"
            ],
            "localIdentifier": [
              "3",
              "2"
            ],
            "participatesIn": {
              "id": [
                "a2/761",
                "a2/557"
              ],
              "title": [
                "someTitle1",
                "someTitle2"
              ]
            }
          }
        ]
      }
    ]
  }
}

And I would like to achieve the json below where for each entry in "myItems" the values in the various arrays are separated into individual objects

{
  "data": {
    "myItems": [
      {
        "id": "a3/2820",
        "usedFor": [
          {
            "id": "a6/868",
            "participatesIn": {
              "id": "a2/761",
              "studyScientificTitle": "someTitle1"
            }
          },
          {
            "id": "a6/870",
            "participatesIn": {
              "id": "a2/557",
              "title": "someTitle2"
            }
          }
        ]
      },
      {
        "id": "a3/3002",
        "usedFor": [
          {
            "id": "a6/489",
            "participatesIn": {
              "id": "a2/761",
              "title": "someTitle1"
            }
          },
          {
            "id": "a6/490",
            "participatesIn": {
              "id": "2/557",
              "title": "someTitle2"
            }
          }
        ]
      }
    ]
  }
}

I tried with something like the below before but that does not work as it just combines all entries from "myItems" into the first one.

[
  {
    "operation": "shift",
    "spec": {
      "data": {
        "myItems": {
          "*": {
            "id": "data.myItems[&1].id",
            "usedFor": {
              "*": {
                "id": {
                  "*": {
                    "@": "data.myItems[&3].usedFor[&1].id",
                    "@(2,localIdentifier[&])": "data.myItems[&3].usedFor[&1].localIdentifier",
                    "@(2,participatesIn)": "data.myItems[&3].usedFor[&1].participatesIn"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
]

Any hint is highly appreciated! Thanks a lot in advance

[gbouget]

If id a3/2820 is used for a6/868 and a6/489 and not 868/870 as in your example, I proceed in two steps:

1. initial grouping of attributes based on their position in each table (index) ;
2. change index list to an array in usedFor

[
  { // grouping of attributes based on their position in each table (index)
    "operation": "shift",
    "spec": {
      "data": {
        "myItems": {
          "*": {
            "id": "data.myItems[&1].id",
            "usedFor": {
              "*": {
                "id|localIdentifier": {
                  "*": { // index (position in array)
                    "@": "data.myItems[&5].usedFor.&1.&2" // &1 is index and &2 is id or localIdentifier
                  }
                },
                "participatesIn": {
                  "*": {
                    "*": {
                      "@": "data.myItems[&6].usedFor.&1.&3.&2"
                    }
                  },
                  "title": {
                    "*": {
                      "@": "data.myItems[&6].usedFor.&1.&3.studyScientificTitle"
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  },
  { // change index list to an array in usedFor
    "operation": "shift",
    "spec": {
      "data": {
        "myItems": {
          "*": {
            "id": "data.myItems[&1].id",
            "usedFor": {
              "*": {
                "*": "data.myItems[&3].usedFor[&1].&"
              }
            }
          }
        }
      }
    }
  }
]

[Chris-Fri]

That is great. Thank you so much!