Flatten a complex nested JSON using jolt transform

Question

I am facing a problem, transforming a very complex nested JSON using jolt transformation. Input and output detail is given below.

Input JSON

{
  "OutboundSchedulingLT": {
    "DepertureSite": "0DE1",
    "ShippingPoint": "010D",
    "LeadTimes": {
      "TransportationLT": [
        {
          "DestinationSite": "0DEA",
          "DestinationLT": [
            {
              "ShippingCondition": "01",
              "DestinationSiteLT": "3"
            },
            {
              "ShippingCondition": "02",
              "DestinationSiteLT": "4"
            },
            {
              "ShippingCondition": "OA",
              "DestinationSiteLT": "2"
            }
          ]
        },
        {
          "DestinationSite": "0DEB",
          "DestinationLT": [
            {
              "ShippingCondition": "01",
              "DestinationSiteLT": "1"
            },
            {
              "ShippingCondition": "02",
              "DestinationSiteLT": "2"
            },
            {
              "ShippingCondition": "OA",
              "DestinationSiteLT": "3"
            }
          ]
        }
      ],
      "DNCreationLeadTimes": {
        "DNCreationLeadTime": [
          {
            "DistributionChannel": "40",
            "OrderType": "ZFUT",
            "OrderReason": "",
            "DNCreationLT": "3"
          },
          {
            "DistributionChannel": "40",
            "OrderType": "ZOR",
            "OrderReason": "",
            "DNCreationLT": "3"
          },
          {
            "DistributionChannel": "70",
            "OrderType": "ZU20",
            "OrderReason": "RDC",
            "DNCreationLT": "1"
          }
        ]
      },
      "DNProcessingLeadTimes": {
        "DNProcessingLeadTime": [
          {
            "DistributionChannel": "40",
            "OrderType": "ZFUT",
            "OrderReason": "",
            "DNprocessingLT": "2"
          },
          {
            "DistributionChannel": "40",
            "OrderType": "ZOR",
            "OrderReason": "",
            "DNprocessingLT": "4"
          },
          {
            "DistributionChannel": "70",
            "OrderType": "ZU20",
            "OrderReason": "RDC",
            "DNprocessingLT": "3"
          }
        ]
      },
      "DNLoadingLeadTimes": {
        "DNLoadingLeadTime": [
          {
            "DistributionChannel": "40",
            "ShippingCondition": "O1",
            "OrderType": "ZFUT",
            "OrderReason": "",
            "DNLoadingLT": "3"
          },
          {
            "DistributionChannel": "40",
            "ShippingCondition": "O1",
            "OrderType": "ZOR",
            "OrderReason": "",
            "DNLoadingLT": "3"
          },
          {
            "DistributionChannel": "70",
            "ShippingCondition": "O1",
            "OrderType": "ZU20",
            "OrderReason": "RDC",
            "DNLoadingLT": "1"
          }
        ]
      },
      "VASLeadTimes": {
        "VASLeadTime": [
          {
            "VASCode": "L01",
            "VASLT": "2"
          },
          {
            "VASCode": "P02",
            "VASLT": "3"
          }
        ]
      }
    }
  }
}

I want the below columns in flattened way

DepertureSite,DestinationSite,DestinationSiteLT,DistributionChannel,OrderType,OrderReason,DNCreationLT,DNprocessingLT,DNLoadingLT,VASCode,VASLT

Jolt Spec I tried

[
  {
    "operation": "shift",
    "spec": {
      "OutboundSchedulingLT": {
        "DepertureSite": "DepertureSite",
        "LeadTimes": {
          "TransportationLT": {
            "*": {
              "DestinationSite": "DestinationSite",
              "DestinationLT": {
                "*": {
                  "DestinationSiteLT": "DestinationSiteLT"
                }
              }
            }
          },
          "DNCreationLeadTimes": {
            "DNCreationLeadTime": {
              "*": {
                "DistributionChannel": "DistributionChannel",
                "OrderType": "OrderType",
                "OrderReason": "OrderReason",
                "DNCreationLT": "DNCreationLT"
              }
            }
          },
          "DNProcessingLeadTimes": {
            "DNProcessingLeadTime": {
              "*": {
                "DNprocessingLT": "DNprocessingLT"
              }
            }
          },
          "DNLoadingLeadTimes": {
            "DNLoadingLeadTime": {
              "*": {
                "DNLoadingLT": "DNLoadingLT"
              }
            }
          },
          "VASLeadTimes": {
            "VASLeadTime": {
              "*": {
                "VASCode": "VASCode",
                "VASLT": "VASLT"
              }
            }
          }
        }
      }
    }
  }
]

