如何在PostgreSQL中合并相同id下的denominations数组？

输入数据 #

[
  { id:1, details: { denominations : [ { code: "USD",value :100} ] } },
  { id:1, details: { denominations : [ { code: "USD",value :110} ] } },
  { id:2, details: { denominations : [ { code: "USD",value :100} ] } },
  { id:2, details: { denominations : [ { code: "USD",value :110} ] } }
]

期望输出 #

[
  { id:1, details: { denominations : [ { code: "USD", value: 100 }, { code: "USD",value :110} ] } },
  { id:2, details: { denominations : [ { code: "USD", value: 100 }, { code: "USD",value :110} ] } }
]

实现方案（JavaScript） #

const input = [
  { id:1, details: { denominations : [ { code: "USD",value :100} ] } },
  { id:1, details: { denominations : [ { code: "USD",value :110} ] } },
  { id:2, details: { denominations : [ { code: "USD",value :100} ] } },
  { id:2, details: { denominations : [ { code: "USD",value :110} ] } }
];

const result = Object.values(input.reduce((acc, current) => {
  // 按id分组处理
  if (!acc[current.id]) {
    acc[current.id] = {
      id: current.id,
      details: { denominations: [...current.details.denominations] }
    };
  } else {
    acc[current.id].details.denominations.push(...current.details.denominations);
  }
  return acc;
}, {}));

console.log(result);

逻辑说明 #

1. 用reduce遍历输入数组，以id为键将对象分组存入累加器
2. 首次遇到某id时，初始化对象并复制当前的denominations数组
3. 再次遇到相同id时，直接将当前的denominations元素追加到已有数组中
4. 最后通过Object.values将分组后的对象转换为数组，得到目标结构

内容的提问来源于stack exchange，提问作者Hiep Phan Quang