[11일차] MongoDB 정리본

 

db.restaurants.aggregate([{ $match: { cuisine: "Hamburgers" } }])
db.restaurants.findOne()
db.restaurants.aggregate([
    { $match: { cuisine: "Hamburgers" } }, // 배열이 stage 추가 가능
    { $sort: { name: 1 } }, // 오름차순 정렬
    { $skip: 10 }, // 상위 10개 skip
    { $limit: 10 }, // 10개만 출력
    {
        $project: {
            _id: 0, cuisine: 1, borough: 1, name: 1
        }
    }
])

db.restaurants.aggregate([
    { $match: { "address.street": "Stillwell Avenue" } },
    {
        $project: {
            _id: 0, cuisine: 1, name: 1, dates: "$grades.date",
            valuation: "$grades.grade", scores: "$grades.score" // 실제 필드 $ 표시해준다.
        }
    }
])

db.restaurants.aggregate([
    { $match: { "address.street": "Stillwell Avenue" } },
    { $unwind: "$grades" }, // unwind 전개 
    {
        $project: {
            _id: 0, cuisine: 1, name: 1, dates: "$grades.date",
            valuation: "$grades.grade", scores: "$grades.score" // 실제 필드 $ 표시해준다.
        }
    }
])

// "Brooklyn"에 있는 "Italian" 레스토랑 중에서 20점 이상 평가를 받은 내용 출력

db.restaurants.aggregate([
    { $match: { "cuisine": "Italian", "borough": "Brooklyn" } },
    {
        $project:
            {
                _id: 0, cuisine: 1, name: 1,
                praise: {
                    $filter: {
                        input: "$grades", // 문서마다 grades가 있는데
                        as: "good", // good이라는 변수에 담아서
                        cond: { $gte: ["$$good.score", 20] } // cond안에서 변수 지칭 $$ 두개 good이라는 변수에 score값이 20점 이상 받은거 출력 
                    }
                }
            }
    }
])


// 맨 마지막 stage에 추가

db.restaurants.aggregate([
    { $match: { "cuisine": "Italian", "borough": "Brooklyn" } },
    {
        $project:
            {
                _id: 0, cuisine: 1, name: 1,
                praise: {
                    $filter: {
                        input: "$grades",
                        as: "good",
                        cond: { $gte: ["$$good.score", 20] }
                    }
                }
            }
    },
    { $match: { "praise.0": { "$exists": true } } } // praise 항목에 0번째인거 출력
]).toArray().length

db.restaurants.aggregate([
    { $match: { cuisine: "Italian", borough: "Brooklyn" } },
    {$project:
        {_id: 0, cuisine: 1, name: 1,
            last_award: {$arrayElemAt: ["$grades", 0]}, // 가장 최근거    
            first_award: {$arrayElemAt: ["$grades", -1]} // 가장 마지막거
        }
    }
])

db.restaurants.findOne()

db.restaurants.aggregate([
    {$match: { cuisine: "Italian", borough: "Brooklyn"}},
    {$project:
        {_id: 0, cuisine: 1, name: 1,
                total_award: {$size: "$grades"}
         }
    }
])

db.restaurants.aggregate([
    {$match: { cuisine: "Italian", borough: "Brooklyn"}}, // 최소한 등급 이상 받은 배열
    {$match: {"grades.0":{"$exists":true}}},
    {$project:
        {_id: 0, cuisine:1, name:1,
            max_award: {$max: "$grades.score"} // score 누적 상위 배열 출력
        }
    }
])

db.restaurants.aggregate([
    {$match: { cuisine: "Italian", borough: "Brooklyn"}}, 
    {$match: {"grades.0":{"$exists":true}}},
    {$project:
        {_id: 0, cuisine:1, name:1,
            sum_award: {$sum: "$grades.score"} //각 도큐먼트 내에 grades를 sum한 것
        }
    }
])

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

// 연도별 설립회사 직원 수의 평균
db.companies.aggregate([
    {$group: {
        _id : {founded_year: "$founded_year"}, // group stage _id는 그룹핑을 무엇을 할지 지정해준다.
        average_number_of_employees: {$avg: "$number_of_employees"}
    }},
    {$sort: {average_number_of_employees : -1}} // 평균 직원수 많은 순으로 출력
])


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

db.companies.aggregate([
    //{$match: {"relationships.0":{"$exists":true}}}, // 1번째 항목이 있는 지 판단 이거 써도 괜찮다.
    {$match: {"relationships.person": {$ne: null}}}, // null 이 아닌 것을 대상으로
    {$project: {relationships: 1, _id:0}},
    {$unwind: "$relationships"},
    {$group:{
        _id: "$relationships.person", // 전체 grouping 후 sum
        count: {$sum :1}
    }},
    {$sort: {count:-1}} //desc count 높은 순으로 출력
])

// 2000년 포함 이후부터 설립된 회사들을 배열로 집계
db.companies.aggregate([
    {$match: {founded_year : {$gte:2000}}},
    {$group :{
        _id: {founded : "$founded_year"}, // 쓸거면 라벨링해서 쓴다.
        competitions: {$push: "$name" } // 같은 회사끼리 묶음  
    }},
    {$sort: {"_id.founded": -1}} //desc 정렬
])

// 다중 필드로 group by
// 2010년 포함 이후에 설립된 회사를 연도와 카테고리 별로 검색
db.companies.aggregate([
    {$match: {founded_year : {$gte:2000}}},
    {$group :{
        _id: {founded : "$founded_year", category_code: "$category_code"}, 
        competitions: {$push: "$name" } 
    }},
    {$sort: {"_id.founded": -1}} 
])