Convert Flat JSON to Nested JSON with arrays-CodePudding

I am facing a problem, transforming flat JSON to the nested JSON using jolt transformation. And I am very new to jolt Transformation. Input and output detail is given below.

My input

[
  {
    "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
  }
]

expected output

{
    "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,
                }
            ]
        }
    ]
}

I wrote jolt spec and I'm not getting the desired output

Jolt i used

[
  {
    "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": {
      "*": ""
    }
  }
]

Can anyone who is a jolt expert, help me get the desired output. I think i m stuck in the last step

CodePudding user response：

You can use

[
  { // group by ProposalId values
    "operation": "shift",
    "spec": {
      "*": {
        "*": "@(1,ProposalId).&",
        "RetailVariantId|Qty": {
          "@": "@(2,ProposalId).RetailVariant[&2].&"
        }
      }
    }
  },
  { // nest all JSON value within Proposal array
    "operation": "shift",
    "spec": {
      "*": "Proposal[]"
    }
  },
  {
    "operation": "cardinality",
    "spec": {
      "*": {
        "*": {
          "*": "ONE",
          "RetailVariant": "MANY"
        }
      }
    }
  },
  { // get rid of redundant nulls
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": "=recursivelySquashNulls"
    }
  }
]

the demo on the site