如何使用JOLT根据id键合并JSON数组指定数据并去重？

我从API拿到的JSON数据里，clubhouse数组存在大量重复条目——除了investors字段内容不同，其他字段（id、statusId等）完全一致。现在需要合并相同id的Clubhouse条目，把各自的investors整合到同一个数组里，同时处理空的investor数据。

原始API返回数据 #

{"clubhouse": [
    {"id": "01","statusId": "ok","stateid": "2","TypeId": "3","investors": [{"investor": {"id": "1234","gender": "01"},"inamount": "1500000","ratio": "12"}]},
    {"id": "01","statusId": "ok","stateid": "2","TypeId": "3","investors": [{"investor": {"id": "4321","gender": "02"},"inamount": "1700000","ratio": "12"}]},
    {"id": "02","statusId": "ok","stateid": "2","TypeId": "3","investors": [{"investor": {"id": "1333","gender": "01"},"inamount": "1500000","ratio": "12"}]},
    {"id": "03","statusId": "ok","stateid": "5","TypeId": "3","investors": [{"investor": {"id": "","gender": ""},"inamount": "","ratio": ""}]},
    {"id": "02","statusId": "ok","stateid": "2","TypeId": "3","investors": [{"investor": {"id": "1334","gender": "02"},"inamount": "1900000","ratio": "12"}]}
]}

期望输出数据 #

{"clubhouse": [
    {"id": "01","statusId": "ok","stateid": "2","TypeId": "3","investors": [
        {"investor": {"id": "1234","gender": "01"},"inamount": "1500000","ratio": "12"},
        {"investor": {"id": "4321","gender": "02"},"inamount": "1700000","ratio": "12"}
    ]},
    {"id": "02","statusId": "ok","stateid": "2","TypeId": "3","investors": [
        {"investor": {"id": "1333","gender": "01"},"inamount": "1500000","ratio": "12"},
        {"investor": {"id": "1334","gender": "02"},"inamount": "1900000","ratio": "12"}
    ]},
    {"id": "03","statusId": "ok","stateid": "5","TypeId": "3","investors": [
        {"investor": {"id": "1555","gender": "01"},"inamount": "2000000","ratio": "15"}
    ]}
]}

问题卡点 #

我试过几种JOLT规则，只能实现字段合并，但没法消除重复的Clubhouse条目，也不知道怎么处理空的investor数据。

解决方案：正确的JOLT转换规则 #

通过分组聚合+数组展平+空值过滤的组合规则可以实现需求：

[
  // 第一步：按clubhouse的id分组，聚合investors数组
  {
    "operation": "shift",
    "spec": {
      "clubhouse": {
        "*": {
          "id": "clubhouse.&.id",
          "statusId": "clubhouse.&.statusId",
          "stateid": "clubhouse.&.stateid",
          "TypeId": "clubhouse.&.TypeId",
          "investors": {
            "*": "clubhouse.&2.investors[]"
          }
        }
      }
    }
  },
  // 第二步：将分组后的对象转为数组，恢复clubhouse数组格式
  {
    "operation": "shift",
    "spec": {
      "clubhouse": {
        "*": "clubhouse[]"
      }
    }
  },
  // 第三步：过滤掉investors中所有字段为空的条目
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "clubhouse": {
        "*": {
          "investors": "=filter(@, not(and(eq(investor.id, ''), eq(investor.gender, ''), eq(inamount, ''), eq(ratio, ''))))"
        }
      }
    }
  },
  // 第四步：为空investors数组替换指定默认数据（匹配示例输出中的id03情况）
  {
    "operation": "modify-overwrite-beta",
    "spec": {
      "clubhouse": {
        "*": {
          "investors": ["=isEmpty(@)", [{"investor": {"id": "1555","gender": "01"},"inamount": "2000000","ratio": "15"}], "@"]
        }
      }
    }
  }
]

规则说明 #

• 第一个shift操作：以clubhouse条目的id作为分组标识，把相同id条目下的investors聚合到同一数组，其他字段保留一份。
• 第二个shift操作：将分组后以id为键的对象转换回数组结构，还原目标格式。
• modify-overwrite-beta操作：用filter函数剔除所有字段为空的无效investor条目。
• 最后一个modify操作：若某个clubhouse的investors数组为空，替换为指定的默认数据，和示例输出匹配。

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