能否通过JOLT实现指定JSON数据的拆分与清洗转换？

输入数据 #

[
  {
    "MasterUrl": "https://$$$$/exodus2022/player.aspx",
    "Programme": [
      {
        "Retirement": "2022",
        "Current Team": "Bulls",
        "Previous Team 1": "NA",
        "Previous Team 2": "NA",
        "Previous Team 3": "NA"
      },
      {
        "Retirement": "2020",
        "Current Team": "Celtics",
        "Previous Team 1": "NA",
        "Previous Team 2": "NA",
        "Previous Team 3": "NA"
      }
    ]
  },
  {
    "MasterUrl": "https://$$$$/risen2022/player.aspx",
    "Programme": [
      {
        "Current Team": "Heat",
        "Current Location": "Miami",
        "Previous Team 1": "NA",
        "Previous Team 2": "NA",
        "Previous Team 3": "NA"
      },
      {
        "Current Team": "Cavalliers",
        "Current Location": "Texas",
        "Previous Team 1": "NA",
        "Previous Team 2": "NA",
        "Previous Team 3": "NA"
      },
      {
        "Current Team": "Pistons",
        "Current Location": "New York",
        "Previous Team 1": "NA",
        "Previous Team 2": "NA",
        "Previous Team 3": "NA"
      }
    ]
  }
]

转换需求 #

• 按MasterUrl中的exodus2022和risen2022标识拆分数据，分别生成对应首字母大写的键名数组
• 移除MasterUrl中的player.aspx后缀
• 删除Programme数组内所有Previous Team相关字段
• 将Risen2022下第一个Programme项的Current Team值转为大写

JOLT转换规范 #

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "MasterUrl": {
          "https://$$$$/exodus2022/player.aspx": {
            "#https://$$$$/exodus2022": "[0].Exodus2022[0].MasterUrl",
            "@2,Programme": "[0].Exodus2022[0].Programme"
          },
          "https://$$$$/risen2022/player.aspx": {
            "#https://$$$$/risen2022": "[1].Risen2022[0].MasterUrl",
            "@2,Programme": "[1].Risen2022[0].Programme"
          }
        }
      }
    }
  },
  {
    "operation": "remove",
    "spec": {
      "*": {
        "*": {
          "*": {
            "Programme": {
              "*": {
                "Previous Team *": ""
              }
            }
          }
        }
      }
    }
  },
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "1": {
        "Risen2022": {
          "0": {
            "Programme": {
              "0": {
                "Current Team": "=toUpper"
              }
            }
          }
        }
      }
    }
  }
]

转换说明 #

1. Shift操作：匹配不同的MasterUrl，将数据分配到对应的Exodus2022和Risen2022数组中，同时替换MasterUrl为截断后的地址
2. Remove操作：递归删除所有Programme项中的Previous Team相关字段
3. Modify操作：将Risen2022下第一个Programme项的Current Team值转为大写

转换结果 #

[
  {
    "Exodus2022": [
      {
        "MasterUrl": "https://$$$$/exodus2022",
        "Programme": [
          {
            "Retirement": "2022",
            "Current Team": "Bulls"
          },
          {
            "Retirement": "2020",
            "Current Team": "Celtics"
          }
        ]
      }
    ]
  },
  {
    "Risen2022": [
      {
        "MasterUrl": "https://$$$$/risen2022",
        "Programme": [
          {
            "Current Team": "HEAT",
            "Current Location": "Miami"
          },
          {
            "Current Team": "Cavalliers",
            "Current Location": "Texas"
          },
          {
            "Current Team": "Pistons",
            "Current Location": "New York"
          }
        ]
      }
    ]
  }
]

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