Question posted in Json
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Flattening a nested JSON using jolt transform – Very complex

ThiyagarajNarayanan
September 18, 2023
121 views
0 votes
2 Answers

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

Input

[
  {
    "metadata": {
      "event_time": "2023-09-04T08:46:15.0842Z"
    },
    "payload": [
      {
        "plant": "0DE1",
        "store": "",
        "t_objectandcharvalcomb": [
          {
            "productalloccharacteristic01": "0DE1",
            "productalloccharacteristic02": "30",
            "productalloccharacteristic03": "FC",
            "productalloccharacteristic04": "#",
            "t_time_series_consumption": [
              {
                "startdateofproductallocperd": "2023-09-04",
                "prodallocconsumpnegopenqty": 0,
                "prodallocconsumpopenqty": 0,
                "prodallocconsumpassigqty": 0,
                "prodallocconsumpqty": 0,
                "prodallocconsumpqtyunit": "EA"
              },
              {
                "startdateofproductallocperd": "2023-09-05",
                "prodallocconsumpnegopenqty": 0,
                "prodallocconsumpopenqty": 0,
                "prodallocconsumpassigqty": 0,
                "prodallocconsumpqty": 0,
                "prodallocconsumpqtyunit": "EA"
              }
            ]
          }
        ]
      },
      {
        "plant": "0DE1",
        "store": "",
        "t_objectandcharvalcomb": [
          {
            "productalloccharacteristic01": "0DE1",
            "productalloccharacteristic02": "30",
            "productalloccharacteristic03": "LT",
            "t_time_series_consumption": [
              {
                "startdateofproductallocperd": "2023-09-04",
                "prodallocconsumpnegopenqty": 0,
                "prodallocconsumpopenqty": 0,
                "prodallocconsumpassigqty": 0,
                "prodallocconsumpqty": 0,
                "prodallocconsumpqtyunit": "EA"
              },
              {
                "startdateofproductallocperd": "2023-09-05",
                "prodallocconsumpnegopenqty": 0,
                "prodallocconsumpopenqty": 0,
                "prodallocconsumpassigqty": 0,
                "prodallocconsumpqty": 0,
                "prodallocconsumpqtyunit": "EA"
              }
            ]
          }
        ]
      },
      {
        "plant": "0DE1",
        "store": "",
        "t_objectandcharvalcomb": [
          {
            "productalloccharacteristic01": "0DE1",
            "productalloccharacteristic02": "30",
            "productalloccharacteristic03": "PP",
            "productalloccharacteristic04": "#",
            "t_time_series_consumption": [
              {
                "startdateofproductallocperd": "2023-09-04",
                "startdateofprodallocconsump": "2023-09-04",
                "prodallocconsumpnegopenqty": 0,
                "prodallocconsumpopenqty": 0,
                "prodallocconsumpassigqty": 0,
                "prodallocconsumpqty": 0,
                "prodallocconsumpqtyunit": "EA"
              },
              {
                "startdateofproductallocperd": "2023-09-05",
                "prodallocconsumpnegopenqty": 0,
                "prodallocconsumpopenqty": 0,
                "prodallocconsumpassigqty": 0,
                "prodallocconsumpqty": 0,
                "prodallocconsumpqtyunit": "EA"
              }
            ]
          }
        ]
      }
    ]
  }
]

Output Expected

[
  {
    "event_time": "2023-09-04T08:46:15.0842Z",
    "Plant": "0DE1",
    "Store": "",
    "Location": "0DEA",
    "Distribution_Channel": "#",
    "Packing_type": "FC",
    "Product_Division": "",
    "Date": "2023-09-04",
    "Planned_Capacity_qty": 0,
    "Consumed_Capacity_qty": 0,
    "Negative_open_qty": 0,
    "Consumed_assign_qty": 0
  },
  {
    "event_time": "2023-09-04T08:46:15.0842Z",
    "Plant": "0DE1",
    "Store": "",
    "Location": "0DEA",
    "Distribution_Channel": "#",
    "Packing_type": "FC",
    "Product_Division": "",
    "Date": "2023-09-05",
    "Planned_Capacity_qty": 0,
    "Consumed_Capacity_qty": 0,
    "Negative_open_qty": 0,
    "Consumed_assign_qty": 0
  },
  {
    "event_time": "2023-09-04T08:46:15.0842Z",
    "Plant": "0DE1",
    "Store": "",
    "Location": "0DEA",
    "Distribution_Channel": "#",
    "Packing_type": "LT",
    "Product_Division": "",
    "Date": "2023-09-04",
    "Planned_Capacity_qty": 0,
    "Consumed_Capacity_qty": 0,
    "Negative_open_qty": 0,
    "Consumed_assign_qty": 0
  },
  {
    "event_time": "2023-09-04T08:46:15.0842Z",
    "Plant": "0DE1",
    "Store": "",
    "Location": "0DEA",
    "Distribution_Channel": "#",
    "Packing_type": "LT",
    "Product_Division": "",
    "Date": "2023-09-05",
    "Planned_Capacity_qty": 0,
    "Consumed_Capacity_qty": 0,
    "Negative_open_qty": 0,
    "Consumed_assign_qty": 0
  },
  {
    "event_time": "2023-09-04T08:46:15.0842Z",
    "Plant": "0DE1",
    "Store": "",
    "Location": "0DEA",
    "Distribution_Channel": "#",
    "Packing_type": "PP",
    "Product_Division": "",
    "Date": "2023-09-04",
    "Planned_Capacity_qty": 0,
    "Consumed_Capacity_qty": 0,
    "Negative_open_qty": 0,
    "Consumed_assign_qty": 0
  },
  {
    "event_time": "2023-09-04T08:46:15.0842Z",
    "Plant": "0DE1",
    "Store": "",
    "Location": "0DEA",
    "Distribution_Channel": "#",
    "Packing_type": "PP",
    "Product_Division": "",
    "Date": "2023-09-05",
    "Planned_Capacity_qty": 0,
    "Consumed_Capacity_qty": 0,
    "Negative_open_qty": 0,
    "Consumed_assign_qty": 0
  }
]

