MongoDB aggregate 함수로 피벗테이블(pivot) 만들기 -

회원정보, 수납내역, 일정관리 등 간단한 사이트를 개발한 적이 있는데, MongoDB 를 사용하여 데이터를 관리하고 있다. 월별로 수납정보를 피벗테이블 형태로 바꾸어 보여줘야 할 일이 생겼다. MongoDB 의 aggregate 함수를 이용해서 구현할 수 있었는데, 생각보다 소스가 너무 길어져서 mongoDB 를 괜히 사용했나 싶은 생각까지 들었다. 그 힘들었던 과정을 스탭별로 정리하기로 했다.

최초 데이터 구조는 아래표와 같고, 회원별로 수납한 수납일자와 학원비 내역이다.

memberId	paymentDate	amount
id_0001	2024-01-04	120000
id_0001	2024-02-09	90000
id_0002	2024-01-04	100000
id_0002	2024-02-10	90000

원하는 레이아웃은, 아래처럼 월별로 피벗된 데이터 형태로 웹페이지에 출력하려고 한다.

memberId	2024-01	2024-02
id_0001	120000	90000
id_0002	100000	90000

Pivot table 만들기

우선 MongoDB 에 저장되어 있는 데이터를 조회한다.

mydb> db.testData.find( { }, { _id : 0 } )
[
  { paymentDate: '2023-12-04', amount: 120000, memberId: 'id_0001' },
  { paymentDate: '2024-01-04', amount: 120000, memberId: 'id_0001' },
  { paymentDate: '2024-02-09', amount: 90000, memberId: 'id_0001' },
  { paymentDate: '2023-10-10', amount: 140000, memberId: 'id_0002' },
  { paymentDate: '2023-11-13', amount: 280000, memberId: 'id_0002' },
  { paymentDate: '2024-01-04', amount: 100000, memberId: 'id_0002' },
  { paymentDate: '2024-02-10', amount: 90000, memberId: 'id_0002' }
]

기본적으로 aggregate 함수를 이용해서 pivot table 구조를 만들것이다. 월별로 그룹핑할 필드 paymentYm를 하나 추가한다. 데이터 형식이 Date 는 아니고 String 이라서 $substr 로 잘랐다.

MongoDB aggregate 함수로 group by 기능 사용하기

mydb> db.testData.aggregate([
    {
        $addFields : {
            paymentYm : {
                $substr : [ "$paymentDate", 0, 7 ]
            }
        }
    }
])

// 출력결과
[
  {
    _id: ObjectId("65c781ee20d712293ea1a9da"),
    paymentDate: '2023-12-04',
    amount: 120000,
    memberId: 'id_0001',
    paymentYm: '2023-12'
  },
  {
    _id: ObjectId("65c781ee20d712293ea1a9db"),
    paymentDate: '2024-01-04',
    amount: 120000,
    memberId: 'id_0001',
    paymentYm: '2024-01'
  },
  {
    _id: ObjectId("65c781ee20d712293ea1a9dc"),
    paymentDate: '2024-02-09',
    amount: 90000,
    memberId: 'id_0001',
    paymentYm: '2024-02'
  },
  {
    _id: ObjectId("65c781ee20d712293ea1a9dd"),
    paymentDate: '2023-10-10',
    amount: 140000,
    memberId: 'id_0002',
    paymentYm: '2023-10'
  },
  {
    _id: ObjectId("65c781ee20d712293ea1a9de"),
    paymentDate: '2023-11-13',
    amount: 280000,
    memberId: 'id_0002',
    paymentYm: '2023-11'
  },
  {
    _id: ObjectId("65c781ee20d712293ea1a9df"),
    paymentDate: '2024-01-04',
    amount: 100000,
    memberId: 'id_0002',
    paymentYm: '2024-01'
  },
  {
    _id: ObjectId("65c781ee20d712293ea1a9e0"),
    paymentDate: '2024-02-10',
    amount: 90000,
    memberId: 'id_0002',
    paymentYm: '2024-02'
  }
]

