[12일차] MongoDB 정리본

2022-07-05 TUE

---

 

sample DB 사용

db.companies.findOne({name : /Twitter/})


db.companies.aggregate([
    {$match: {"ipo.pub_year": {$exists: true, $ne: null}}},
    {$group:{
        _id : {ipo_year: "$ipo.pub_year"},
        companies: {$push: "$name"}
     }},
     {$sort: {"_id.ipo.pub_year" : 1}}
])

db.companies.aggregate([
    {$group: {
        _id: null,
        count: {$sum: 1} 
    }}
]) // document 개수세기 

db.companies.aggregate([
    {$group: {
        _id: "$category_code"
    }}
]) // distinct 효과 

// 20개 이상 회사와 연관되어 있는 지 판단 Ⅰ
db.companies.aggregate([
    {$match:{"relationships.person": {$ne: null}}},
    {$project: {relationships: 1, _id : 0}},
    {$unwind: "$relationships"}, 
    {$group: {
        _id: "$relationships.person",
        count: {$sum : 1}
    }},
    {$sort : {count: -1}}, // desc
    {$project: {_id: 1, count: 1,
                famous: {$cond: {if:{$gt: ["$count", 20]}, // 20보다 큰거 
                                    then: true,    // cond 연산자 추가 if를 쓸 수 있으므로 
                                    else: false
                                }
                        } 
    }}
])

// 20개 이상 회사와 연관되어 있는 지 판단 Ⅱ

db.companies.aggregate([
    {$match:{"relationships.person": {$ne: null}}},
    {$project: {relationships: 1, _id: 0}},
    {$unwind: "$relationships"},
    {$group: {
        _id: "$relationships.person",
        count: {$sum: 1}
    }},
    {$sort: {count: -1}},
    {$project: {_id: 1, count: 1,
                famous: {$cond:[{$gt: ["$count", 20]}, true, false]}
    }}
])


// 가볍게 보기...
// 펀딩 라운드 날짜로 정렬 후 회사 별 펀딩 금액과 연도 집계
db.companies.aggregate([
    {$match: {funding_rounds: {$ne: []}}},
    {$unwind: "$funding_rounds"},
    {$sort: {"funding_rounds.funded_year": 1,
            "funding_rounds.funded_month": 1,
            "funding_rounds.funded_day": 1}},
    {$group: {
        _id: {company: "$name"},
        funding: {
            $push: {
                amount: "$funding_rounds.raised_amount",
                year: "$funding_rounds.funded_year"
            }
        }
    }}
])

// 위 조건에서 회사별 최초, 마지막 펀딩 내용과 총 펀딩 횟수, 총 금액 집계

db.companies.aggregate([
    {$match: {funding_rounds: {$ne: []}}},
    {$unwind: "$funding_rounds"},
    {$sort: {"funding_rounds.funded_year": 1,
            "funding_rounds.funded_month": 1,
            "funding_rounds.funded_day": 1}},
    {$group: {
        _id: {company: "$name"},
        first_round: {$first: "$funding_rounds"},
        last_round: {$last: "$funding_rounds"},
        num_round: {$sum: 1},
        total_raised: {$sum: "$funding_rounds.raised_amount"}
    }},
    {$project: {
        _id:0,
        company: "$_id.company",
        first_round: {
            amount: "$first_round.raised_amount",
            article : "$last_round.source_url",
            year:"$last_round.funded_year"
        },
        num_rounds: 1,
        total_raised: 1
     }},
    {$sort: {total_raised: -1}}
])

 

---

test DB 사용

 

db.orders.insertMany([
    { "_id" : 1, "item" : "almonds", "price" : 12, "quantity" : 2 },
    { "_id" : 2, "item" : "pecans", "price" : 20, "quantity" : 1 },
    { "_id" : 3  }
])

db.inventory.insertMany([
    { "_id" : 1, "sku" : "almonds", description: "product 1", "instock" : 120 },
    { "_id" : 2, "sku" : "bread", description: "product 2", "instock" : 80 },
    { "_id" : 3, "sku" : "cashews", description: "product 3", "instock" : 60 },
    { "_id" : 4, "sku" : "pecans", description: "product 4", "instock" : 70 },
    { "_id" : 5, "sku": null, description: "Incomplete" },
    { "_id" : 6 }
])

db.orders.aggregate([
    {$lookup: {
        from: "inventory", 
        localField: "item",
        foreignField: "sku", // inventory
        as: "inventory_docs"    
    }}
])

// class collection  수강신청
db.classes.insertMany( [
    { _id: 1, title: "Reading is ...", enrollmentlist: [ "giraffe2", "pandabear", "artie" ], days: ["M", "W", "F"] },
    { _id: 2, title: "But Writing ...", enrollmentlist: [ "giraffe1", "artie" ], days: ["T", "F"] }
])

// 학생 
db.members.insertMany( [
    { _id: 1, name: "artie", joined: new Date("2016-05-01"), status: "A" },
    { _id: 2, name: "giraffe", joined: new Date("2017-05-01"), status: "D" },
    { _id: 3, name: "giraffe1", joined: new Date("2017-10-01"), status: "A" },
    { _id: 4, name: "panda", joined: new Date("2018-10-11"), status: "A" },
    { _id: 5, name: "pandabear", joined: new Date("2018-12-01"), status: "A" },
    { _id: 6, name: "giraffe2", joined: new Date("2018-12-01"), status: "D" }
])

db.classes.aggregate([
    {$lookup: {
        from: "members",
        localField: "enrollmentlist", // 배열
        foreignField: "name",
        as: "enroll_info"
    }}
])

/* 신용카드 신용한도 
전체 신용한도를 넘어선 고객 찾기
같은 도큐먼트끼리 비교한다. */ 

db.accounts.insertMany([
    {"_id": 1, "credits": 5000, "expenses": [2000, 2000]},
{"_id": 2, "credits": 4000, "expenses": [1000, 4000, 2000]},
{"_id": 3, "credits": 3000, "expenses": [1500, 750]},
{"_id": 4, "credits": 2000, "expenses": [2500, 750]}
])

db.accounts.find({
    $expr: { // 동일 도큐먼트내에서 필드 비교 
        //expr: 연산자 내부의 외부 및 로컬 필드에 대한 동등성 일치
        $gt: [{$sum: ["$expenses"]}, "$credits"]
        
    }
})