Converting flat json to nested json for multiple payloads 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.

Input Data:

[
  {
    "container_id": "ABC_id",
    "shipperN": null,
    "PNumber": null,
    "trackingNumber": null,
    "loadNumber": "BO123345",
    "billOfLading": "BO12345",
    "addressLine1": "ABC Street",
    "city": "ABC_city",
    "country": "ABC_country",
    "earliestAppointmentTime": "XXXXX09:25",
    "postalCode": "XXXX3",
    "sequence": "1",
    "state": "ABC_state",
    "stopReferenceId": "0001",
    "stopType": "PU",
    "containerNumber": "232323"
  },
  {
    "container_id": "ABC_id",
    "shipperN": null,
    "PNumber": null,
    "trackingNumber": null,
    "loadNumber": "BO123345",
    "billOfLading": "BO12345",
    "addressLine1": null,
    "city": "ABC1_city",
    "country": "ABC1_country",
    "earliestAppointmentTime": "XXXXX09:15",
    "postalCode": "XXXX4",
    "sequence": "2",
    "state": "ABC1_state",
    "stopReferenceId": "0002",
    "stopType": "PL",
    "containerNumber": "232323"
  },
  {
    "container_id": "DEF_id",
    "shipperN": null,
    "PNumber": null,
    "trackingNumber": null,
    "loadNumber": "DO123345",
    "billOfLading": "DO12345",
    "addressLine1": null,
    "city": "DEF_city",
    "country": "DEF_country",
    "earliestAppointmentTime": "XXXXX08:15",
    "postalCode": "XXXX5",
    "sequence": "4",
    "state": "DEF_state",
    "stopReferenceId": "0003",
    "stopType": "PU",
    "containerNumber": "454545"
  },
  {
    "container_id": "DEF_id",
    "shipperN": null,
    "PNumber": null,
    "trackingNumber": null,
    "loadNumber": "DO123345",
    "billOfLading": "DO12345",
    "addressLine1": "DEF_address",
    "city": "DEF1_city",
    "country": "DEF_country",
    "earliestAppointmentTime": "XXXXX07:15",
    "postalCode": "XXXX6",
    "sequence": "5",
    "state": "DEF_state",
    "stopReferenceId": "0004",
    "stopType": "PL",
    "containerNumber": "454545"
  }
]

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

Output:

[
  {
    "container_id": "ABC_id",
    "shipperN": null,
    "PNumber": null,
    "trackingNumber": null,
    "loadNumber": "BO123345",
    "billOfLading": "BO12345",
    "PU": {
      "addressLine1": "ABC Street",
      "city": "ABC_city",
      "country": "ABC_country",
      "earliestAppointmentTime": "XXXXX09:25",
      "postalCode": "XXXX3",
      "sequence": "1",
      "state": "ABC_state",
      "stopReferenceId": "0001",
      "stopType": "PU"
    },
    "PL": {
      "addressLine1": null,
      "city": "ABC1_city",
      "country": "ABC1_country",
      "earliestAppointmentTime": "XXXXX09:15",
      "postalCode": "XXXX4",
      "sequence": "2",
      "state": "ABC1_state",
      "stopReferenceId": "0002",
      "stopType": "PL"
    },
    "containerNumber": "232323"
  },
  {
    "container_id": "DEF_id",
    "shipperN": null,
    "PNumber": null,
    "trackingNumber": null,
    "loadNumber": "DO123345",
    "billOfLading": "DO12345",
    "PU": {
      "addressLine1": null,
      "city": "DEF_city",
      "country": "DEF_country",
      "earliestAppointmentTime": "XXXXX08:15",
      "postalCode": "XXXX5",
      "sequence": "4",
      "state": "DEF_state",
      "stopReferenceId": "0003",
      "stopType": "PU"
    },
    "PL": {
      "addressLine1": "DEF_address",
      "city": "DEF1_city",
      "country": "DEF1_country",
      "earliestAppointmentTime": "XXXXX07:15",
      "postalCode": "XXXX6",
      "sequence": "5",
      "state": "DEF_state",
      "stopReferenceId": "0004",
      "stopType": "PL"
    },
    "containerNumber": "454545"
  }
]

can you please help me with this expected output.

Barbaros Özhan · Accepted Answer · 2022-09-13 12:44:29Z

1

The trick is partitioning all of the attributes by @(1,container_id) and by @(1,stopType) for those to be nested within the sub-objects determined by the values of the stopType attributes such as

[
  {
    // separate by @(1,container_id) and @(1,stopType) for attributes to be nested
    "operation": "shift",
    "spec": {
      "*": {
        "container_id": "@(1,container_id).&",
        "shipperN": "@(1,container_id).&",
        "PNumber": "@(1,container_id).&",
        "trackingNumber": "@(1,container_id).&",
        "loadNumber": "@(1,container_id).&",
        "billOfLading": "@(1,container_id).&",
        "addressLine1": "@(1,container_id).@(1,stopType).&",
        "city": "@(1,container_id).@(1,stopType).&",
        "country": "@(1,container_id).@(1,stopType).&",
        "earliestAppointmentTime": "@(1,container_id).@(1,stopType).&",
        "postalCode": "@(1,container_id).@(1,stopType).&",
        "sequence": "@(1,container_id).@(1,stopType).&",
        "state": "@(1,container_id).@(1,stopType).&",
        "stop*": "@(1,container_id).@(1,stopType).&",
        "containerNumber": "@(1,container_id).&"
      }
    }
  },
  {
    // reduce only to a single one for the repeating components of the arrays
    "operation": "cardinality",
    "spec": {
      "*": {
        "*": "ONE"
      }
    }
  },
  {
    // get rid of object labels
    "operation": "shift",
    "spec": {
      "*": "[]"
    }
  }
]

answered Sep 13, 2022 at 12:44

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

1 Comment

VamsiKumar_gudala Over a year ago

Thank you so much @Barbaros Özhan. I understood the code. I have tried the same thing for another question. But I'm getting the different output. Please check the question here i have posted

Collectives™ on Stack Overflow

Converting flat json to nested json for multiple payloads using jolt transform

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related