使用JOLT实现数组值关联映射，完成特定JSON格式转换

源JSON示例 #

{
  "query": {
    "ids": ["12345"],
    "dimensions": ["ym:s:date", "ym:s:region"],
    "metrics": ["ym:s:goal123Visits", "ym:s:goal456Visits"]
  },
  "data": [
    {
      "dimensions": ["2024-05-01", "北京"],
      "metrics": [100, 50]
    },
    {
      "dimensions": ["2024-05-02", "上海"],
      "metrics": [150, 75]
    }
  ]
}

目标JSON示例 #

[
  {
    "counter_id": "12345",
    "date_of_visit": "2024-05-01",
    "region": "北京",
    "goal_id": "123",
    "conversions": 100
  },
  {
    "counter_id": "12345",
    "date_of_visit": "2024-05-01",
    "region": "北京",
    "goal_id": "456",
    "conversions": 50
  },
  {
    "counter_id": "12345",
    "date_of_visit": "2024-05-02",
    "region": "上海",
    "goal_id": "123",
    "conversions": 150
  },
  {
    "counter_id": "12345",
    "date_of_visit": "2024-05-02",
    "region": "上海",
    "goal_id": "456",
    "conversions": 75
  }
]

正确JOLT转换配置 #

[
  // 绑定全局配置与每个数据条目
  {
    "operation": "shift",
    "spec": {
      "query": {
        "ids": {
          "*": "counter_id"
        },
        "dimensions": "dim_mapping",
        "metrics": "metric_mapping"
      },
      "data": {
        "*": {
          "@(2,counter_id)": "[#2].counter_id",
          "@(2,dim_mapping)": "[#2].dim_mapping",
          "@(2,metric_mapping)": "[#2].metric_mapping",
          "dimensions": "[#2].data_dims",
          "metrics": "[#2].data_metrics"
        }
      }
    }
  },
  // 定义维度重命名规则
  {
    "operation": "shift",
    "spec": {
      "*": {
        "counter_id": "[&1].counter_id",
        "dim_mapping": {
          "0": {
            "#date_of_visit": "[&3].dim_keys[0]"
          },
          "1": {
            "#region": "[&3].dim_keys[1]"
          }
        },
        "data_dims": {
          "*": "[&3].dim_vals[&1]"
        },
        "metric_mapping": "[&1].metric_mapping",
        "data_metrics": "[&1].data_metrics"
      }
    }
  },
  // 合并维度键值对为顶层字段
  {
    "operation": "shift",
    "spec": {
      "*": {
        "counter_id": "[&1].counter_id",
        "dim_keys": {
          "*": {
            "@": "[&3].@(0)"
          }
        },
        "dim_vals": {
          "*": {
            "@": "[&3].@(2,dim_keys[&1])"
          }
        },
        "metric_mapping": "[&1].metric_mapping",
        "data_metrics": "[&1].data_metrics"
      }
    }
  },
  // 拆分指标并提取goal_id
  {
    "operation": "shift",
    "spec": {
      "*": {
        "metric_mapping": {
          "*": {
            "^ym:s:goal(\\d+)Visits$": "[#3].goal_id",
            "@(2,counter_id)": "[#3].counter_id",
            "@(2,date_of_visit)": "[#3].date_of_visit",
            "@(2,region)": "[#3].region",
            "@(2,data_metrics[&1])": "[#3].conversions"
          }
        }
      }
    }
  },
  // 清理临时字段
  {
    "operation": "remove",
    "spec": {
      "*": {
        "dim_keys": "",
        "dim_vals": "",
        "metric_mapping": "",
        "data_metrics": ""
      }
    }
  }
]

配置说明 #

• 第一步：将query中的全局配置（counter_id、维度映射、指标映射）与每个data条目绑定，确保每条数据都能获取全局参数。
• 第二步：按顺序定义维度的重命名规则，同时将数据维度值存入对应列表。
• 第三步：把维度的重命名字段和对应数据值合并，生成顶层的维度字段。
• 第四步：遍历每个指标，通过正则提取goal_id，将对应指标值设为conversions，每个指标生成独立数据条目。
• 第五步：移除转换过程中产生的临时字段，得到最终干净的结构。

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