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

MongoDB의 집계(aggregate)는 데이터베이스 내에서 데이터를 질의하고 변환하는 도구이다. 특히 필터링, 그룹화, 정렬 및 데이터 변환과 같은 작업에 유용하다보니, MongoDB 를 잘 사용하기 위해서는 aggregate 함수가 필수이다. 모든 기능을 설명하지는 못했지만, 주로 쓰는 기능위주로 정리했다.

$match

$match 절은 sql 의 where 절과 동일하다보면된다. group by 하기전에 조건으로 필터링해서 document 대상을 줄인다. payment 컬렉션은 학원 수강료 수납이력이 적재되고 있고, 여기서는 삭제한 대상은 제외하기 위해서 필터링한 예시이다.

db.payment.aggregate([
    { $match : { delYn: false } }, // group by 하기전 대상조건
]);

$project

$project 영역은 select 절과 비슷하다. 사용안해도 되지만, 사용안하면 payment 컬렉션의 모든 필드가 결과로 출력되기 때문이고, 중간에 substr 해서 그 데이터를 기반으로 group by 를 하고 싶을 때 필드를 추가하여 만들 수도 있다.

db.payment.aggregate(
    [
        { $match: { delYn: false } }, 
        { $project:
            {
                paymentMonth: { $substr: [ "$paymentDate", 0, 7 ] },
                amount : 1
            }
        }
    ]
)

// 출력 결과
[
  {
    _id: ObjectId("2166d84e0c"),
    amount: 80000,
    paymentMonth: '2021-11'
  },
  {
    _id: ObjectId("2166d84e0e"),
    amount: 80000,
    paymentMonth: '2021-12'
  },
  .....
]

그리고 출력하고 싶은 필드를 정의하고 1 로 바인딩하면 나오고, 0 으로 하면 생략된다. _id 필드는 정의하지 않아도 기본으로 출력이 되는데, 안나오게 하고 싶다면, _id : 0 로 넣어야 한다.

db.payment.aggregate(
    [
        { $match: { delYn: false } }, // group by 하기전 대상조건
        { $project:
            {
                paymentMonth: { $substr: [ "$paymentDate", 0, 7 ] },
                amount : 1,
                _id : 0
            }
        }
    ]
)

// 출력 결과
[
  { amount: 80000, paymentMonth: '2021-11' },
  { amount: 80000, paymentMonth: '2021-12' },
  .....
]

$addFields

$project 에서도 필드 추가는 할 수 있지만, 출력하려는 필드마다 명시적으로 필드명:1과 같이 정의를 해야한다. 그냥 이렇게 다 정의하지 않고, 기존 데이터 출력 레이아웃에다가 추가만 하고싶다면 $addFields로 정의하면 된다.

db.payment.aggregate([
    { 
        $match: { delYn: false }
    }, 
    {
        $addFields: {
            paymentMonth: { $substr: [ "$paymentDate", 0, 7 ] }
        }
    }
])

// 출력 결과
[
  {
    _id: ObjectId("3251c49af27e"),
    memberId: '27a6469a2af',
    paymentDate: '2022-03-01',
    classesPerWeek: '1',
    minutesPerSession: '80',
    amountModifyYn: true,
    amount: 100000,
    paymentType: '2',
    .....
    delYn: false,
    paymentMonth: '2022-03'   // 필드가 추가되었다.
  },
  .....
]

$unset

출력이 필요없는 정보들은 $unset 함수를 이용해서 제거하면 된다. $project 는 필요한 부분만 정의해서 출력할 컬럼을 정의한다면, $unset은 제외할 컬럼만 정의한다. 아래 쿼리에서는 payment 문서안에 memberIdInfo 라는 문서가 포함되어 있는 구조인데, 출력하지 않도록 제거한 예시이다. 여러가지를 넣을 수 있어 [] 안에 입력해야 한다.

db.payment.aggregate([
    {
        $match: { delYn: false }
    },
    {
        $addFields: {
            paymentMonth: { $substr: [ "$paymentDate", 0, 7 ] }
        }
    },
    {
        $unset: [ "memberIdInfo" ]
    }
])

