Convert Flat json to Nested Json with multiple arrays and keep null values in output using Jolt transform

Question

I'm trying to write a spec to do the below transformation using jolt transformation. I need to convert the flat JSON to nested JSON by keeping null values. I attached the input, expected output and jolt transform. I need to keep the null values in the output but it doesn't show in output after jolt transform. I didn't get exact output with my jolt transform.

I am having some trouble with converting the flat JSON to nested JSON. I have looked at examples and didn't get any closer as to what is mentioned above. I need to transform a JSON structure by using a JOLT spec. I use https://jolt-demo.appspot.com to test the following below.

Input:

[
  {
    "container_id": "ABC",
    "shipperN": null,
    "PNumber": null,
    "trackingNumber": null,
    "priority": null,
    "HType": "IN_Load",
    "loadNumber": "123345",
    "billOfLading": "12345",
    "referenceNumbers": "LID",
    "addressLine1": "ABC Street",
    "addressLine2": "null",
    "city": "Chicago",
    "country": "US",
    "latitude": "null",
    "longitude": "null",
    "earliestAppointmentTime": "XXXXX09:25",
    "latestAppointmentTime": "XXXXX09:25",
    "postalCode": "XXXXX3",
    "sequence": "1",
    "state": "XY",
    "stopReferenceId": "0001",
    "stopType": "PU",
    "truckNumber": null,
    "trailerNumber": null,
    "driverPhone": null,
    "railEquipmentInitials": null,
    "railEquipmentNumber": null,
    "containerNumber": "XXXXXXXX"
  },
  {
    "container_id": "ABC",
    "shipperN": null,
    "PNumber": null,
    "trackingNumber": null,
    "priority": null,
    "HType": "IN_Load",
    "loadNumber": "123345",
    "billOfLading": "12345",
    "referenceNumbers": "LID",
    "addressLine1": "null",
    "addressLine2": "null",
    "city": "null",
    "country": "null",
    "latitude": null,
    "longitude": null,
    "earliestAppointmentTime": "XXXXX09:25",
    "latestAppointmentTime": "XXXXX09:25",
    "name": "null",
    "postalCode": "null",
    "sequence": "2",
    "state": "null",
    "stopReferenceId": "XXXXD",
    "stopType": "PL",
    "truckNumber": null,
    "trailerNumber": null,
    "driverPhone": null,
    "railEquipmentInitials": null,
    "railEquipmentNumber": null,
    "containerNumber": "XXXXXXXX"
  }
]

Desired Output:

{
  "load": {
    "container_id": "ABC",
    "shipperN": null,
    "PNumber": null,
    "trackingNumber": null,
    "priority": null,
    "HType": [ "IN_Load" ],
    "loadNumber": "123345",
    "billOfLading": "12345",
    "referenceNumbers": [ "LID" ],
    "stops": [
      {
        "addressLine1": "ABC Street",
        "addressLine2": "null",
        "city": "Chicago",
        "country": "US",
        "earliestAppointmentTime": "XXXXX09:25",
        "latestAppointmentTime": "XXXXX09:25",
        "postalCode": "XXXXX3",
        "sequence": "1",
        "state": "XY",
        "stopReferenceId": "0001",
        "stopType": "PU"
      },
      {
        "earliestAppointmentTime": "2021-03-09T15:25:00.203Z",
        "latestAppointmentTime": "2021-03-09T15:25:00.203Z",
        "sequence": "2",
        "stopReferenceId": "dummy",
        "stopType": "PL",
        "externalAddressId": "dummy"
      }
    ]
  },
  "containerInfo": {
    "containerNumber": "XXXXXXXX"
  },
  "trackingInfo": {
    "truckNumber": null,
    "trailerNumber": null,
    "driverPhone": null,
    "railEquipmentInitials": null,
    "railEquipmentNumber": null
  }
}

