Convert Flat json to Nested Json with multiple arrays and json should nested with flat json attribute

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.

Input Data is almost same as this question.

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

I am having some trouble with converting the flat JSON to nested JSON. Here, i want to aggregate the data based on stoptype attribute. I use https://jolt-demo.appspot.com to test the following below.

Input Json:

[
  {
    "container_id": "ABC",
    "shipperN": null,
    "PNumber": null,
    "trackingNumber": null,
    "priority": null,
    "loadNumber": "123345",
    "billOfLading": "12345",
    "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",
    "containerNumber": "XXXXXXXX"
  },
  {
    "container_id": "ABC",
    "shipperN": null,
    "PNumber": null,
    "trackingNumber": null,
    "priority": null,
    "loadNumber": "123345",
    "billOfLading": "12345",
    "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",
    "containerNumber": "XXXXXXXX"
  }
]

Expected Output:

[
  {
    "container_id": "ABC",
    "shipperN": null,
    "PNumber": null,
    "trackingNumber": null,
    "priority": null,
    "loadNumber": "123345",
    "billOfLading": "12345",
    "PU": 
      {
        "addressLine1": "ABC Street",
        "city": "Chicago",
        "country": "US",
        "earliestAppointmentTime": "XXXXX09:25",
        "latestAppointmentTime": "XXXXX09:25",
        "postalCode": "XXXXX3",
        "sequence": "1",
        "state": "XY",
        "stopReferenceId": "0001",
        "stopType": "PU"
      },
      "PL" : {
        "earliestAppointmentTime": "XXXXX09:25",
        "latestAppointmentTime": "XXXXX09:25",
        "sequence": "2",
        "stopReferenceId": "dummy",
        "stopType": "PL",
        "externalAddressId": "dummy"
     },
    "containerNumber": "XXXXXXXX"
 }
]

Here it is same jolt spec, i have used for last question. Link: Convert Flat json to Nested Json with multiple arrays and keep null values in output using Jolt transform

As seen, you're trying to remove the attributes with "null" (quoted) values, but "addressLine2" : "null" is still kept within the desired result ... — Barbaros Özhan
– Barbaros Özhan, Commented Sep 6, 2022 at 17:40
Yes, I just edited that null values. can you check it. Here my main aim is to nested the flat json to a attribute of stop type. can you please share the code with expected output — VamsiKumar_gudala
– VamsiKumar_gudala, Commented Sep 7, 2022 at 6:43

Barbaros Özhan · Accepted Answer · 2022-09-13 06:37:37Z

1

You can use the following explained specs

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": "&",
        "addressLine1": "@(1,stopType).&",
        "addressLine2": "@(1,stopType).&",
        "city": "@(1,stopType).&",
        "country": "@(1,stopType).&",
        "*AppointmentTime": "@(1,stopType).&",
        "sequence": "@(1,stopType).&",
        "state": "@(1,stopType).&",
        "stop*": "@(1,stopType).&",
        "post*": "@(1,stopType).&",
        "lat*|lon*|name": { // in order individually to get rid of the undesired null valued attributes
          "$": "@(0)"
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": "&",
      "PU|PL": {
        "*": {
          "$": "&2.&1.@(0)" // in order to get rid of all undesired null valued attributes nested within the subobjects PU and PL
        }
      }
    }
  },
  {
    // revert key-value pairs in order to obtain the originals
    "operation": "shift",
    "spec": {
      "container_id|shipperN|PNumber|trackingNumber|priority|loadNumber|billOfLading": "&", // indeed using "*":"&" is enough, but replaced after wish of sorting the containerNumber attribute at the bottom of the object 
      "PU|PL": {
        "*": {
          "*": {
            "$": "&3.@(0)"
          }
        }
      },
      "containerNumber": "&"
    }
  },
  {
    // reduce only to a single one for the repeating components of the arrays
    "operation": "cardinality",
    "spec": {
      "*": "ONE"
    }
  },
  {
    "operation": "shift",
    "spec": {
      "@": "[]"
    }
  }
]

the demo on the site https://jolt-demo.appspot.com/ is

edited Sep 13, 2022 at 6:37

answered Sep 7, 2022 at 7:17

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

12 Comments

VamsiKumar_gudala Over a year ago

Perfect! Thank you so much @Barbaros. I Understood the code. But How do i get the ""containerNumber": "XXXXXXXX"" at last attribute in json output. It comes in middle of PU and PL

VamsiKumar_gudala Over a year ago

Just now, i have seen. Its working perfect. Thank you @Barbaros Özhan

VamsiKumar_gudala Over a year ago

Hi @Barbaros Özhan. Can you please look into this question

VamsiKumar_gudala Over a year ago

Sure @Barbaros Ozhan . Definitely I will votup. Let me know once you fix this part

VamsiKumar_gudala Over a year ago

Okay. i've posted, please lookin to this question

|

Collectives™ on Stack Overflow

Convert Flat json to Nested Json with multiple arrays and json should nested with flat json attribute

1 Answer 1

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related