如何通过JOLT基于数组索引计算seq值并写入JSON

输入JSON #

[
  {
    "id": "1",
    "createDate": "6/10/2016 6:12:33 PM",
    "reviewDate": "7/10/2016 1:00:00 AM",
    "reviewedBy": "Dave Smith",
    "notes": "corrected spelling error"
  },
  {
    "id": "5",
    "createDate": "8/09/2016 3:15:55 PM",
    "reviewDate": "7/10/2016 1:00:00 AM",
    "reviewedBy": "John Wayne",
    "notes": "corrected spelling error"
  },
  {
    "id": "5",
    "createDate": "8/09/2016 3:15:55 PM",
    "reviewDate": "3/12/2016 12:00:00 PM",
    "reviewedBy": "Dave Smith",
    "notes": "finished"
  },
  {
    "id": "6",
    "createDate": "16/10/2017 1:08:33 PM",
    "reviewDate": "17/10/2017 1:00:00 AM",
    "reviewedBy": "Dave Smith",
    "notes": "changed location"
  },
  {
    "id": "6",
    "createDate": "16/10/2017 1:08:33 PM",
    "reviewDate": "13/12/2017 12:00:00 PM",
    "reviewedBy": "John Wayne",
    "notes": "consider additional control measures"
  },
  {
    "id": "6",
    "createDate": "26/10/2017 1:08:33 PM",
    "reviewDate": "13/01/2018 12:00:00 PM",
    "reviewedBy": "Susan Port",
    "notes": "finished"
  }
]

期望输出JSON #

[
  {
    "id": "1",
    "seq": "1",
    "createDate": "6/10/2016 6:12:33 PM",
    "reviewDate": "7/10/2016 1:00:00 AM",
    "reviewedBy": "Dave Smith",
    "notes": "corrected spelling error"
  },
  {
    "id": "5",
    "seq": "1",
    "createDate": "8/09/2016 3:15:55 PM",
    "reviewDate": "7/10/2016 1:00:00 AM",
    "reviewedBy": "John Wayne",
    "notes": "corrected spelling error"
  },
  {
    "id": "5",
    "seq": "2",
    "createDate": "8/09/2016 3:15:55 PM",
    "reviewDate": "3/12/2016 12:00:00 PM",
    "reviewedBy": "Dave Smith",
    "notes": "finished"
  },
  {
    "id": "6",
    "seq": "1",
    "createDate": "16/10/2017 1:08:33 PM",
    "reviewDate": "17/10/2017 1:00:00 AM",
    "reviewedBy": "Dave Smith",
    "notes": "changed location"
  },
  {
    "id": "6",
    "seq": "2",
    "createDate": "16/10/2017 1:08:33 PM",
    "reviewDate": "13/12/2017 12:00:00 PM",
    "reviewedBy": "John Wayne",
    "notes": "consider additional control measures"
  },
  {
    "id": "6",
    "seq": "3",
    "createDate": "26/10/2017 1:08:33 PM",
    "reviewDate": "13/01/2018 12:00:00 PM",
    "reviewedBy": "Susan Port",
    "notes": "finished"
  }
]

已尝试的JOLT规则 #

分组规则

[
  {
    "operation": "shift",
    "spec": {
      "*": "@id[]"
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "@": "[]"
      }
    }
  }
]

生成seq规则（seq从0开始，不符合需求）

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "id": "[&1].id",
        "createDate": "[&1].createDate",
        "reviewDate": "[&1].reviewDate",
        "notes": "[&1].notes",
        "$": "[&1].seq"
      }
    }
  }
]

解决方案 #

使用以下JOLT规则可实现需求，核心流程为：按ID分组 → 计算组内从1开始的seq → 展开数组恢复扁平结构：

[
  // 第一步：按id分组，将相同id的元素归入同一数组
  {
    "operation": "shift",
    "spec": {
      "*": {
        "@": "@(1,id).[]"
      }
    }
  },
  // 第二步：遍历每个分组，为组内元素添加初始seq（索引从0开始）
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "@": "[&2].&1",
          "$": "[&2].&1.seq"
        }
      }
    }
  },
  // 第三步：将seq数值加1并转为字符串，确保从1开始且为字符串格式
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": {
        "*": {
          "seq": "=intSum(@(1,seq),1)",
          "seq": "=toString"
        }
      }
    }
  },
  // 第四步：展开分组后的嵌套数组，恢复为原始扁平结构
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": "[]"
      }
    }
  }
]

规则解释 #

1. 第一步shift：根据每个元素的id字段对原始数组分组，生成以id为键、对应元素数组为值的结构。
2. 第二步shift：遍历每个分组内的元素，保留元素原有字段的同时，将元素在组内的索引（从0开始）赋值给seq字段。
3. 第三步modify-overwrite-beta：通过intSum函数将seq数值加1，再用toString转为字符串，满足从1开始的需求。
4. 第四步shift：将分组后的嵌套数组展开，恢复为原始的扁平数组结构，得到最终结果。

内容的提问来源于stack exchange，提问作者Omar Beltran Rodriguez