Jolt Spec that I'm using :

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": "@(1,container_id).load.stops[&1].&",
        "container_id": "@(1,container_id).load.&", // "else" case
        "shipperN": "@(1,container_id).load.&",
        "PNumber": "@(1,container_id).load.&",
        "trackingNumber": "@(1,container_id).load.&",
        "priority": "@(1,container_id).load.&",
        "HType": "@(1,container_id).load.&",
        "loadNumber": "@(1,container_id).load.&",
        "billOfLading": "@(1,container_id).load.&",
        "referenceNumbers": "@(1,container_id).load.&",
        "containerNumber": "@(1,container_id).containerInfo.&",
        "truckNumber": "@(1,container_id).trackingInfo.&",
        "trailerNumber": "@(1,container_id).trackingInfo.&",
        "driverPhone": "@(1,container_id).trackingInfo.&",
        "railEquipmentInitials": "@(1,container_id).trackingInfo.&",
        "railEquipmentNumber": "@(1,container_id).trackingInfo.&"
      }
    }
  },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": "=recursivelySquashNulls"
    }
  },
  {
    "operation": "cardinality",
    "spec": {
      "*": {
        "*": {
          "container_id": "ONE",
          "shipperN": "ONE",
          "PNumber": "ONE",
          "trackingNumber": "ONE",
          "priority": "ONE",
          "HType": "ONE",
          "referenceNumbers": "ONE",
          "loadNumber": "ONE",
          "billOfLading": "ONE",
          "containerInfo": {
            "*": "ONE"
          },
          "trackingInfo": {
            "*": "ONE"
          }
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": ""
    }
  }
]

the nulls are lost because of using recursivelySquashNulls ... — Barbaros Özhan
– Barbaros Özhan, Commented Sep 2, 2022 at 14:10
Thanks @BarbarosÖzhan. But I need the HType and referenceNumbers in list object type. So, i used this ' "HType": "@(1,container_id).load.[&1].&" ` but it doesn't work and I'm getting containerinfo attributes multiple times as well. Can you please share the changed jolt transform — VamsiKumar_gudala
– VamsiKumar_gudala, Commented Sep 3, 2022 at 7:10

Barbaros Özhan · Accepted Answer · 2022-09-05 07:19:17Z

1

You're so close;

The spec containing recursivelySquashNulls should be removed
The identifier .&[] should be used proper to the attributes HType and referenceNumbers
The cardinality spec preferably be shortened

So use the following as a whole spec

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": "@(1,container_id).load.stops[&1].&",
        "container_id": "@(1,container_id).load.&", // "else" case
        "shipperN": "@(1,container_id).load.&",
        "PNumber": "@(1,container_id).load.&",
        "trackingNumber": "@(1,container_id).load.&",
        "priority": "@(1,container_id).load.&",
        "HType": "@(1,container_id).load.&",
        "loadNumber": "@(1,container_id).load.&",
        "billOfLading": "@(1,container_id).load.&",
        "referenceNumbers": "@(1,container_id).load.&",
        "containerNumber": "@(1,container_id).containerInfo.&",
        "truckNumber": "@(1,container_id).trackingInfo.&",
        "trailerNumber": "@(1,container_id).trackingInfo.&",
        "driverPhone": "@(1,container_id).trackingInfo.&",
        "railEquipmentInitials": "@(1,container_id).trackingInfo.&",
        "railEquipmentNumber": "@(1,container_id).trackingInfo.&"
      }
    }
  },
  {
    "operation": "cardinality",
    "spec": {
      "*": {
        "*": {
          "*": "ONE",
          "stops": "MANY"
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": "&",
        "load": {
          "HType|referenceNumbers": "&1.&[]", 
          "*": "&1.&" // &1 stands for the key "load", and & replicates the leaf values  
        }
      }
    }
  }
]

edited Sep 5, 2022 at 7:19

answered Sep 4, 2022 at 13:49

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

VamsiKumar_gudala Over a year ago

Thanks for the code @BarbarosÖzhan. But the code takes only one input. I mean in the stops attribute, its gives only one output in the stop. It is not giving all the stops. Can you please check it

Barbaros Özhan Over a year ago

Excuse me @VamsiKumar_gudala I hadn't noticed then, just now fixed.

VamsiKumar_gudala Over a year ago

Thank you so much @BarbarosÖzhan. I got the expected output. I learning about jolt, if you have any documents or notes , can you suggest some materials to look

Barbaros Özhan Over a year ago

Hi @VamsiKumar_gudala , you're welcome. Surely I can suggest some useful ones of those : 1 , 2, 3, 4, 5. Have a nice day!

VamsiKumar_gudala Over a year ago

Hi @BarbarosÖzhan. Thanks for the article links. Now I'm trying to get the output in different way. Instead of stops in single attribute i want it in the two attributes based on stop type. 'code' "PU": { "addressLine1": "ABC Street", ......... }, "PL" : { "earliestAppointmentTime": "2021-03-09T15:25:00.203Z", "latestAppointmentTime": "2021-03-09T15:25:00.203Z", ......... } How can I get specific attribute based on one type attribute?

|

Collectives™ on Stack Overflow

Convert Flat json to Nested Json with multiple arrays and keep null values in output using Jolt transform

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related