Flat JSON to Nested JSON on 3 common fields

Question

I am facing a problem, transforming flat JSON to the nested JSON using jolt transformation. Existing JSON is failing

Data should be nested based on DOC_TYPE,VENDOR and PLANT keys

Input

[
  {
    "DOC_TYPE": "ZU15",
    "VENDOR": "0DE1",
    "PLANT": "0DEA",
    "REF_1": "AR1000000061",
    "RSD": "20230405",
    "SALES_CHANNEL": "20",
    "DELIVERY_PRIORITY": "30",
    "ORDER_REASON": "RRU",
    "PO_ITEM": "00010",
    "MATERIAL": "AJ5563310",
    "QUANTITY": "1",
    "GR_PROCESSING": "2",
    "HEADER_TEXT": "Test"
  },
  {
    "DOC_TYPE": "ZU15",
    "VENDOR": "0DE1",
    "PLANT": "0DEA",
    "REF_1": "AR1000000062",
    "RSD": "20230405",
    "SALES_CHANNEL": "20",
    "DELIVERY_PRIORITY": "30",
    "ORDER_REASON": "RRU",
    "PO_ITEM": "00020",
    "MATERIAL": "AC8791590",
    "QUANTITY": "3",
    "GR_PROCESSING": "1",
    "HEADER_TEXT": "Test"
  },
  {
    "DOC_TYPE": "ZU20",
    "VENDOR": "0DE1",
    "PLANT": "0DEA",
    "REF_1": "AR1000000063",
    "RSD": "20230405",
    "SALES_CHANNEL": "20",
    "DELIVERY_PRIORITY": "30",
    "ORDER_REASON": "RRU",
    "PO_ITEM": "00030",
    "MATERIAL": "W27778506",
    "QUANTITY": "4",
    "GR_PROCESSING": "3",
    "HEADER_TEXT": "Test"
  },
  {
    "DOC_TYPE": "ZU20",
    "VENDOR": "0DE1",
    "PLANT": "0DEA",
    "REF_1": "AR1000000064",
    "RSD": "20230405",
    "SALES_CHANNEL": "20",
    "DELIVERY_PRIORITY": "30",
    "ORDER_REASON": "RRU",
    "PO_ITEM": "00040",
    "MATERIAL": "GL8981210",
    "QUANTITY": "2",
    "GR_PROCESSING": "4",
    "HEADER_TEXT": "Test"
  },
  {
    "DOC_TYPE": "ZU25",
    "VENDOR": "0D97",
    "PLANT": "0D99",
    "REF_1": "AR1000000065",
    "RSD": "20230406",
    "SALES_CHANNEL": "20",
    "DELIVERY_PRIORITY": "30",
    "ORDER_REASON": "RRU",
    "PO_ITEM": "00070",
    "MATERIAL": "AJ5563310",
    "QUANTITY": "2",
    "GR_PROCESSING": "",
    "HEADER_TEXT": ""
  }
]

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

Jolt Spec

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "DOC_TYPE": "@(1,DOC_TYPE).&",
        "VENDOR": "@(1,DOC_TYPE).&",
        "REF_1": "@(1,DOC_TYPE).&",
        "PLANT": "@(1,DOC_TYPE).&",
        "RSD": "@(1,DOC_TYPE).&",
        "SALES_CHANNEL": "@(1,DOC_TYPE).&",
        "DELIVERY_PRIORITY": "@(1,DOC_TYPE).&",
        "ORDER_REASON": "@(1,DOC_TYPE).&",
        "HEADER_TEXT": "@(1,DOC_TYPE).&",
        "*": "@(1,DOC_TYPE).Items[&1].&"
      }
    }
  },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": "=recursivelySquashNulls"
    }
  },
  {
    "operation": "cardinality",
    "spec": {
      "*": {
        "DOC_TYPE": "ONE",
        "VENDOR": "ONE",
        "REF_1": "ONE",
        "PLANT": "ONE",
        "RSD": "ONE",
        "SALES_CHANNEL": "ONE",
        "DELIVERY_PRIORITY": "ONE",
        "ORDER_REASON": "ONE",
        "HEADER_TEXT": "ONE"
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": ""
    }
  }
]

Output Needed

[
  {
    "DOC_TYPE": "ZU15",
    "VENDOR": "0DE1",
    "PLANT": "0DEA",
    "REF_1": "AR1000000061",
    "RSD": "20230405",
    "SALES_CHANNEL": "20",
    "DELIVERY_PRIORITY": "30",
    "ORDER_REASON": "RRU",
    "Items": [
      {
        "PO_ITEM": "00010",
        "MATERIAL": "AJ5563310",
        "QUANTITY": "1",
        "GR_PROCESSING": "2"
      },
      {
        "PO_ITEM": "00020",
        "MATERIAL": "AC8791590",
        "QUANTITY": "3",
        "GR_PROCESSING": "1"
      }
    ]
  },
  {
    "DOC_TYPE": "ZU20",
    "VENDOR": "0DE1",
    "PLANT": "0DEA",
    "REF_1": "AR1000000061",
    "RSD": "20230405",
    "SALES_CHANNEL": "20",
    "DELIVERY_PRIORITY": "30",
    "ORDER_REASON": "RRU",
    "Items": [
      {
        "PO_ITEM": "00030",
        "MATERIAL": "W27778506",
        "QUANTITY": "4",
        "GR_PROCESSING": "3"
      },
      {
        "PO_ITEM": "00040",
        "MATERIAL": "GL8981210",
        "QUANTITY": "2",
        "GR_PROCESSING": "4"
      }
    ]
  },
  {
    "DOC_TYPE": "ZU25",
    "VENDOR": "0DE1",
    "PLANT": "0DEA",
    "REF_1": "AR1000000061",
    "RSD": "20230405",
    "SALES_CHANNEL": "20",
    "DELIVERY_PRIORITY": "30",
    "ORDER_REASON": "RRU",
    "Items": [
      {
        "PO_ITEM": "00070",
        "MATERIAL": "AJ5563310",
        "QUANTITY": "4",
        "GR_PROCESSING": "3"
      }
    ]
  }
]

Right now its not coming properly.

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

Answer 1

You can use the following transformation which will pick only first elements from the arrays except for "Items", after grouped into independent objects

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": "@(1,DOC_TYPE).@(1,VENDOR).@(1,PLANT).&",
        "PO_ITEM|MATERIAL|QUANTITY|GR_PROCESSING": "@(1,DOC_TYPE).@(1,VENDOR).@(1,PLANT).Items[&1].&"
      }
    }
  },
  { // get rid of object keys
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "*": {
            "@": ""
          }
        }
      }
    }
  },
  {
    "operation": "cardinality",
    "spec": {
      "*": {
        "*": "ONE",
        "Items": "MANY"
      }
    }
  },
  {// get rid of the "HEADER_TEXT" attribute 
    "operation": "remove",
    "spec": {
      "*": {
        "HEADER_TEXT": ""
      }
    }
  },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": "=recursivelySquashNulls"
    }
  }
]