혹시나 같은 달에 분할납부를 했을 수도 있으니, 회원별, 월별로 우선 수강료를 합한다.

db.testData.aggregate([
    {
        $addFields : {
            paymentYm : {
                $substr : [ "$paymentDate", 0, 7 ]
            }
        }
    },
    {
        $group : {
            _id : {
                memberId : "$memberId",
                paymentYm :"$paymentYm"
            },
            total_amount : {
                $sum : "$amount"
            }
        }
    }
])

// 출력결과
[
  {
    _id: { memberId: 'id_0002', paymentYm: '2023-11' },
    total_amount: 280000
  },
  {
    _id: { memberId: 'id_0002', paymentYm: '2023-10' },
    total_amount: 140000
  },
  {
    _id: { memberId: 'id_0001', paymentYm: '2023-12' },
    total_amount: 120000
  },
  {
    _id: { memberId: 'id_0002', paymentYm: '2024-02' },
    total_amount: 90000
  },
  {
    _id: { memberId: 'id_0002', paymentYm: '2024-01' },
    total_amount: 100000
  },
  {
    _id: { memberId: 'id_0001', paymentYm: '2024-01' },
    total_amount: 120000
  },
  {
    _id: { memberId: 'id_0001', paymentYm: '2024-02' },
    total_amount: 90000
  }
]

피벗데이터라는게 { '2023-11' : 90000, '2024-12' : 10000 } 이런식의 데이터를 만들어야 하기 때문에, 조금은 피곤한 사전작업들이 필요하다. $arrayToObject 기능을 활용하면 피벗데이터의 구조를 만들 수 있는데, 미리 몇몇 작업을 해놓아야 한다. key-value 형태로 합칠 데이터를 k, v 이름으로 매핑하고, $push 기능을 이용해서 여러줄이었던 데이터를 한 row 의 데이터로 합친다. 7건이었던 데이터가 2건으로 변경된 걸 볼 수 있다. 공식문서를 참고하자.

db.testData.aggregate([
    {
        $addFields : {
            paymentYm : {
                $substr : [ "$paymentDate", 0, 7 ]
            }
        }
    },
    {
        $group : {
            _id : {
                memberId : "$memberId",
                paymentYm :"$paymentYm"
            },
            total_amount : {
                $sum : "$amount"
            }
        }
    },
    {
        $group : {
            _id : "$_id.memberId",
            tmp1: { $push : { k : "$_id.paymentYm", v : "$total_amount"} }
        }
    }
])

// 출력결과
[
  {
    _id: 'id_0002',
    tmp1: [
      { k: '2023-11', v: 280000 },
      { k: '2023-10', v: 140000 },
      { k: '2024-02', v: 90000 },
      { k: '2024-01', v: 100000 }
    ]
  },
  {
    _id: 'id_0001',
    tmp1: [
      { k: '2023-12', v: 120000 },
      { k: '2024-01', v: 120000 },
      { k: '2024-02', v: 90000 }
    ]
  }
]

$arrayToObject 를 사용한다. tmp2 를 보면 확연히 달라진 레이아웃을 볼 수 있다.

db.testData.aggregate([
    {
        $addFields : {
            paymentYm : {
                $substr : [ "$paymentDate", 0, 7 ]
            }
        }
    },
    {
        $group : {
            _id : {
                memberId : "$memberId",
                paymentYm :"$paymentYm"
            },
            total_amount : {
                $sum : "$amount"
            }
        }
    },
    {
        $group : {
            _id : "$_id.memberId",
            tmp1: { $push : { k : "$_id.paymentYm", v : "$total_amount"} }
        }
    },
    {
         $addFields : {
            tmp2 : {
                $arrayToObject : "$tmp1"
            }
         }
     }
])

