Unnest deep array to top level with JOLT

Question

I'm keep practicing with JOLT. I've a JSON:

{
  "orders": [
    {
      "id": "247/01",
      "order_date": "2023-08-29",
      "categories": [
        {
          "id": "559",
          "type": "Smart Devices",
          "items": [
            {
              "sku": "65001b733ee01148141e05d2",
              "name": "Apple iPhone 14 Pro Max",
              "declined": true
            }
          ]
        },
        {
          "id": "602",
          "type": "Auto Tires",
          "items": [
            {
              "sku": "65018d50a65a5a0b9be7e232_1_1",
              "name": "Nokian Tyres Hakkapeliitta 9",
              "quantity": 4
            },
            {
              "sku": "65018d50a65a5a0b9be7e232_1_2",
              "name": "Nokian Tyres Hakkapeliitta 10p",
              "quantity": 4
            }
          ]
        }
      ]
    },
    {
      "id": "247/02",
      "order_date": "2023-08-29",
      "categories": [
        {
          "id": "123",
          "type": "TV",
          "items": [
            {
              "sku": "65019304ff453b7596501dea_1_1",
              "name": "LG 42LA644V LED",
              "quantity": 1
            }
          ]
        }
      ]
    }
  ]
}

I want to loop through items array, take sku and name and get back to one level top and grab id from categories array and write it as category_id. Expect output:

Actually tried and result :

It's close, but it stores values inside array if items array contains more than one element.

Answer 1

You can use the following transformation in which the important thing is to distinguish the levels in order to separate each object. In this case, &5_&3_1 is used to handle it. They stand for the levels of the indexes of each array respectively.

Indeed, there's no difference with using &5_&3_1 and &5.&3.1 other than preparation for the second spec, eg. the second spec would be shorter if we prefer using the first one

[
  {
    "operation": "shift",
    "spec": {
      "orders": {
        "*": { // 5 levels up
          "categories": {
            "*": { // 3 levels up
              "items": {
                "*": { // going 1 level up the tree based on the below(innermost) object notation
                  "@2,id": "&5_&3_&1.category_id", // @2 represents going 2 levels up to be able to grab the id's value
                  "sku|name": "&5_&3_&1.&"
                }
              }
            }
          }
        }
      }
    }
  },
  { // get rid of object keys
    "operation": "shift",
    "spec": {
      "*": ""
    }
  }
]

where you got four different key names from the first transformation :

"0_0_0"
"0_1_0"
"0_1_1"
"1_0_0"