Input JSON
[
{
"collectionname": "Property name",
"idgroup_uuid": "7750ceae-0528-44cb-9aeb-8865602d7f6f",
"iduser_uuid": "5f9c09ea-6332-7541-9632-4a9a0ee768f1"
},
[
{
"zone_name": "01 green",
"zone_type_name": "Green",
"conditions": {
"timestamp": "2024-06-11 06:35:41",
"num_samples": 12,
"img_extent": {
"min_lng": -5.877219,
"max_lng": -5.876672,
"min_lat": 54.220024,
"max_lat": 54.220338
},
"moisture_avg": 29.01,
"moisture_img": "https://api.pogoturfpro.com/api/partner/maya/insight/5387721?attribute=M&key=05127be738e28884c2eba2585968eb687aa0cd20dadc299f5282ff6bf82f5e5b",
"moisture_avg_color": "#4EC3DA",
"moisture_avg_rating": "High",
"ec_avg": 0.21,
"ec_img": "https://api.pogoturfpro.com/api/partner/maya/insight/5387721?attribute=E&key=05127be738e28884c2eba2585968eb687aa0cd20dadc299f5282ff6bf82f5e5b",
"ec_avg_color": "#2D882D",
"ec_avg_rating": "Optimal",
"salinity_index_avg": 0.73,
"salinity_index_img": "https://api.pogoturfpro.com/api/partner/maya/insight/5387721?attribute=S&key=05127be738e28884c2eba2585968eb687aa0cd20dadc299f5282ff6bf82f5e5b",
"salinity_index_avg_color": "#AF4440",
"salinity_index_avg_rating": "Critically Low",
"surface_heat_f_avg": 49.21,
"surface_heat_f_img": "https://api.pogoturfpro.com/api/partner/maya/insight/5387721?attribute=T&key=05127be738e28884c2eba2585968eb687aa0cd20dadc299f5282ff6bf82f5e5b",
"surface_heat_f_avg_color": "#AF4440",
"surface_heat_f_avg_rating": "Critically Low",
"soil_temp_f_avg": 48.98,
"soil_temp_f_img": "https://api.pogoturfpro.com/api/partner/maya/insight/5387721?attribute=ST&key=05127be738e28884c2eba2585968eb687aa0cd20dadc299f5282ff6bf82f5e5b",
"soil_temp_f_avg_color": "#AF4440",
"soil_temp_f_avg_rating": "Critically Low",
"notes": []
},
"pins": []
},
{
"zone_name": "02 Green",
"zone_type_name": "Green",
"conditions": {
"timestamp": "2024-06-11 06:58:30",
"num_samples": 12,
"img_extent": {
"min_lng": -5.87333,
"max_lng": -5.872946,
"min_lat": 54.222294,
"max_lat": 54.222509
},
"moisture_avg": 29.3,
"moisture_img": "https://api.pogoturfpro.com/api/partner/maya/insight/5387712?attribute=M&key=05127be738e28884c2eba2585968eb687aa0cd20dadc299f5282ff6bf82f5e5b",
"moisture_avg_color": "#4EC3DA",
"moisture_avg_rating": "High",
"ec_avg": 0.21,
"ec_img": "https://api.pogoturfpro.com/api/partner/maya/insight/5387712?attribute=E&key=05127be738e28884c2eba2585968eb687aa0cd20dadc299f5282ff6bf82f5e5b",
"ec_avg_color": "#2D882D",
"ec_avg_rating": "Optimal",
"salinity_index_avg": 0.73,
"salinity_index_img": "https://api.pogoturfpro.com/api/partner/maya/insight/5387712?attribute=S&key=05127be738e28884c2eba2585968eb687aa0cd20dadc299f5282ff6bf82f5e5b",
"salinity_index_avg_color": "#AF4440",
"salinity_index_avg_rating": "Critically Low",
"surface_heat_f_avg": 49.63,
"surface_heat_f_img": "https://api.pogoturfpro.com/api/partner/maya/insight/5387712?attribute=T&key=05127be738e28884c2eba2585968eb687aa0cd20dadc299f5282ff6bf82f5e5b",
"surface_heat_f_avg_color": "#AF4440",
"surface_heat_f_avg_rating": "Critically Low",
"soil_temp_f_avg": 49.55,
"soil_temp_f_img": "https://api.pogoturfpro.com/api/partner/maya/insight/5387712?attribute=ST&key=05127be738e28884c2eba2585968eb687aa0cd20dadc299f5282ff6bf82f5e5b",
"soil_temp_f_avg_color": "#AF4440",
"soil_temp_f_avg_rating": "Critically Low",
"notes": []
},
"pins": []
}
]
]
Expected Output JSON
[
{
"Time": "2024-06-11 06:35:41",
"VMC": 29.01,
"EC": 0.21,
"Temp_Soil_F": 48.98,
"Temp_Soil": 9.433,
"Latitude": 54.220181,
"Longitude": -5.876945,
"idgroup": "group(7750ceae-0528-44cb-9aeb-8865602d7f6f)",
"iduser": "user(5f9c09ea-6332-7541-9632-4a9a0ee768f1)",
"Rod_Length": "S",
"Soil_type": "D",
"VWC_Mode": "V"
},
{
"Time": "2024-06-11 06:58:30",
"VMC": 29.3,
"EC": 0.21,
"Temp_Soil_F": 49.55,
"Temp_Soil": 9.75,
"Latitude": 54.222401,
"Longitude": -5.873138,
"idgroup": "group(7750ceae-0528-44cb-9aeb-8865602d7f6f)",
"iduser": "user(5f9c09ea-6332-7541-9632-4a9a0ee768f1)",
"Rod_Length": "S",
"Soil_type": "D",
"VWC_Mode": "V"
}
]
Specifications:
Rod_Length, Soil_type & VWC_Mode are hardcoded values
idgroup is idgroup_uuid
iduser is iduser_uuid
Time is timestamp
VWC is moisture_avg
EC is ec_avg
Temp_Soil_F is soil_temp_f_avg
Temp_Soil is celcius(soil_temp_f_avg) - convert soil_temp_f_avg to celsius
Latitude is (min_lat+ max_lat) / 2
Longitude is (min_lng + max_lng)/2
Hi this spec will help you resolve your query.
Updated the spec :
Also added logic to convert Fahrenheit to Celsius.
[
{
"operation": "shift",
"spec": {
"*": {
"*": {
"@(1,idgroup_uuid)": "[#2].idgroup",
"@(1,iduser_uuid)": "[#2].iduser",
"conditions": {
"#S": "[#3].Rod_Length",
"#D": "[#3].Soil_type",
"#V": "[#3].VWC_Mode",
"timestamp": "[#3].Time",
"moisture_avg": "[#3].VMC",
"ec_avg": "[#3].EC",
"soil_temp_f_avg": "[#3].Temp_Soil_F",
"img_extent": {
"min_lng": "[#4].min_lng",
"max_lng": "[#4].max_lng",
"max_lat": "[#4].max_lat",
"min_lat": "[#4].min_lat"
}
}
}
}
}
}, {
"operation": "modify-overwrite-beta",
"spec": {
"*": {
"Latitude": "=avg(@(1,min_lat),@(1,max_lat))",
"Longitude": "=avg(@(1,min_lng),@(1,max_lng))",
"Temp_Soil": "=doubleSum(@(1,Temp_Soil_F),-32)",
"idgroup": "=concat(group,'(',@(1,idgroup),))",
"iduser": "=concat(user,'(',@(1,iduser),))"
}
}
},
{
"operation": "modify-overwrite-beta",
"spec": {
"*": {
"Temp_Soil_F": "=divide(@(1,Temp_Soil_F),1.8)"
}
}
}, {
"operation": "remove",
"spec": {
"*": {
"min_lng": "",
"max_lng": "",
"max_lat": "",
"min_lat": ""
}
}
},
{
"operation": "shift",
"spec": {
"*": {
"Rod_Length": {
"*": {
"@(2,Time)": "[#4].Time",
"@(2,VMC)": "[#4].VMC",
"@(2,Temp_Soil_F)": "[#4].Temp_Soil_F",
"@(2,Temp_Soil)": "[#4].Temp_Soil",
"@(2,Latitude)": "[#4].Latitude",
"@(2,Longitude)": "[#4].Longitude",
"@(2,idgroup)": "[#4].idgroup",
"@(2,iduser)": "[#4].iduser",
"@(2,Rod_Length)": "[#4].Rod_Length",
"@(2,Soil_type)": "[#4].Soil_type",
"@(2,VWC_Mode)": "[#4].VWC_Mode",
"@(2,EC)": "[#4].EC"
}
}
}
}
}
]
Explanation:
First shift spec will do the transformation to the array required format. "@(1,idgroup_uuid)": "[#2].idgroup" // @--> will pick value from 1 level up and place it among the array.
Everything inside "conditions" --> will be placed in an array object. "#S": "[#3].Rod_Length" --> # in shift operation on LHS means adding default value.
Second "modify-overwrite-beta" --> will do average for longitude and latitude.
Third Spec "remove" will remove nonrequired fields.
Note : Temp_Soil_F convertion to celcius wont work with Jolt we need to add a logic for it in Java.
I hope this helps you in getting the required output.