But its not coming as expected.

Pls help

Output Expected

[
  {
    "DepertureSite": "0DE1",
    "DestinationSite": "0DEA",
    "DestinationSiteLT": "3",
    "DistributionChannel": "40",
    "OrderType": "ZOR",
    "OrderReason": "",
    "DNCreationLT": "3",
    "DNprocessingLT": "2",
    "DNLoadingLT": "3",
    "VASCode": "L01",
    "VASLT": "2"
  },
  {
    "DepertureSite": "0DE1",
    "DestinationSite": "0DEA",
    "DestinationSiteLT": "4",
    "DistributionChannel": "70",
    "OrderType": "ZU20",
    "OrderReason": "RDC",
    "DNprocessingLT": "4",
    "DNLoadingLT": "3",
    "VASCode": "L01",
    "VASLT": "2"
  },
  {
    "DepertureSite": "0DE1",
    "DestinationSite": "0DEA",
    "DestinationSiteLT": "2",
    "DistributionChannel": "70",
    "OrderType": "ZU20",
    "OrderReason": "RDC",
    "DNCreationLT": "1",
    "DNprocessingLT": "3",
    "DNLoadingLT": "1",
    "VASCode": "L01",
    "VASLT": "2"
  },
  {
    "DepertureSite": "0DE1",
    "DestinationSite": "0DEB",
    "DestinationSiteLT": "1",
    "DistributionChannel": "40",
    "OrderType": "ZOR",
    "OrderReason": "",
    "DNCreationLT": "3",
    "DNprocessingLT": "2",
    "DNLoadingLT": "3",
    "VASCode": "P02",
    "VASLT": "3"
  },
  {
    "DepertureSite": "0DE1",
    "DestinationSite": "0DEB",
    "DestinationSiteLT": "2",
    "DistributionChannel": "70",
    "OrderType": "ZU20",
    "OrderReason": "RDC",
    "DNprocessingLT": "4",
    "DNLoadingLT": "3",
    "VASCode": "P02",
    "VASLT": "3"
  },
  {
    "DepertureSite": "0DE1",
    "DestinationSite": "0DEB",
    "DestinationSiteLT": "3",
    "DistributionChannel": "70",
    "OrderType": "ZU20",
    "OrderReason": "RDC",
    "DNCreationLT": "1",
    "DNprocessingLT": "3",
    "DNLoadingLT": "1",
    "VASCode": "P02",
    "VASLT": "3"
  }
]

Answer 1

You can use the following shift transformation as walking through the DestinationLT object spec

[
  {
    "operation": "shift",
    "spec": {
      "OutboundSchedulingLT": {
        "LeadTimes": {
          "TransportationLT": {
            "*": {
              "DestinationLT": {
                "*": {
                  "@5,DepertureSite": "&3[#2].DepertureSite",
                  "@2,DestinationSite": "&3[#2].DestinationSite",
                  "DestinationSiteLT": "&3[#2].&",
                  "@4,DNCreationLeadTimes.DNCreationLeadTime[&].DistributionChannel": "&3[#2].DistributionChannel",
                  "@4,DNCreationLeadTimes.DNCreationLeadTime[&].OrderType": "&3[#2].OrderType",
                  "@4,DNCreationLeadTimes.DNCreationLeadTime[&].OrderReason": "&3[#2].OrderReason",
                  "@4,DNCreationLeadTimes.DNCreationLeadTime[&].DNCreationLT": "&3[#2].DNCreationLT",
                  "@4,DNProcessingLeadTimes.DNCreationLeadTime[&].DNProcessingLeadTime": "&3[#2].DNprocessingLT",
                  "@4,DNLoadingLeadTimes.DNLoadingLeadTime[&].DNLoadingLT": "&3[#2].DNLoadingLT",
                  "@4,VASLeadTimes.VASLeadTime[&].VASCode": "&3[#2].VASCode",
                  "@4,VASLeadTimes.VASLeadTime[&].VASLT": "&3[#2].VASLT"
                }
              }
            }
          }
        }
      }
    }
  },
  {// get rid of the object keys generated within the previous spec
    "operation": "shift",
    "spec": {
      "*": {
        "*": ""
      }
    }
  }
]