使用JOLT将深层items数组展开至顶层并提取category_id

原始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
            }
          ]
        }
      ]
    }
  ]
}

转换需求 #

• 遍历所有items数组元素，提取sku和name字段提升至顶层
• 提取对应分类的id字段，重命名为category_id
• 最终输出需每个items元素对应一条独立数据，避免字段值被打包为数组

当前问题 #

现有转换结果已接近需求，但当items包含多个元素时，sku、name、category_id会被存入数组，不符合预期。

解决方案JOLT规范 #

[
  {
    "operation": "shift",
    "spec": {
      "orders": {
        "*": {
          "categories": {
            "*": {
              "items": {
                "*": {
                  "sku": "[&1].sku",
                  "name": "[&1].name",
                  "@2,id": "[&1].category_id"
                }
              }
            }
          }
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": "[]"
    }
  }
]

转换逻辑说明 #

1. 第一个shift操作：通过多级数组遍历（orders->*，categories->*，items->*），将每个item的sku、name与对应分类的id（通过@2,id向上两层取categories的id）映射到以item索引为键的对象中
2. 第二个shift操作：将顶层的索引对象展开为数组，每个元素对应一条独立的item数据

预期输出 #

[
  {
    "sku": "65001b733ee01148141e05d2",
    "name": "Apple iPhone 14 Pro Max",
    "category_id": "559"
  },
  {
    "sku": "65018d50a65a5a0b9be7e232_1_1",
    "name": "Nokian Tyres Hakkapeliitta 9",
    "category_id": "602"
  },
  {
    "sku": "65018d50a65a5a0b9be7e232_1_2",
    "name": "Nokian Tyres Hakkapeliitta 10p",
    "category_id": "602"
  },
  {
    "sku": "65019304ff453b7596501dea_1_1",
    "name": "LG 42LA644V LED",
    "category_id": "123"
  }
]

内容的提问来源于stack exchange，提问作者takotsubo