Nested lists and aggreagted data

[Pierrefreelance]

Hello everyone
I need to several transformations, in order to nest data in list, and aggregate them.
I'm more or less OK on the nest part, but I can't quite resolve how to aggregate them the way I want.

I have this in input :

[
{
"market_id": "4501",
"market_buyer_id": "Peter",
"modif_id": "AST-12 ",
"modif_length": "3",
"modif_owner": "Alice",
"modif_owner_title": "CEO"
},
{
"market_id": "4501",
"market_buyer_id": "Peter",
"modif_id": "AST-14 ",
"modif_length": "4",
"modif_owner": "Ken",
"modif_owner_title": "accountant"
},
{
"market_id": "5021",
"market_buyer_id": "Bob",
"modif_id": "AST-16 ",
"modif_length": "5",
"modif_owner": "Ryu",
"modif_owner_title": "counselor"
}
]

I want to create a list named "market" which list each "market_id" (and other fields), and inside the "market_id" list, I want a sub-list
named "modifications" which contains modif_id, modif_length, modif_owner and modif_owner_title.
Here is what i manage to get :

{
"market" : [ {
"id" : "4501",
"modifications" : [ {
"id" : "AST-12 ",
"length" : "3",
"owner" : [ {
"name" : "Alice",
"title" : "CEO"
} ]
} ]
}, {
"id" : "4501",
"modifications" : [ {
"id" : "AST-14 ",
"length" : "4",
"owner" : [ {
"name" : "Ken",
"title" : "accountant"
} ]
} ]
}, {
"id" : "5021",
"modifications" : [ {
"id" : "AST-16 ",
"length" : "5",
"owner" : [ {
"name" : "Ryu",
"title" : "counselor"
} ]
} ]
} ]
}

What i want is this, i'd like the modifications sub-list to actually list all the modification for a given market_id :

{
"market" : [ {
"id" : "4501",
"modifications" : [ {
"id" : "AST-12 ",
"length" : "3",
"owner" : [ {
"name" : "Alice",
"title" : "CEO"
}]
}
,{
"id" : "AST-14 ",
"length" : "4",
"owner" : [ {
"name" : "Ken",
"title" : "accountant"
}]
}

]

}, {
"id" : "5021",
"modifications" : [ {
"id" : "AST-16 ",
"length" : "5",
"owner" : [ {
"name" : "Ryu",
"title" : "counselor"
} ]
} ]
} ]
}

Here is my actual transformation :

[{
"operation": "shift",
"spec": {
"*": {
"market_id": "market[#2].id",
"modif_id": "market[#2].modifications[#3].id",
"modif_length": "market[#2].modifications[#3].length",
"modif_owner": "market[#2].modifications[#3].owner[#3].name",
"modif_owner_title": "market[#2].modifications[#3].owner[#3].title"

  }
}

}

]

[gbouget]

Hello,

What do you think of the following specification?

[
  {
    "operation": "shift",
    "spec": {
      "*": "@market_id.modifications[]" // regroup by market_id
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": "market[]"
    }
  },
  {
    "operation": "shift",
    "spec": {
      "market": {
        "*": {
          "modifications": {
            "*": {
              "market_id": "market[&3].id",
              "market_buyer_id": "market[&3].modifications[&1].id",
              "modif_length": "market[&3].modifications[&1].length",
              "modif_owner": "market[&3].modifications[&1].owner[#0].name",
              "modif_owner_title": "market[&3].modifications[&1].owner[#0].title"
            }
          }
        }
      }
    }
  },
  {
    "operation": "cardinality",
    "spec": {
      "market": {
        "*": {
          "id": "ONE"
        }
      }
    }
  }
]