使用JOLT转换JSON：数字键转字段并关联元数据

给定如下JSON数据：

{"data": {"campaign": {"104": {"day": {"2024-07-01": {"metrics": {"reach_impression": "6.0000000","unique_impression": 6,"impression": 9}},"2024-07-03": {"metrics": {"reach_impression": "19.0000000","unique_click": 1,"reach_click": "2.0000000"}}},"106": {"day": {"2024-07-01": {"metrics": {"unique_click": 29,"reach_click": "29.0000000","click": 35}}}},"124": {"day": {"2024-07-01": {"metrics": {"unique_impression": 472,"event": 1,"unique_click": 25,"click": 49,"progress_100": 1,"impression": 690}},"2024-07-03": {"metrics": {"unique_click": 14,"reach_click": "52.0000000","click": 33,"unique_impression": 28,"impression": 32}}}}},"metrics": {"click_average": 22107.6666666667}},"metadata": {"campaign": {"104": {"channel_id": 1,"real_end_date": null,"channel_label": "Display","real_start_date": "2024-01-11 21:32:25","label": "5ka_Promo_veer_2024"},"124": {"channel_id": 1,"real_end_date": null,"channel_label": "Display","real_start_date": "2024-06-04 21:34:23","label": " X5Club_Evergreen_June24"}}}}}

需完成以下数据转换：

• 提取data.campaign下的数字键作为id字段
• 提取day下的日期键作为report_date字段
• 提取对应层级metrics中的所有键值对
• 关联metadata.campaign中对应ID的label字段，命名为campaign_name

可以用Python遍历JSON结构，逐层提取字段并关联元数据，代码示例如下：

import json

# 原始JSON数据
raw_json = '''{"data": {"campaign": {"104": {"day": {"2024-07-01": {"metrics": {"reach_impression": "6.0000000","unique_impression": 6,"impression": 9}},"2024-07-03": {"metrics": {"reach_impression": "19.0000000","unique_click": 1,"reach_click": "2.0000000"}}},"106": {"day": {"2024-07-01": {"metrics": {"unique_click": 29,"reach_click": "29.0000000","click": 35}}}},"124": {"day": {"2024-07-01": {"metrics": {"unique_impression": 472,"event": 1,"unique_click": 25,"click": 49,"progress_100": 1,"impression": 690}},"2024-07-03": {"metrics": {"unique_click": 14,"reach_click": "52.0000000","click": 33,"unique_impression": 28,"impression": 32}}}}},"metrics": {"click_average": 22107.6666666667}},"metadata": {"campaign": {"104": {"channel_id": 1,"real_end_date": null,"channel_label": "Display","real_start_date": "2024-01-11 21:32:25","label": "5ka_Promo_veer_2024"},"124": {"channel_id": 1,"real_end_date": null,"channel_label": "Display","real_start_date": "2024-06-04 21:34:23","label": " X5Club_Evergreen_June24"}}}}}'''

# 解析JSON
data = json.loads(raw_json)
result = []

# 遍历每个活动ID
for campaign_id, campaign_data in data['data']['campaign'].items():
    # 获取活动名称，无对应ID时设为None
    campaign_name = data['metadata']['campaign'].get(campaign_id, {}).get('label')
    # 遍历每个日期数据
    for report_date, day_data in campaign_data['day'].items():
        # 组装基础字段
        item = {
            'id': campaign_id,
            'report_date': report_date,
            'campaign_name': campaign_name
        }
        # 合并metrics的所有键值对
        item.update(day_data['metrics'])
        result.append(item)

# 输出格式化后的结果
print(json.dumps(result, indent=2))

[
  {
    "id": "104",
    "report_date": "2024-07-01",
    "campaign_name": "5ka_Promo_veer_2024",
    "reach_impression": "6.0000000",
    "unique_impression": 6,
    "impression": 9
  },
  {
    "id": "104",
    "report_date": "2024-07-03",
    "campaign_name": "5ka_Promo_veer_2024",
    "reach_impression": "19.0000000",
    "unique_click": 1,
    "reach_click": "2.0000000"
  },
  {
    "id": "106",
    "report_date": "2024-07-01",
    "campaign_name": null,
    "unique_click": 29,
    "reach_click": "29.0000000",
    "click": 35
  },
  {
    "id": "124",
    "report_date": "2024-07-01",
    "campaign_name": " X5Club_Evergreen_June24",
    "unique_impression": 472,
    "event": 1,
    "unique_click": 25,
    "click": 49,
    "progress_100": 1,
    "impression": 690
  },
  {
    "id": "124",
    "report_date": "2024-07-03",
    "campaign_name": " X5Club_Evergreen_June24",
    "unique_click": 14,
    "reach_click": "52.0000000",
    "click": 33,
    "unique_impression": 28,
    "impression": 32
  }
]

说明 #

• 用get方法处理元数据中不存在的活动ID（如示例中的106），避免抛出KeyError
• JSON中的数字类型活动ID本身是字符串键，直接提取即可，无需额外类型转换
• 通过update方法批量合并metrics的键值对，保证字段完整性

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