Json Question posted in
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

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

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": ""
      }
    }
  }
]
Tags:

2

Answers


  1. 0

    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"
      }
    }
  }
]
    Login or Signup to reply.
  2. 0

    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": {
      "*": "[]"
    }
  }
]
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search