Jolt spec which i m using

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "payload": {
          "*": {
            "t_objectandcharvalcomb": {
              "*": {
                "t_time_series_consumption": {
                  "*": {
                    "@(6,metadata.event_time)": "[&1].event_time",
                    "@(4,plant)": "[&1].Plant",
                    "@(4,store)": "[&1].Store",
                    "@(2,productalloccharacteristic01)": "[&1].Location",
                    "@(2,productalloccharacteristic02)": "[&1].Distribution_Channel",
                    "@(2,productalloccharacteristic03)": "[&1].Packing_type",
                    "@(2,productalloccharacteristic04)": "[&1].Product_Division",
                    "startdateofproductallocperd": "[&1].Date",
                    "productallocquantity": "[&1].Planned_Capacity_qty",
                    "prodallocconsumpopenqty": "[&1].Consumed_Capacity_qty",
                    "prodallocconsumpnegopenqty": "[&1].Negative_open_qty",
                    "prodallocconsumpassigqty": "[&1].Consumed_assign_qty"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
]

But its not coming as expected.

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

Answers

Need to add one more partition which might be provided by adding &5 along with the current identifier &1, which has not to be nested within square brackets, such as

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "payload": {
          "*": {
            "t_objectandcharvalcomb": {
              "*": {
                "t_time_series_consumption": {
                  "*": {
                    "@6,metadata.event_time": "&5_&1.event_time",
                    "@4,plant": "&5_&1.Plant",
                    "@4,store": "&5_&1.Store",
                    "@2,productalloccharacteristic01": "&5_&1.Location",
                    "@2,productalloccharacteristic02": "&5_&1.Distribution_Channel",
                    "@2,productalloccharacteristic03": "&5_&1.Packing_type",
                    "@2,productalloccharacteristic04": "&5_&1.Product_Division",
                    "startdateofproductallocperd": "&5_&1.Date",
                    "productallocquantity": "&5_&1.Planned_Capacity_qty",
                    "prodallocconsumpopenqty": "&5_&1.Consumed_Capacity_qty",
                    "prodallocconsumpnegopenqty": "&5_&1.Negative_open_qty",
                    "prodallocconsumpassigqty": "&5_&1.Consumed_assign_qty"
                  }
                }
              }
            }
          }
        }
      }
    }
  },
  { // get rid of the object keys
    "operation": "shift",
    "spec": {
      "*": ""
    }
  }
]

Your input has 3 payload objects, each with 2 t_time_series_consumption objects, and you want to generate 6 objects from them. Unfortunately in JOLT I don’t believe there is a way to keep incrementing the output index when the internal objects have multiple fields. Using [] will keep incrementing the output index for each field you’re trying to add, and neither the payload index nor the t_time_series_consumption indexes are correct, rather the correct output index is the (payload index * 2) + t_time_series_consumption index, which I don’t believe you can represent in JOLT.

Instead if you put all the output fields into a single object, each field will contain an array of the correct size and values. Then you can iterate through each array and use that index to specify the output index. You can use the following spec:

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "payload": {
          "*": {
            "t_objectandcharvalcomb": {
              "*": {
                "t_time_series_consumption": {
                  "*": {
                    "@(6,metadata.event_time)": "event_time",
                    "@(4,plant)": "Plant",
                    "@(4,store)": "Store",
                    "@(2,productalloccharacteristic01)": "Location",
                    "@(2,productalloccharacteristic02)": "Distribution_Channel",
                    "@(2,productalloccharacteristic03)": "Packing_type",
                    "@(2,productalloccharacteristic04)": "Product_Division",
                    "startdateofproductallocperd": "Date",
                    "productallocquantity": "Planned_Capacity_qty",
                    "prodallocconsumpopenqty": "Consumed_Capacity_qty",
                    "prodallocconsumpnegopenqty": "Negative_open_qty",
                    "prodallocconsumpassigqty": "Consumed_assign_qty"
                  }
                }
              }
            }
          }
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "event_time": {
        "*": "[&].event_time"
      },
      "Plant": {
        "*": "[&].Plant"
      },
      "Store": {
        "*": "[&].Store"
      },
      "Location": {
        "*": "[&].Location"
      },
      "Distribution_Channel": {
        "*": "[&].Distribution_Channel"
      },
      "Packing_type": {
        "*": "[&].Packing_type"
      },
      "Product_Division": {
        "*": "[&].Product_Division"
      },
      "Date": {
        "*": "[&].Date"
      },
      "Planned_Capacity_qty": {
        "*": "[&].Planned_Capacity_qty"
      },
      "Consumed_Capacity_qty": {
        "*": "[&].Consumed_Capacity_qty"
      },
      "Negative_open_qty": {
        "*": "[&].Negative_open_qty"
      },
      "Consumed_assign_qty": {
        "*": "[&].Consumed_assign_qty"
      }
    }
  }
]

There might be a way to simplify the second shift spec but I couldn’t figure out how/if to iterate over an object instead of an array. I could get the key names as values but couldn’t figure out how to get them as key names in the output.

Please signup or login to give your own answer.

Click here to cancel reply.