使用JOLT处理JSON：替换机器ID为名称并完成指定字段映射

我有如下JSON输入数据，需要完成以下处理：

1. 将machine字段的ID替换为对应的机器名称
2. 按照指定字段映射生成目标输出JSON：
   • distance 对应 "code": "X2-P1"
   • temp 对应 "code": "X1-P1"
   • weight 对应 "code": "weight"
3. 支持将指定code对应的值转换为字符串
4. 过滤规则：
   • 无匹配读数记录的机器，不纳入输出
   • 无匹配机器信息的读数记录，不纳入输出

输入JSON：

[
  [
    {
      "id": 26359,
      "name": "station green"
    },
    {
      "id": 22321,
      "name": "ravenstein"
    },
    {
      "id": 45328,
      "name": "workstation syn"
    }
  ],
  [
    {
      "time": "2024-06-16T21:30:40.289705Z",
      "machine": 22321,
      "temp": 18,
      "distance": 0.113,
      "long": 0.7866,
      "weight": 0.2563
    },
    {
      "time": "2024-06-16T19:02:16.970647Z",
      "machine": 26359,
      "temp": 27,
      "distance": 0.453,
      "site": 2219,
      "weight": 7.0261
    },
    {
      "time": "2024-06-17T19:32:13.180647Z",
      "machine": 26359,
      "temp": 56,
      "distance": 6.453,
      "site": 2219,
      "weight": 3.5463
    },
    {
      "time": "2024-32-16T19:02:16.970647Z",
      "machine": 78124,
      "temp": 22,
      "distance": 3.465,
      "site": 8723,
      "weight": 4.4312
    }
  ]
]

目标输出JSON：

[
  {
    "stationname": "ravenstein",
    "Date": "2024-06-16T21:30:40",
    "data": [
      {
        "key": "X2-P1",
        "value": 0.113
      },
      {
        "key": "X1-P1",
        "value": 18
      },
      {
        "key": "weight",
        "value": 0.2563
      }
    ]
  },
  {
    "stationname": "station green",
    "Date": "2024-06-16T19:02:16",
    "data": [
      {
        "key": "X2-P1",
        "value": 0.453
      },
      {
        "key": "X1-P1",
        "value": 27
      },
      {
        "key": "weight",
        "value": 7.0261
      }
    ]
  },
  {
    "stationname": "station green",
    "Date": "2024-06-17T19:32:13",
    "data": [
      {
        "key": "X2-P1",
        "value": 6.453
      },
      {
        "key": "X1-P1",
        "value": 56
      },
      {
        "key": "weight",
        "value": 3.5463
      }
    ]
  }
]

用Python可以快速实现这个转换逻辑，以下是完整代码：

import json

def transform_json(input_data):
    # 构建机器ID到名称的映射字典，方便快速查找
    machine_map = {machine['id']: machine['name'] for machine in input_data[0]}
    # 定义字段与目标code的映射关系
    field_code_map = {
        'distance': 'X2-P1',
        'temp': 'X1-P1',
        'weight': 'weight'
    }
    result = []
    
    for record in input_data[1]:
        machine_id = record.get('machine')
        # 跳过无匹配机器的记录
        if machine_id not in machine_map:
            continue
        # 处理时间格式，截断到秒
        time_str = record['time'].split('.')[0]
        # 生成data数组
        data = []
        for field, code in field_code_map.items():
            value = record.get(field)
            if value is not None:
                # 若需要将值转为字符串，取消下面一行的注释
                # value = str(value)
                data.append({'key': code, 'value': value})
        # 将处理后的记录加入结果列表
        result.append({
            'stationname': machine_map[machine_id],
            'Date': time_str,
            'data': data
        })
    return result

# 加载输入JSON
input_json = '''[
  [
    {"id":26359,"name":"station green"},
    {"id":22321,"name":"ravenstein"},
    {"id":45328,"name":"workstation syn"}
  ],
  [
    {"time":"2024-06-16T21:30:40.289705Z","machine":22321,"temp":18,"distance":0.113,"long":0.7866,"weight":0.2563},
    {"time":"2024-06-16T19:02:16.970647Z","machine":26359,"temp":27,"distance":0.453,"site":2219,"weight":7.0261},
    {"time":"2024-06-17T19:32:13.180647Z","machine":26359,"temp":56,"distance":6.453,"site":2219,"weight":3.5463},
    {"time":"2024-32-16T19:02:16.970647Z","machine":78124,"temp":22,"distance":3.465,"site":8723,"weight":4.4312}
  ]
]'''

# 执行转换并输出结果
input_data = json.loads(input_json)
output_data = transform_json(input_data)
print(json.dumps(output_data, indent=2))

代码说明 #

1. 机器映射字典：通过字典推导式快速建立ID与名称的对应关系，提升查找效率
2. 时间处理：通过分割字符串将时间截断到秒，完全匹配目标格式
3. 字段转换：遍历指定的字段映射，自动跳过不存在的字段，保证鲁棒性
4. 过滤逻辑：直接跳过无匹配机器的记录，符合需求中的过滤规则
5. 字符串转换：如果需要将value转为字符串，只需取消代码中对应的注释即可

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