基于JOLT格式实现指定JSON数据转换的技术需求

核心转换规则回顾 #

• 硬编码固定值：Rod_Length、Soil_type、VWC_Mode
• ID格式转换：idgroup → group(原idgroup_uuid值)；iduser → user(原iduser_uuid值)
• 字段直接映射：
  • Time ← timestamp
  • VMC ← moisture_avg
  • EC ← ec_avg
  • Temp_Soil_F ← soil_temp_f_avg
• 计算生成字段：
  • Temp_Soil：华氏温度转摄氏度，公式：(soil_temp_f_avg - 32) * 5 / 9
  • Latitude：(min_lat + max_lat) / 2
  • Longitude：(min_lng + max_lng) / 2

JOLT转换规范（Spec） #

[
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "idgroup": "=concat('group(', @(1,idgroup_uuid), ')')",
      "iduser": "=concat('user(', @(1,iduser_uuid), ')')",
      "Temp_Soil": "=divide(=multiply(=minus(@(1,soil_temp_f_avg), 32)), 9, 5)",
      "Latitude": "=divide(=plus(@(1,min_lat), @(1,max_lat)), 2)",
      "Longitude": "=divide(=plus(@(1,min_lng), @(1,max_lng)), 2)",
      // 硬编码固定值，替换为实际业务需要的数值/字符串
      "Rod_Length": 10,
      "Soil_type": "Loam",
      "VWC_Mode": "Calibrated"
    }
  },
  {
    "operation": "shift",
    "spec": {
      "idgroup": "idgroup",
      "iduser": "iduser",
      "timestamp": "Time",
      "moisture_avg": "VMC",
      "ec_avg": "EC",
      "soil_temp_f_avg": "Temp_Soil_F",
      "Temp_Soil": "Temp_Soil",
      "Latitude": "Latitude",
      "Longitude": "Longitude",
      "Rod_Length": "Rod_Length",
      "Soil_type": "Soil_type",
      "VWC_Mode": "VWC_Mode"
    }
  }
]

输入输出示例 #

输入JSON

{
  "idgroup_uuid": "abc123-def456-ghi789",
  "iduser_uuid": "jkl012-mno345-pqr678",
  "timestamp": "2024-05-20T14:30:00Z",
  "moisture_avg": 25.6,
  "ec_avg": 1.2,
  "soil_temp_f_avg": 77,
  "min_lat": 40.7128,
  "max_lat": 40.7138,
  "min_lng": -74.0060,
  "max_lng": -74.0050
}

输出JSON

{
  "idgroup": "group(abc123-def456-ghi789)",
  "iduser": "user(jkl012-mno345-pqr678)",
  "Time": "2024-05-20T14:30:00Z",
  "VMC": 25.6,
  "EC": 1.2,
  "Temp_Soil_F": 77,
  "Temp_Soil": 25,
  "Latitude": 40.7133,
  "Longitude": -74.0055,
  "Rod_Length": 10,
  "Soil_type": "Loam",
  "VWC_Mode": "Calibrated"
}

规则对应实现说明 #

1. 硬编码字段：在modify-overwrite-beta操作中直接赋值固定值，可根据业务需求修改具体内容
2. ID格式转换：使用=concat函数拼接前缀与原UUID值
3. 温度转换：通过嵌套数学函数实现华氏转摄氏的公式计算
4. 经纬度计算：用=plus求和后再=divide取平均值
5. 字段映射：最后通过shift操作将处理后的字段映射到目标输出结构中

内容的提问来源于stack exchange，提问作者BHADRAKA HERATH