// 출력결과
[
  {
    _id: 'id_0002',
    tmp1: [
      { k: '2023-10', v: 140000 },
      { k: '2024-02', v: 90000 },
      { k: '2024-01', v: 100000 },
      { k: '2023-11', v: 280000 }
    ],
    tmp2: {
      '2023-10': 140000,
      '2024-02': 90000,
      '2024-01': 100000,
      '2023-11': 280000
    }
  },
  {
    _id: 'id_0001',
    tmp1: [
      { k: '2024-02', v: 90000 },
      { k: '2023-12', v: 120000 },
      { k: '2024-01', v: 120000 }
    ],
    tmp2: { 
      '2024-02': 90000, 
      '2023-12': 120000, 
      '2024-01': 120000 
    }
  }
]

거의 끝났다. 회원ID값도 tmp2 에 추가해준다.

db.testData.aggregate([
    {
        $addFields : {
            paymentYm : {
                $substr : [ "$paymentDate", 0, 7 ]
            }
        }
    },
    {
        $group : {
            _id : {
                memberId : "$memberId",
                paymentYm :"$paymentYm"
            },
            total_amount : {
                $sum : "$amount"
            }
        }
    },
    {
        $group : {
            _id : "$_id.memberId",
            tmp1: { $push : { k : "$_id.paymentYm", v : "$total_amount"} }
        }
    },
    {
         $addFields : {
            tmp2 : {
                $arrayToObject : "$tmp1"
            }
         }
     },
    {
         $addFields : {
            "tmp2.memberId" : "$_id"
         }
     }
])

// 출력결과
[
  {
    _id: 'id_0002',
    tmp1: [
      { k: '2023-11', v: 280000 },
      { k: '2023-10', v: 140000 },
      { k: '2024-02', v: 90000 },
      { k: '2024-01', v: 100000 }
    ],
    tmp2: {
      '2023-11': 280000,
      '2023-10': 140000,
      '2024-02': 90000,
      '2024-01': 100000,
      memberId: 'id_0002'
    }
  },
  {
    _id: 'id_0001',
    tmp1: [
      { k: '2023-12', v: 120000 },
      { k: '2024-01', v: 120000 },
      { k: '2024-02', v: 90000 }
    ],
    tmp2: {
      '2023-12': 120000,
      '2024-01': 120000,
      '2024-02': 90000,
      memberId: 'id_0001'
    }
  }
]

이제 필요없는 데이터들을 걷어내고, $replaceRoot로 새로운 root 기준을 잡아주면 완성이다.

db.testData.aggregate([
    {
        $addFields : {
            paymentYm : {
                $substr : [ "$paymentDate", 0, 7 ]
            }
        }
    },
    {
        $group : {
            _id : {
                memberId : "$memberId",
                paymentYm :"$paymentYm"
            },
            total_amount : {
                $sum : "$amount"
            }
        }
    },
    {
        $group : {
            _id : "$_id.memberId",
            tmp1: { $push : { k : "$_id.paymentYm", v : "$total_amount"} }
        }
    },
    {
        $addFields : {
            tmp2 : {
                $arrayToObject : "$tmp1"
            }
        }
    },
    {
        $addFields : {
            "tmp2.memberId" : "$_id"
        }
    },
    {
        $replaceRoot: { 
            newRoot : "$tmp2" 
        } 
    }   
])

// 출력결과
[
  {
    '2023-12': 120000,
    '2024-01': 120000,
    '2024-02': 90000,
    memberId: 'id_0001'
  },
  {
    '2023-11': 280000,
    '2023-10': 140000,
    '2024-02': 90000,
    '2024-01': 100000,
    memberId: 'id_0002'
  }
]

데이터는 여기까지 준비를 했고, 웹페이지로 피벗데이터를 표현하기 위해서 Thymeleaf 템플릿 엔진 기능을 사용하면, 아래 화면처럼 구현할 수 있다.

Thymeleaf 과 spring boot 연동하기

mongodb

Pivot table 만들기

더 보면 좋을 글들