MongoDB: 기본 쿼리(Basic Quries)

이 글은 전공 "빅데이터시스템" 강의 내용을 정리한 글입니다.

Query Document

The find() method

db.COLLECTION_NAME.find(query, projection)

• collection내에 모든 document를 select

db.inventory.find()

db.inventory.find().pretty()

SELECT * FROM inventory

Query operator

Comparison operators

• $eq

  • { field : {$eq: value}}

    SELECT * FROM inventory WHERE qty = 25

  • db.inventory.find({qty:{$eq:25}}) db.inventory.find({qty:25})

• $gt, $gte, $lt, $lte

  • {field :{$gt: value}}

    SELECT * FROM inventory WHERE qty > 25 (qty>=25)

  • db.inventory.find({qty:{$gt:25}}) db.inventory.find({qty:{$gte:25}})

• $ne

  • {field : {$ne: value}}

    SELECT * FROM inventory WHERE qty != 25

  • db.inventory.find({qty:{$ne: 25}})

• $in

  • {field: {$in: [value1, value2, ... ,valueN]}}

    SELECT * FROM inventory WHERE status in ("A", "D")

  • db.inventory.find({status:{$in:["A","D"]}})

Logical operators

• $and

  • {$and : [{expression1}, {expression2}, ... , {expressionN}]}

    SELECT * FROM inventory WHERE status="D" and qty <= 75

  • db.inventory.find({$and:[{status:"D"}, {qty:{$lte:75}}]}) db.inventory.find({status:"D", qty:{$lte:75}})

• $or

  • {$or: [{expression1},{expression2},...,{expressionN}]}

    SELECT * FROM invetory WHERE status = "A" OR qty<30

  • db.inventory.find({$or[{status:"A"},{qty:{$lt:30}}]})

• $not

  • {field: {$not: {operator-expression}}}
  • db.inventory.find({qty:{$not:{$gt:75}}})

Query embedded document

• {field:document} or (”field.nestedField”) → “ “ 빼먹으면 안됨

db.inventory.find({size:{h: 14, w: 21, uom:"cm"}})

db.inventory.find({"size.h": 14, "size.w": 21, "size.uom": "cm"})

Projection

• {field1: value1 , field2: value2 ...}

  • value

    • 1 or true : 해당 field를 포함해서 출력
    • 0 or false : 해당 field를 제외하고 출력

    db.inventory.find({item: "paper"}, {size:0, _id:0})

    SELECT item, qty, status FROM inventory WHERE item = "paper"

Update Document

• update()

  • db.COLLECTION_NAME.update(query, updated_docuemnt)

  • // item이 paper인 하나의 document만 paperless로 바꾼다 db.inventory.update({item:"paper"},{$set:{item:"paperless"}})

  • db.COLLECTION_NAME.update(query, updated_docuemnt, multi option)

    UPDATE inventory SET item = "paperless" where item = "paper"

  • // item이 paper인 모든 document를 paperless로 바꾼다 db.inventory.update({item:"paper"},{$set:{item:"paperless"}}, {multi: true})

Delete Document

• remove()

  • db.COLLECTION_NAME.remove(query, justOne)

    • query : (Optional) 삭제 기준
    • justOne: (Optional) 1이면 true, 하나의 document만 삭제

    // item이 paper인 모든 document를 제거한다.
    db.inventory.remove({item:"journal"})
    // 첫번째 record만 제거한다.
    db.inventory.remove({item:"notebook"}, 1)
    // 모든 document를 제거한다.
    db.inventory.remove({})

Task

Task 1

Write a query to display the fields restaurant_id, name, borough and zip code, but exclude the field _id for all the documents in the collection restaurant

db.restaurants.find({}, {"_id": 0, "restaurant_id" : 1, "name": 1, "borough": 1, "zipcode": 1})

Task 2

Write a MongoDB query to find the restaurant_id, name, borough and cuisine for those restaurants which are not beloging to the borough Queens

db.restaurants.find({"borough": {$ne: "Queens"}}, 
                    {"_id": 0, "restaurant_id" : 1, "name": 1, "borough": 1, "cuisine": 1}) 

Task 3

Find the restaurants that do not prepare any cuisine of 'American' and their grade score more than 70

db.restaurants.find({"cuisine": {$ne: "American"}, "grades.score": {$gt: 70}})

Task 4

Find the restaurant Id, name, borough and cuisine for those restaurants which are not belonging to the borough Staten Island or Queens or Bronxor Brooklyn

db.restaurants.find({"borough": {$nin : ["Staten Island", "Queens", "Bronxor Brooklyn" ]}},
                        {"_id": 0, "restaurant_id": 1, "name": 1, "borough": 1, "cuisine": 1})

Task 5

Find the restaurants which do not prepare any cuisine of ‘American’ and achieved a grade point ‘A’ not belongs to ths borough Brooklyn

db.restaurants.find({"cuisine": {$ne: "American"}, "borough": {$ne: "Brooklyn"}, "grades.grade": "A" })