Grouping Json with same value of a key and rename keys with Jolt

Casper
April 16, 2024
82 views
1 vote
2 Answers

This is a continuation of question asked in Grouping Json with same value of a key with Jolt

I want to do following changes in output JSON with JOLT. Could you please help on getting the desired JSON output. Thank you.

Add "StartTime" for each object in output json
Rename istance_3 and distance_4 into X_Z_distance and Y_Z_distance
Round Temp_1 value into 2 decimal points

Input JSON:

[
  [
    {
      "Name": "03.04.2023",
      "StartTime": "2023-04-03 06:32",
      "SessionData": [
        {
          "LocationX": "36.282466",
          "LocationY": "-5.298164",
          "DataValue": "0.36",
          "DataType": "distance_1"
        },
        {
          "LocationX": "36.282466",
          "LocationY": "-5.298164",
          "DataValue": "11.0",
          "DataType": "distance_2"
        },
        {
          "LocationX": "36.282466",
          "LocationY": "-5.298164",
          "DataValue": "2231",
          "DataType": "distance_3"
        },
        {
          "LocationX": "36.282466",
          "LocationY": "-5.298164",
          "DataValue": "0.04",
          "DataType": "distance_4"
        },
        {
          "LocationX": "36.282466",
          "LocationY": "-5.298164",
          "DataValue": "58.82",
          "DataType": "distance_5"
        },
        {
          "LocationX": "36.278355",
          "LocationY": "-5.290660",
          "DataValue": "0.00",
          "DataType": "distance_1"
        },
        {
          "LocationX": "36.278355",
          "LocationY": "-5.290660",
          "DataValue": "9.8",
          "DataType": "distance_2"
        },
        {
          "LocationX": "36.278355",
          "LocationY": "-5.290660",
          "DataValue": "2206",
          "DataType": "distance_3"
        },
        {
          "LocationX": "36.278355",
          "LocationY": "-5.290660",
          "DataValue": "0.00",
          "DataType": "distance_4"
        },
        {
          "LocationX": "36.278355",
          "LocationY": "-5.290660",
          "DataValue": "58.28",
          "DataType": "distance_5"
        }
      ]
    }
  ]
]

Expected Output JSON:


[
  {
    "StartTime": "2023-04-03 06:32",
    "distance_1": "0.36",
    "LocationX": "36.282466",
    "LocationY": "-5.298164",
    "distance_2": "11.0",
    "X_Z_distance": "2231",
    "Y_Z_distance": "0.04",
    "Temp_1": 2189,
    "Distance_Type": "D",
    "Distance_Length": "M"
  },
  {
    "StartTime": "2023-04-03 06:32",
    "distance_1": "0.00",
    "LocationX": "36.278355",
    "LocationY": "-5.290660",
    "distance_2": "9.8",
    "X_Z_distance": "2206",
    "Y_Z_distance": "0.00",
    "Temp_1": 2164,
    "Distance_Type": "D",
    "Distance_Length": "M"
  }
]

JOLT:

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "*": {
            "*": {
              "Location*": "@(1,LocationX).@(1,LocationY).&",
              "@DataValue": "@(1,LocationX).@(1,LocationY).@DataType"
            }
          }
        }
      }
    }
  },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": {
        "*": {
          "Temp_1": "=doubleSum(@(1,distance_3),-42)"
        }
      }
    }
  },
  {
    "operation": "default",
    "spec": {
      "*": {
        "*": {
          "Distance_Length": "M",
          "Distance_Type": "D"
        }
      }
    }
  },
  {
    "operation": "cardinality",
    "spec": {
      "*": {
        "*": {
          "*": "ONE"
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": "[]"
      }
    }
  },
  {
    "operation": "remove",
    "spec": {
      "*": {
        "distance_5": ""
      }
    }
  }
]

Answers

Your initial JOLT specification didn’t properly reference the StartTime field at the correct level. You were attempting to access it at level *, but it should have been at level "0". did not include renaming distance_3 and distance_4 to X_Z_distance and Y_Z_distance respectively.

Incorrect Modification of Temp_1 and your JOLT spec lacked setting default values for Distance-length and distance-type.

also I removed the distance_5, your JOLT spec didn’t include the removal of the distance_5 key as specified in the desired output.

here is the edited JOLT;

    [
  {
    "operation": "shift",
    "spec": {
      "*": {
        "0": {
          "StartTime": "[&1].StartTime",
          "SessionData": {
            "*": {
              "LocationX": "[&2].LocationX",
              "LocationY": "[&2].LocationY",
              "DataValue": {
                "distance_1": "[&3].distance_1",
                "distance_2": "[&3].distance_2",
                "distance_3": "[&3].X_Z_distance",
                "distance_4": "[&3].Y_Z_distance",
                "*": {
                  "@": "[&3].&"
                }
              }
            }
          }
        }
      }
    }
  },
  {
    "operation": "modify-default-beta",
    "spec": {
      "*": {
        "X_Z_distance": "=toInteger",
        "Y_Z_distance": "=toInteger",
        "distance_3": "=toDecimal",
        "Temp_1": "=toInteger(@(1,distance_3) * 100)"
      }
    }
  },
  {
    "operation": "default",
    "spec": {
      "*": {
        "Distance_Length": "M",
        "Distance_Type": "D"
      }
    }
  }
]

You can go on using the previous grouping technique as in the following transformation :

[
  { // you can pull all the expected attributes as some of them are arrays
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "*": {
            "*": {
              "@2,StartTime": "@1,LocationX.@1,LocationY.StartTime",
              "Location*": "@1,LocationX.@1,LocationY.&",
              "@DataValue": "@1,LocationX.@1,LocationY.@DataType", // match the values of the attributes as a whole
              "#D": "@1,LocationX.@1,LocationY.Distance_Type",
              "#M": "@1,LocationX.@1,LocationY.Distance_Length"
            }
          }
        }
      }
    }
  },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": {
        "*": {
          "distance_5?": null, // prepare this attribute to be removed
          "Temp_1": "=intSum(@(1,distance_3),-42)" // expected result attribute is an integer
        }
      }
    }
  },
  { // rename distance_3 and _4 
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "*": "&2_&1.&",
          "distance_3": "&2_&1.X_Z_distance",
          "distance_4": "&2_&1.Y_Z_distance",
          "distance_5": { "": "" } // reove this attribute
        }
      }
    }
  },
  { // pick only the first elements from the arrays
    "operation": "cardinality",
    "spec": {
      "*": {
        "*": "ONE"
      }
    }
  },
  { // derive an array of objects without a key
    "operation": "shift",
    "spec": {
      "*": "[]"
    }
  }
]

Please signup or login to give your own answer.

Click here to cancel reply.