[MongoDB] 간단한 aggregation $lookup

{
   $lookup:
     {
       from: <collection to join>,
       localField: <field from the input documents>,
       foreignField: <field from the documents of the "from" collection>,
       as: <output array field>
     }
}

MongoDB에서 aggregation 연산은 다음과 같이 정의합니다.

Aggregation operations are expressions you can use to produce reduced and summarized results in MongoDB. MongoDB's aggregation pipeline, part of the Query API, allows you to create a pipeline that consists of one or more stages, each of which performs a specific operation on your data.

여러가지 스테이지 중에 간단한 $lookup 예시를 살펴보겠습니다.

$lookup은 outer left join과 유사합니다.

{
   $lookup:
     {
       from: <collection to join>,
       localField: <field from the input documents>,
       foreignField: <field from the documents of the "from" collection>,
       as: <output array field>
     }
}

1. from: 조인할 컬렉션

2. localField: from 도큐먼트와 매칭할 내 도큐먼트의 필드

3. foreignField: from 도큐먼트의 필드

4. as: 새롭게 만들어질 필드의 별칭

 

이론적인 부분만으로는 이해가 쉽지 않으므로 예시 하나를 보겠습니다.

users 도큐먼트에 거주지 정보(city 필드)가 있고 도시 정보를 담은 cities 도큐먼트가 있습니다.

    await cities.insertMany([
      {
        name: '서울',
        population: 1000,
      },
      {
        name: '부산',
        population: 342,
      },
    ]);
    
        await users.insertMany([
      {
        name: 'Kim',
        age: 24,
        hobby: [
          {
            name: 'readig books',
            type: 'non-active',
          },
          {
            name: 'working out',
            type: 'active',
          },
        ],
        // many to many
        city: '서울',
      },
      {
        name: 'Lee',
        age: 33,
        hobby: [
          {
            name: 'soccer',
            type: 'active',
          },
        ],
        city: '서울',
      },
      {
        name: 'Park',
        age: 30,
        hobby: [
          {
            name: 'tennis',
            type: 'active',
          },
        ],
        city: '부산',
      },
      {
        name: 'Amy',
        age: 27,
        hobby: [
          {
            name: 'watching movies',
            type: 'non-active',
          },
        ],
        city: '부산',
      },
      {
        name: 'Barkez',
        age: 31,
        hobby: [
          {
            name: 'driving with car',
            type: 'non-active',
          },
        ],
        city: '부산',
      },
    ]);

 

이 둘을 $lookup 하고 출력해보겠습니다.

    const cursor = users.aggregate(
      {
        $lookup: {
          from: 'cities',
          foreignField: 'name',
          localField: 'city',
          as: 'city_detail',
        },
      },
    );
    
    await cursor.forEach(console.log);

 

console.log 로 출력한 결과는 다음과 같습니다.

{
  _id: new ObjectId("62c3ef1c417f0cb2c57655e1"),    
  name: 'Kim',
  age: 24,
  hobby: [
    { name: 'readig books', type: 'non-active' },   
    { name: 'working out', type: 'active' }
  ],
  city: '서울',
  city_detail: [
    {
      _id: new ObjectId("62c3ef1c417f0cb2c57655df"),
      name: '서울',
      population: 1000
    }
  ]
}
{
  _id: new ObjectId("62c3ef1c417f0cb2c57655e2"),
  name: 'Lee',
  age: 33,
  hobby: [ { name: 'soccer', type: 'active' } ],
  city: '서울',
  city_detail: [
    {
      _id: new ObjectId("62c3ef1c417f0cb2c57655df"),
      name: '서울',
      population: 1000
    }
  ]
}
{
  _id: new ObjectId("62c3ef1c417f0cb2c57655e3"),
  name: 'Park',
  age: 30,
  hobby: [ { name: 'tennis', type: 'active' } ],
  city: '부산',
  city_detail: [
    {
      _id: new ObjectId("62c3ef1c417f0cb2c57655e0"),
      name: '부산',
      population: 342
    }
  ]
}
{
  _id: new ObjectId("62c3ef1c417f0cb2c57655e4"),
  name: 'Amy',
  age: 27,
  hobby: [ { name: 'watching movies', type: 'non-active' } ],
  city: '부산',
  city_detail: [
    {
      _id: new ObjectId("62c3ef1c417f0cb2c57655e0"),
      name: '부산',
      population: 342
    }
  ]
}
{
  _id: new ObjectId("62c3ef1c417f0cb2c57655e5"),
  name: 'Barkez',
  age: 31,
  hobby: [ { name: 'driving with car', type: 'non-active' } ],
  city: '부산',
  city_detail: [
    {
      _id: new ObjectId("62c3ef1c417f0cb2c57655e0"),
      name: '부산',
      population: 342
    }
  ]
}

 필터도 넣을 수 있습니다.

    const cursor = users.aggregate([
      {
        $lookup: {
          from: 'cities',
          foreignField: 'name',
          localField: 'city',
          as: 'city_detail',
        },
      },
      {
        $match: {          
          $and: [
            {
              'city_detail.population': {
                $gte: 500,
              },
            },
            {
              age: {
                $gte: 30,
              },
            },
          ],
        },
      },
    ]);

{
  _id: new ObjectId("62c3efef48666b1f10da94e8"),    
  name: 'Lee',
  age: 33,
  hobby: [ { name: 'soccer', type: 'active' } ],    
  city: '서울',
  city_detail: [
    {
      _id: new ObjectId("62c3efef48666b1f10da94e5"),
      name: '서울',
      population: 1000
    }
  ]
}