arraysjsonjolt
Grouping Json with same value of a key with Jolt
I have hard time to understand how to group JSON array using Jolt. I want to group by "LocationX" and "LocationY" into individual objects in an Array.
Input JSON:
[
[
{
"LocationX": "36.28",
"LocationY": "-5.29",
"DataValue": "0.36",
"DataType": "distance_1"
},
{
"LocationX": "36.28",
"LocationY": "-5.29",
"DataValue": "11.0",
"DataType": "distance_2"
},
{
"LocationX": "36.28",
"LocationY": "-5.29",
"DataValue": "10.0",
"DataType": "distance_3"
},
{
"LocationX": "36.28",
"LocationY": "-5.29",
"DataValue": "0.04",
"DataType": "distance_4"
},
{
"LocationX": "36.28",
"LocationY": "-5.29",
"DataValue": "58.82",
"DataType": "distance_5"
},
{
"LocationX": "36.34",
"LocationY": "-5.29",
"DataValue": "0.00",
"DataType": "distance_1"
},
{
"LocationX": "36.34",
"LocationY": "-5.29",
"DataValue": "9.8",
"DataType": "distance_2"
},
{
"LocationX": "36.34",
"LocationY": "-5.29",
"DataValue": "2206",
"DataType": "distance_3"
},
{
"LocationX": "36.34",
"LocationY": "-5.29",
"DataValue": "0.00",
"DataType": "distance_4"
},
{
"LocationX": "36.34",
"LocationY": "-5.29",
"DataValue": "58.28",
"DataType": "distance_5"
}
]
]
I would like to group by "LocationX" and "LocationY" into individual objects in an Array.
Expected JSON output:
[
{
"LocationX": 36.28,
"LocationY": -5.29,
"distance_1": 0.36,
"distance_2": 11.0,
"distance_3": 10.0,
"distance_4": 0.04,
"distance_5": 58.82,
"Rod":"S" //this is default value for all objects
},
{
"LocationX": 36.34,
"LocationY": -5.25,
"distance_1": 0.00,
"distance_2": 9.8,
"distance_3": 2206,
"distance_4": 0.00,
"distance_5": 58.28,
"Rod":"S"
}
]
Could you please help on getting the desired JSON output. Thank you.
16/04/2024
I have new improvements that need to add for the above input json as follows.
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"
}
]
Based on the answer I got, I developed following JOLT.
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": ""
}
}
}
]
I want add "StartTime" for each object, and distance_3 and distance_4 into X_Z_distance and Y_Z_distance.
Could you please help on getting the desired JSON output. Thank you
Solution
You can partition the objects by LocationX/Y attributes in order to get binary result such as
[
{
"operation": "shift",
"spec": {
"*": {
"*": {
"Location*": "@(1,LocationX).@(1,LocationY).&",
"@DataValue": "@(1,LocationX).@(1,LocationY).@DataType",
"#S": "@(1,LocationX).@(1,LocationY).Rod" // hardcode desired fixed
// value by using # operator
}
}
}
},
{ // pick only one occurrence per each repeating Location arrays
"operation": "cardinality",
"spec": {
"*": {
"*": {
"*": "ONE"
}
}
}
},
{ // get rid of the object keys while converting whole JSON
// to array of objects
"operation": "shift",
"spec": {
"*": {
"*": "[]"
}
}
}
]
the demo on the site https://jolt-demo.appspot.com/ is :
