求助：使用Jolt将扁平JSON转换为带数组的嵌套JSON

我是Jolt Transformation新手，目前在将扁平JSON转换为带数组的嵌套JSON时遇到问题，以下是详细的输入、预期输出及我尝试的Jolt规格，但未得到预期结果，恳请帮忙解决。

输入JSON #

[
  {
    "ProposalId": "1234",
    "ReplStrategy": "External",
    "CreatedDate": "2022-10-26",
    "ValidFromDate": "2022-10-26",
    "DeliveryDate": "2022-10-29",
    "InventLocationIdFrom": "10",
    "InventLocationIdTo": "12",
    "RetailVariantId": "123457",
    "Qty": 12
  },
  {
    "ProposalId": "1234",
    "ReplStrategy": "External",
    "CreatedDate": "2022-10-26",
    "ValidFromDate": "2022-10-26",
    "DeliveryDate": "2022-10-29",
    "InventLocationIdFrom": "10",
    "InventLocationIdTo": "12",
    "RetailVariantId": "123458",
    "Qty": 16
  },
  {
    "ProposalId": "1235",
    "ReplStrategy": "External",
    "CreatedDate": "2022-10-26",
    "ValidFromDate": "2022-10-26",
    "DeliveryDate": "2022-10-29",
    "InventLocationIdFrom": "10",
    "InventLocationIdTo": "12",
    "RetailVariantId": "123459",
    "Qty": 12
  },
  {
    "ProposalId": "1235",
    "ReplStrategy": "External",
    "CreatedDate": "2022-10-26",
    "ValidFromDate": "2022-10-26",
    "DeliveryDate": "2022-10-29",
    "InventLocationIdFrom": "10",
    "InventLocationIdTo": "12",
    "RetailVariantId": "123460",
    "Qty": 16
  },
  {
    "ProposalId": "1235",
    "ReplStrategy": "External",
    "CreatedDate": "2022-10-26",
    "ValidFromDate": "2022-10-26",
    "DeliveryDate": "2022-10-29",
    "InventLocationIdFrom": "10",
    "InventLocationIdTo": "12",
    "RetailVariantId": "123461",
    "Qty": 16
  }
]

预期输出JSON #

{
    "Proposal": [
        {
            "ProposalId": "1234",
            "ReplStrategy": "External",
            "CreatedDate": "2022-10-26",
            "ValidFromDate": "2022-10-26",
            "DeliveryDate": "2022-10-29",
            "InventLocationIdFrom": "10",
            "InventLocationIdTo": "12",
            "RetailVariant": [
                {
                    "RetailVariantId": "123456",
                    "Qty": 15
                },
                {
                    "RetailVariantId": "123457",
                    "Qty": 12
                }
            ]
        },
        {
            "ProposalId": "1235",
            "ReplStrategy": "TwoPhased",
            "CreatedDate": "2022-10-26",
            "ValidFromDate": "2022-10-26",
            "DeliveryDate": "2022-10-29",
            "InventLocationIdFrom": "10",
            "InventLocationIdTo": "12",
            "RetailVariant": [
                {
                    "RetailVariantId": "123458",
                    "Qty": 13
                },
                {
                    "RetailVariantId": "123459",
                    "Qty": 11
                }
            ]
        }
    ]
}

尝试的Jolt规格 #

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "ProposalId": "@(1,ProposalId).Proposal.&",
        "ReplStrategy": "@(1,ProposalId).Proposal.&",
        "CreatedDate": "@(1,ProposalId).Proposal.&",
        "ValidFromDate": "@(1,ProposalId).Proposal.&",
        "DeliveryDate": "@(1,ProposalId).Proposal.&",
        "InventLocationIdFrom": "@(1,ProposalId).Proposal.&",
        "InventLocationIdTo": "@(1,ProposalId).Proposal.&",
        "*": "@(1,ProposalId).Proposal.RetailVariant[&1].&"
      }
    }
  },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": "=recursivelySquashNulls"
    }
  },
  {
    "operation": "cardinality",
    "spec": {
      "*": {
        "*": {
          "ProposalId": "ONE",
          "ReplStrategy": "ONE",
          "CreatedDate": "ONE",
          "ValidFromDate": "ONE",
          "DeliveryDate": "ONE",
          "InventLocationIdFrom": "ONE",
          "InventLocationIdTo": "ONE"
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": ""
    }
  }
]

修正后的Jolt规格 #

[
  // 按ProposalId分组，提取公共字段和变体数组
  {
    "operation": "shift",
    "spec": {
      "*": {
        "ProposalId": "@(1,ProposalId).&",
        "ReplStrategy": "@(1,ProposalId).&",
        "CreatedDate": "@(1,ProposalId).&",
        "ValidFromDate": "@(1,ProposalId).&",
        "DeliveryDate": "@(1,ProposalId).&",
        "InventLocationIdFrom": "@(1,ProposalId).&",
        "InventLocationIdTo": "@(1,ProposalId).&",
        "RetailVariantId": "@(1,ProposalId).RetailVariant[#2].&",
        "Qty": "@(1,ProposalId).RetailVariant[#2].&"
      }
    }
  },
  // 去重公共字段，确保每个ProposalId下的公共字段唯一
  {
    "operation": "cardinality",
    "spec": {
      "*": {
        "ProposalId": "ONE",
        "ReplStrategy": "ONE",
        "CreatedDate": "ONE",
        "ValidFromDate": "ONE",
        "DeliveryDate": "ONE",
        "InventLocationIdFrom": "ONE",
        "InventLocationIdTo": "ONE"
      }
    }
  },
  // 将分组后的结构整理成预期的Proposal数组
  {
    "operation": "shift",
    "spec": {
      "*": "Proposal[]"
    }
  }
]

调整说明 #

1. 第一步Shift操作：直接以ProposalId作为分组键，避免多余层级；使用[#2]作为变体数组索引，确保同一ProposalId下的变体依次进入数组。
2. Cardinality操作：直接作用于分组后的顶层对象，简化路径，确保公共字段仅保留一个。
3. 最后一步Shift操作：将所有分组后的对象收集到Proposal数组中，直接匹配预期的顶层结构。

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