$group

$group을 사용해서 데이터를 그룹핑하고, sum, min, max, avg 등 집계함수를 사용하여 결과값을 나오게 하는 함수이다. _id 에 그룹핑할 기준 필드를 지정하고, 나머지는 집계함수를 이용하여 데이터를 만든다. 월별로 수강료 합산하려는 예시이다.

db.payment.aggregate([
    { 
        $match: { delYn: false }
    }, 
    {
        $addFields: {
            paymentMonth: { $substr: [ "$paymentDate", 0, 7 ] }
        }
    },
    {
        $group: { 
            _id: "$paymentMonth", 
            sumAmount: { $sum: "$amount" } 
        }
    }
])

// 출력 결과
[
  { _id: '2021-11', sumAmount: 80000 },
  { _id: '2021-12', sumAmount: 180000 },
  { _id: '2023-05', sumAmount: 105500 },
  { _id: '2023-06', sumAmount: 106500 },
  .....
]

여러 필드를 기준으로 group 지정하고 싶다면 아래처럼 하면 된다.

{ 
    $group: {
        _id: { paymentMonth : "$paymentMonth", memberId : "$memberId" },
        amount: { $sum: "$amount" },
        count: { $sum : 1 }
    }
}

$sort

정렬하는 건 $sort를 사용하면 된다. 1이면 올림차순, -1이면 내림차순으로 정렬된다.

db.payment.aggregate([
    { 
        $match: { delYn: false }
    }, 
    {
        $addFields: {
            paymentMonth: { $substr: [ "$paymentDate", 0, 7 ] }
        }
    },
    {
        $group: { 
            _id: "$paymentMonth", 
            sumAmount: { $sum: "$amount" } 
        }
    },
    { $sort : { _id: 1 } } // 정렬기준 추가.
])


// 출력 결과
[
  { _id: '2021-11', sumAmount: 80000 },
  { _id: '2021-12', sumAmount: 18000 },
  { _id: '2022-01', sumAmount: 62000 },
  { _id: '2022-02', sumAmount: 62000 },
  .....
]

$limit

$limit 를 지정해서 출력할 document 수를 조정할 수 있다. 고액순으로 top1~5 만 하고 싶다면 $sort 와 $limit 를 사용하면 쉽다.

db.payment.aggregate([
    {
        $match: { delYn: false }
    }, 
    {
        $addFields: {
            paymentMonth: { $substr: [ "$paymentDate", 0, 7 ] }
        }
    },
    {
        $group: { 
            _id: "$paymentMonth", 
            sumAmount: { $sum: "$amount" } 
        }
    },
    { $sort : { sumAmount: -1 } }, // 금액기준으로 내림차순
    { $limit : 5 } // top5 만 출력.
])

// 출력 결과
[
  { _id: '2020-08', sumAmount: 153500 },
  { _id: '2021-11', sumAmount: 129500 },
  { _id: '2020-01', sumAmount: 116000 },
  { _id: '2020-09', sumAmount: 113000 },
  { _id: '2021-12', sumAmount: 111500 }
]

$out

출력결과를 그냥 조회만 할게 아니라, export 하려면 어떻게 해야 할까. 조회 결과를 별도의 컬렉션에 담아놓으면 된다. 그 방법을 제공하는게 $out 명령어이다.

db.payment.aggregate([
    {
        $match: { delYn: false }
    }, 
    {
        $addFields: {
            paymentMonth: { $substr: [ "$paymentDate", 0, 7 ] }
        }
    },
    {
        $group: { 
            _id: "$paymentMonth", 
            sumAmount: { $sum: "$amount" } 
        }
    },
    { $sort : { sumAmount: -1 } }, // 금액기준으로 내림차순
    { $out : { db : "mydb", coll : "exportData" } } 
])

db.exportData.find()
// 출력 결과
[
  { _id: '2021-11', sumAmount: 80000 },
  { _id: '2021-12', sumAmount: 18000 },
  { _id: '2022-01', sumAmount: 62000 },
  { _id: '2022-02', sumAmount: 62000 },
  .....
]

mongodb