01. lookup 1 (이론)

MongoDB 철학

Accessed Together Stays Together

• 함께 조회하는 것을 함께 둬라
• 조인을 피하고 도큐먼트에 내장하라.

MongoDB Join 방식

MongoDB는 Left Outer Join 형태로만 Join을 지원하고 Lookup이라고 한다

01. lookup 2 (실습)

https://www.mongodb.com/docs/manual/reference/operator/aggregation/lookup/

# use stores

# db.restaurants.insertMany( [
   {
      _id: 1,
      name: "American Steak House",
      food: [ "filet", "sirloin" ],
      beverages: [ "beer", "wine" ]
   },
   {
      _id: 2,
      name: "Honest John Pizza",
      food: [ "cheese pizza", "pepperoni pizza" ],
      beverages: [ "soda" ]
   }
] )

# db.orders.insertMany( [
   {
      _id: 1,
      item: "filet",
      restaurant_name: "American Steak House"
   },
   {
      _id: 2,
      item: "cheese pizza",
      restaurant_name: "Honest John Pizza",
      drink: "lemonade"
   },
   {
      _id: 3,
      item: "cheese pizza",
      restaurant_name: "Honest John Pizza",
      drink: "soda"
   }
] )

# **db.restaurants.aggregate([
		{
				$lookup: {
						from: "orders",
						localField: "name",
						foreignField: "restaurant_name",
						as: "orders"
				}
		},
		{
				$unwind: "$orders"
		},
		{
				$match: {
						$expr: {
								$and: [
										{$in: ["$orders.item", "$food"]},  // orders.item이 food 배열안에 포함되어있는지
										{$in: ["$orders.drink", "$beverages"]}
								]
						}
				}
		},
		{
				$group: {
						_id: "$_id",
						name: { $first: "$name" },  // 첫번값 가져옴
						food: { $first: "$food" },
						beverages:{ $first: "$beverages" },
						orders: {
								$push: "$orders"
						}
				}
		}
])**
[
  {
    _id: 2,
    name: 'Honest John Pizza',
    food: [ 'cheese pizza', 'pepperoni pizza' ],
    beverages: [ 'soda' ],
    orders: [
      {
        _id: 3,
        item: 'cheese pizza',
        restaurant_name: 'Honest John Pizza',
        drink: 'soda'
      }
    ]
  }
]

위와 비슷한 결과의 쿼리를 아래처럼도 작성 가능