👋

ひとりMongoDB University / M121 Aggregation Framework(4)

2021/05/01に公開

MongoDB

tech

この記録は、アドベントカレンダー形式ではじめた、MongoDB Universityの学習コースの記録の続きになります！

ただいまのコース

M121: M121: The MongoDB Aggregation Framework
- https://university.mongodb.com/mercury/M121/2021_March_16/overview

このコースでは、Aggregationの深堀りをしていきます。
前回の記事は、ひとりMongoDB University / M121 Aggregation Framework(3) でした。

Chapter 3: Core Aggregation - Combining Information

この章では、情報の組み合わせ、$group ステージや $unwind, $lookup といった処理を扱います。
なかなか理解が追いつかないので、課題の回答だけになってしまいますが...。

The $group Stage

$group ステージ
- Ref: https://docs.mongodb.com/manual/reference/operator/aggregation/group/

Lab - $group and Accumulators

Problem:

In the last lab, we calculated a normalized rating that required us
to know what the minimum and maximum values for imdb.votes were.
These values were found using the $group stage!

For all films that won at least 1 Oscar, calculate the standard deviation,
highest, lowest, and average imdb.rating. Use the sample standard deviation
expression.

少なくとも1つ以上オスカーを受賞した映画について、レーティングの標準偏差、最大、最小、
平均値を求めてください。

答え:

受賞は awards フィールドにあります
フィールドが欠損している場合もあります
- Exp: awards: '1 win.'
at least 1 Oscar なので、オスカーのノミネートは除外
- Won 1 Oscar とか、Won 3 Oscars になるので、パターンマッチがいい？
- '$regex': new RegExp('Won [0-9]{1,2} Oscar') みたいなのが良さそう

MongoDB Enterprise Cluster0-shard-0:PRIMARY> use aggregations
switched to db aggregations

// サンプルを1つ取り出して、データ構造の確認
db.movies.findOne({ "awards": { '$exists': true } })
{ _id: ObjectId("573a1390f29313caabcd4135"),
  title: 'Blacksmith Scene',
  year: 1893,
  runtime: 1,
  released: 1893-05-09T00:00:00.000Z,
  cast: [ 'Charles Kayser', 'John Ott' ],
  plot: '... 略 ...',
  awards: '1 win.',
  lastupdated: '2015-08-26 00:03:50.133000000',
  type: 'movie',
  directors: [ 'William K.L. Dickson' ],
  imdb: { rating: 6.2, votes: 1189, id: 5 },
  countries: [ 'USA' ],
  rated: 'UNRATED',
  genres: [ 'Short' ],
  tomatoes:
   { viewer: { rating: 3, numReviews: 184, meter: 32 },
     lastUpdated: 2015-06-28T18:34:09.000Z },
  num_mflix_comments: 1,
  comments:
   [ { name: 'Taylor Alvarez',
       email: 'taylor_alvarez@fakegmail.com',
       movie_id: ObjectId("573a1390f29313caabcd4135"),
       text: 'Non dignissimos quo vero facere. Nihil incidunt nemo aliquam rem magnam vero. Deleniti nemo quidem ipsam id.',
       date: 1970-03-27T17:15:30.000Z } ] }


// パイプラインのクエリは長いのでいったん変数に
// まず　$matchステージで、オスカー受賞しているデータに絞る
// $groupステージで、$imdb.ratingに対して、標準偏差、max, min, avgオペレータで値をとる
// '_id': null とすると、全データを対象に計算する
pipline = [
  {
    '$match': {
      '$and': [
        {
          'awards': {
            '$exists': true
          }
        }, {
          'awards': {
            '$regex': new RegExp('Won [0-9]{1,2} Oscar')
          }
        }, {
          'imdb.rating': {
            '$gte': 1
          }
        }, {
          'imdb.votes': {
            '$gte': 1
          }
        }
      ]
    }
  }, {
    '$group': {
      '_id': null,
      'highest_rating': {
        '$max': '$imdb.rating'
      },
      'lowest_rating': {
        '$min': '$imdb.rating'
      },
      'avg_rate': {
        '$avg': '$imdb.rating'
      },
      'dev': {
        '$stdDevSamp': '$imdb.rating'
      }
    }
  }, {
    '$addFields': {
      'average_rating': {
        '$trunc': [
          '$avg_rate', 4
        ]
      },
      'deviation': {
        '$trunc': [
          '$dev', 4
        ]
      }
    }
  }
]

// 実行！
MongoDB Enterprise Cluster0-shard-0:PRIMARY> db.movies.aggregate(pipline).pretty()
{
 "_id" : null,
 "highest_rating" : 9.2,
 "lowest_rating" : 4.5,
 "avg_rate" : 7.527024070021882,
 "dev" : 0.5988145513344498,
 "average_rating" : 7.527,
 "deviation" : 0.5988
}

これでOKでした！
ちなみに、パターンマッチはこれでも良いとのこと。

// Oscars? なので、直前の"s" が0以上1つある、という条件
{
  $match: {
    awards: /Won \d{1,2} Oscars?/
  }
}

`$unwind`

$unwind は、ドキュメント中の配列のフィールドを、1行ずつに展開してくれるオペレータ。
データをバラして集計したい場合、グループオペレータを使う場合によく利用します。

データに値が無い場合は、[] （空）に置き換わり、そこから展開されます。

// 以下、動きのみ確認

// まずタイトルとジャンル（配列）のフィールドのみ、3件抽出
pipline　= [
  { '$project':
    { _id: 1, title: 1, genres: 1 }
  },
  { '$limit': 3 }
]

db.movies.aggregate(pipline)
{ _id: ObjectId("573a1390f29313caabcd4192"),
  title: 'The Conjuring of a Woman at the House of Robert Houdin',
  genres: [ 'Short' ] }
{ _id: ObjectId("573a1390f29313caabcd414a"),
  title: 'Baby\'s Dinner',
  genres: [ 'Documentary', 'Short' ] }
{ _id: ObjectId("573a1390f29313caabcd41a9"),
  title: 'A Terrible Night',
  genres: [ 'Short', 'Comedy' ] }

// piplineの条件に、$unwindを追加
pipline.push({ $unwind: "$genres" })
3

// 中身を確認
pipline
[
  { '$project': { _id: 1, title: 1, genres: 1 } },
  { '$limit': 3 },
  { '$unwind': '$genres' }
]

// 実行：ジャンルフィールドが展開されるため、3件の出力から、さらに5件になっている
db.movies.aggregate(pipline)
{ _id: ObjectId("573a1390f29313caabcd4192"),
  title: 'The Conjuring of a Woman at the House of Robert Houdin',
  genres: 'Short' }
{ _id: ObjectId("573a1390f29313caabcd414a"),
  title: 'Baby\'s Dinner',
  genres: 'Documentary' }
{ _id: ObjectId("573a1390f29313caabcd414a"),
  title: 'Baby\'s Dinner',
  genres: 'Short' }
{ _id: ObjectId("573a1390f29313caabcd41a9"),
  title: 'A Terrible Night',
  genres: 'Short' }
{ _id: ObjectId("573a1390f29313caabcd41a9"),
  title: 'A Terrible Night',
  genres: 'Comedy' }

Lab - $unwind

Problem:

Let's use our increasing knowledge of the Aggregation Framework to
explore our movies collection in more detail. We'd like to calculate
how many movies every cast member has been in and get an average
imdb.rating for each cast member.

What is the name, number of movies, and average rating (truncated to one
decimal) for the cast member that has been in the most number of movies
with English as an available language?

{ "_id": "First Last", "numFilms": 1, "average": 1.1 }

- 言語が英語であること
- キャストごとに出演した映画の数と、平均のレートを算出

この場合の回答。

pipline = [{$match: {
  $and: [
    { languages: { $in: [ "English" ] } }
  ]
}}, {$unwind: {
  path: "$cast"
}}, {$group: {
  "_id": "$cast",
  "numFilms": { $sum: 1 },
  "average": {
    $avg: "$imdb.rating"
  }
}}, {$addFields: {
　"average": { $trunc: ["$average", 2 ] }
}}, {$sort: {
  numFilms: -1
}}, {$limit: 1}]

// こたえ
MongoDB Enterprise Cluster0-shard-0:PRIMARY> db.movies.aggregate(pipline).pretty()
{ "_id" : "John Wayne", "numFilms" : 107, "average" : 6.42 }

Lab - Using $lookup

Problem:

Which alliance from air_alliances flies the most routes with either a
Boeing 747 or an Airbus A380 (abbreviated 747 and 380 in air_routes)?

air_alliancesコレクションの中で、Boeing 747 あるいは an Airbus A380が運行する
ルートが一番多いのは、どのアライアンスでしょうか？

この場合の回答。


// サンプルデータを確認
db.air_alliances.findOne()
{ _id: ObjectId("5980bef9a39d0ba3c650ae9b"),
  name: 'Star Alliance',
  airlines:
   [ 'Air Canada',
     'Adria Airways',
     .....
     'EVA Air',
     'South African Airways',
     'Singapore Airlines' ] }

db.air_routes.findOne()
{ _id: ObjectId("56e9b39b732b6122f877fa96"),
  airline: { id: 470, name: 'Air Burkina', alias: '2J', iata: 'VBW' },
  src_airport: 'OUA',
  dst_airport: 'LFW',
  codeshare: '',
  stops: 0,
  airplane: 'CRJ' }

// Boeing 747 or an Airbus A380 を抽出（試しに1件）
num = 1
pipline = [
  { $match:
    { airplane: /747|380/ }
  },
  {
    $limit: num
  }
]

db.air_routes.aggregate(pipline)
{ _id: ObjectId("56e9b39b732b6122f8780d2d"),
  airline: { id: 24, name: 'American Airlines', alias: 'AA', iata: 'AAL' },
  src_airport: 'CDG',
  dst_airport: 'KUL',
  codeshare: '',
  stops: 0,
  airplane: '380' }


// Airlineが属するアライアンスを取得する
// $lookupを使う
num = 5
pipline = [
  { $match:
    { airplane: /747|380/ }
  },
  {
    $project: {
      _id: 1,
      "airline.name": 1,
      airplane: 1
    }
  },
  {
    $limit: num
  }
]

db.air_routes.aggregate(pipline)
{ _id: ObjectId("56e9b39b732b6122f8780d2d"),
  airline: { name: 'American Airlines' },
  airplane: '380' }
{ _id: ObjectId("56e9b39b732b6122f878108a"),
  airline: { name: 'American Airlines' },
  airplane: '380' }
{ _id: ObjectId("56e9b39b732b6122f8781053"),
  airline: { name: 'American Airlines' },
  airplane: '380' }
{ _id: ObjectId("56e9b39b732b6122f8781046"),
  airline: { name: 'American Airlines' },
  airplane: '380 744' }
{ _id: ObjectId("56e9b39b732b6122f8781057"),
  airline: { name: 'American Airlines' },
  airplane: '380' }


pipline = [
  {
    $match: {
      airplane: /747|380/
    }
  },
  {
    $lookup: {
      from: 'air_alliances',
      localField: 'airline.name',
      foreignField: 'airlines',
      as: 'alliance'
    }
  },
  {
    $unwind: "$alliance"
  },
  {
    $group: {
      _id: "$alliance.name",
      count: { $sum: 1 }
    }
  }
]

// 集計結果
db.air_routes.aggregate(pipline).pretty()
{ _id: 'SkyTeam', count: 16 }
{ _id: 'Star Alliance', count: 11 }
{ _id: 'OneWorld', count: 15 }

一番多いのは　SkyTeam でした！

`$graphLookup` Introduction

ソーシャルマップや組織図、空港の路線図のような、グラグとして表現されるようなデータの抽出に利用。

Provides MongoDB with graph or graph-like capabilities
$graphLookup provides MongoDB a transitive closure implementation
Ref. https://docs.mongodb.com/manual/reference/operator/aggregation/graphLookup/

今回のサンプルデータ。
{ _id: 1, name: 'Dev', title: 'CEO' } がトップで、他のノードはすべてレポートライン（reports_to) を持っています。

db.parent_reference.find()
{ _id: 9, name: 'Shannon', title: 'VP Education', reports_to: 5 }
{ _id: 1, name: 'Dev', title: 'CEO' }
{ _id: 7, name: 'Elyse', title: 'COO', reports_to: 2 }
{ _id: 6, name: 'Ron', title: 'VP PM', reports_to: 2 }
{ _id: 4, name: 'Carlos', title: 'CRO', reports_to: 1 }
{ _id: 5, name: 'Andrew', title: 'VP Eng', reports_to: 2 }
{ _id: 3, name: 'Meagen', title: 'CMO', reports_to: 1 }
{ _id: 10,
  name: 'Dan',
  title: 'VP Core Engineering',
  reports_to: 5 }
{ _id: 2, name: 'Eliot', title: 'CTO', reports_to: 1 }
{ _id: 11,
  name: 'Cailin',
  title: 'VP Cloud Engineering',
  reports_to: 5 }
{ _id: 8, name: 'Richard', title: 'VP PS', reports_to: 1 }

直接の親に対しての情報を持つツリー構造。

// topのノード
db.parent_reference.find({_id: 1})
{ _id: 1, name: 'Dev', title: 'CEO' }

// CEOに直にレポートするのは以下で抽出
db.parent_reference.find({reports_to: 1})
{ _id: 4, name: 'Carlos', title: 'CRO', reports_to: 1 }
{ _id: 3, name: 'Meagen', title: 'CMO', reports_to: 1 }
{ _id: 2, name: 'Eliot', title: 'CTO', reports_to: 1 }
{ _id: 8, name: 'Richard', title: 'VP PS', reports_to: 1 }

// ここまでは簡単。全員を取得するには？？

$graphLookup

Performs a recursive search on a collection, with options for restricting the search by recursion depth and query filter.
再帰的な検索をするよ！深さを指定したりクエリでフィルタして検索できるよ！

// aggregation stageで指定する場合のサンプル
{
   $graphLookup: {
      from: <collection（コレクションの指定）>,
      startWith: <expression>,
      connectFromField: <string>,
      connectToField: <string>,
      as: <string>,
      maxDepth: <number>,
      depthField: <string>,
      restrictSearchWithMatch: <document>
   }
}

// 今回の場合、reports_toフィールドに親の_idが設定されている
stage = {
   $graphLookup: {
      from: "parent_reference",
      startWith: "$reports_to",
      connectFromField: "reports_to",
      connectToField: "_id",  // connectFromFieldがどのフィールドを参照しているか指定
      as: "boss"　// reportingHierarchyにそのノード（データ）のすべての親が入る
   }
}

// まずは全部抽出
// それぞれのノードから見て、ボスがどれだけいるか
db.parent_reference.aggregate([stage])
{ _id: 9,
  name: 'Shannon',
  title: 'VP Education',
  reports_to: 5,
  boss:
   [ { _id: 1, name: 'Dev', title: 'CEO' },
     { _id: 2, name: 'Eliot', title: 'CTO', reports_to: 1 },
     { _id: 5, name: 'Andrew', title: 'VP Eng', reports_to: 2 } ] }
{ _id: 1, name: 'Dev', title: 'CEO', boss: [] }
{ _id: 7,
  name: 'Elyse',
  title: 'COO',
  reports_to: 2,
  boss:
   [ { _id: 1, name: 'Dev', title: 'CEO' },
     { _id: 2, name: 'Eliot', title: 'CTO', reports_to: 1 } ] }
{ _id: 6,
  name: 'Ron',
  title: 'VP PM',
  reports_to: 2,
  boss:
   [ { _id: 1, name: 'Dev', title: 'CEO' },
     { _id: 2, name: 'Eliot', title: 'CTO', reports_to: 1 } ] }
{ _id: 4,
  name: 'Carlos',
  title: 'CRO',
  reports_to: 1,
  boss: [ { _id: 1, name: 'Dev', title: 'CEO' } ] }
{ _id: 5,
  name: 'Andrew',
  title: 'VP Eng',
  reports_to: 2,
  boss:
   [ { _id: 1, name: 'Dev', title: 'CEO' },
     { _id: 2, name: 'Eliot', title: 'CTO', reports_to: 1 } ] }
{ _id: 3,
  name: 'Meagen',
  title: 'CMO',
  reports_to: 1,
  boss: [ { _id: 1, name: 'Dev', title: 'CEO' } ] }
{ _id: 10,
  name: 'Dan',
  title: 'VP Core Engineering',
  reports_to: 5,
  boss:
   [ { _id: 1, name: 'Dev', title: 'CEO' },
     { _id: 2, name: 'Eliot', title: 'CTO', reports_to: 1 },
     { _id: 5, name: 'Andrew', title: 'VP Eng', reports_to: 2 } ] }
{ _id: 2,
  name: 'Eliot',
  title: 'CTO',
  reports_to: 1,
  boss: [ { _id: 1, name: 'Dev', title: 'CEO' } ] }
{ _id: 11,
  name: 'Cailin',
  title: 'VP Cloud Engineering',
  reports_to: 5,
  boss:
   [ { _id: 1, name: 'Dev', title: 'CEO' },
     { _id: 2, name: 'Eliot', title: 'CTO', reports_to: 1 },
     { _id: 5, name: 'Andrew', title: 'VP Eng', reports_to: 2 } ] }
{ _id: 8,
  name: 'Richard',
  title: 'VP PS',
  reports_to: 1,
  boss: [ { _id: 1, name: 'Dev', title: 'CEO' } ] }

// top (_id: 1) から表示してみる（自分に「親」がいるかどうか）
{ _id: 1, name: 'Dev', title: 'CEO', boss: [] }
{ _id: 2,
  name: 'Eliot',
  title: 'CTO',
  reports_to: 1,
  boss: [ { _id: 1, name: 'Dev', title: 'CEO' } ] }
{ _id: 3,
  name: 'Meagen',
  title: 'CMO',
  reports_to: 1,
  boss: [ { _id: 1, name: 'Dev', title: 'CEO' } ] }
{ _id: 4,
  name: 'Carlos',
  title: 'CRO',
  reports_to: 1,
  boss: [ { _id: 1, name: 'Dev', title: 'CEO' } ] }
{ _id: 5,
  name: 'Andrew',
  title: 'VP Eng',
  reports_to: 2,
  boss:
   [ { _id: 1, name: 'Dev', title: 'CEO' },
     { _id: 2, name: 'Eliot', title: 'CTO', reports_to: 1 } ] }
{ _id: 6,
  name: 'Ron',
  title: 'VP PM',
  reports_to: 2,
  boss:
   [ { _id: 1, name: 'Dev', title: 'CEO' },
     { _id: 2, name: 'Eliot', title: 'CTO', reports_to: 1 } ] }
{ _id: 7,
  name: 'Elyse',
  title: 'COO',
  reports_to: 2,
  boss:
   [ { _id: 1, name: 'Dev', title: 'CEO' },
     { _id: 2, name: 'Eliot', title: 'CTO', reports_to: 1 } ] }
{ _id: 8,
  name: 'Richard',
  title: 'VP PS',
  reports_to: 1,
  boss: [ { _id: 1, name: 'Dev', title: 'CEO' } ] }
{ _id: 9,
  name: 'Shannon',
  title: 'VP Education',
  reports_to: 5,
  boss:
   [ { _id: 5, name: 'Andrew', title: 'VP Eng', reports_to: 2 },
     { _id: 1, name: 'Dev', title: 'CEO' },
     { _id: 2, name: 'Eliot', title: 'CTO', reports_to: 1 } ] }
{ _id: 10,
  name: 'Dan',
  title: 'VP Core Engineering',
  reports_to: 5,
  boss:
   [ { _id: 5, name: 'Andrew', title: 'VP Eng', reports_to: 2 },
     { _id: 1, name: 'Dev', title: 'CEO' },
     { _id: 2, name: 'Eliot', title: 'CTO', reports_to: 1 } ] }
{ _id: 11,
  name: 'Cailin',
  title: 'VP Cloud Engineering',
  reports_to: 5,
  boss:
   [ { _id: 5, name: 'Andrew', title: 'VP Eng', reports_to: 2 },
     { _id: 1, name: 'Dev', title: 'CEO' },
     { _id: 2, name: 'Eliot', title: 'CTO', reports_to: 1 } ] }


// reports_to -> _id ではなく、_id -> reports_to にしてみる
stage = {
   $graphLookup: {
      from: "parent_reference",
      startWith: "$_id",
      connectFromField: "_id",
      connectToField: "reports_to",  // connectFromFieldがどのフィールドを参照しているか指定
      as: "staff"　// staffにノード（データ）のすべての部下が入る
   }
}

// このパターンだと、自分から見て「子」がいるかどうか。こちらがよさそう！
{ _id: 9,
  name: 'Shannon',
  title: 'VP Education',
  reports_to: 5,
  staff: [] }
{ _id: 1,
  name: 'Dev',
  title: 'CEO',
  staff:
   [ { _id: 7, name: 'Elyse', title: 'COO', reports_to: 2 },
     { _id: 2, name: 'Eliot', title: 'CTO', reports_to: 1 },
     { _id: 6, name: 'Ron', title: 'VP PM', reports_to: 2 },
     { _id: 4, name: 'Carlos', title: 'CRO', reports_to: 1 },
     { _id: 9, name: 'Shannon', title: 'VP Education', reports_to: 5 },
     { _id: 3, name: 'Meagen', title: 'CMO', reports_to: 1 },
     { _id: 5, name: 'Andrew', title: 'VP Eng', reports_to: 2 },
     { _id: 8, name: 'Richard', title: 'VP PS', reports_to: 1 },
     { _id: 11,
       name: 'Cailin',
       title: 'VP Cloud Engineering',
       reports_to: 5 },
     { _id: 10,
       name: 'Dan',
       title: 'VP Core Engineering',
       reports_to: 5 } ] }
{ _id: 7, name: 'Elyse', title: 'COO', reports_to: 2, staff: [] }
{ _id: 6, name: 'Ron', title: 'VP PM', reports_to: 2, staff: [] }
{ _id: 4, name: 'Carlos', title: 'CRO', reports_to: 1, staff: [] }
{ _id: 5,
  name: 'Andrew',
  title: 'VP Eng',
  reports_to: 2,
  staff:
   [ { _id: 9, name: 'Shannon', title: 'VP Education', reports_to: 5 },
     { _id: 11,
       name: 'Cailin',
       title: 'VP Cloud Engineering',
       reports_to: 5 },
     { _id: 10,
       name: 'Dan',
       title: 'VP Core Engineering',
       reports_to: 5 } ] }
{ _id: 3, name: 'Meagen', title: 'CMO', reports_to: 1, staff: [] }
{ _id: 10,
  name: 'Dan',
  title: 'VP Core Engineering',
  reports_to: 5,
  staff: [] }
{ _id: 2,
  name: 'Eliot',
  title: 'CTO',
  reports_to: 1,
  staff:
   [ { _id: 7, name: 'Elyse', title: 'COO', reports_to: 2 },
     { _id: 6, name: 'Ron', title: 'VP PM', reports_to: 2 },
     { _id: 9, name: 'Shannon', title: 'VP Education', reports_to: 5 },
     { _id: 5, name: 'Andrew', title: 'VP Eng', reports_to: 2 },
     { _id: 11,
       name: 'Cailin',
       title: 'VP Cloud Engineering',
       reports_to: 5 },
     { _id: 10,
       name: 'Dan',
       title: 'VP Core Engineering',
       reports_to: 5 } ] }
{ _id: 11,
  name: 'Cailin',
  title: 'VP Cloud Engineering',
  reports_to: 5,
  staff: [] }
{ _id: 8,
  name: 'Richard',
  title: 'VP PS',
  reports_to: 1,
  staff: [] }

全部ではなくて、ある役職から下のすべてを取り出す場合は？
aggregationのステージから、起点になるノードを指定すればいい。

こんなイメージです。

condition = [
  {
    $match: { title: "CTO" }
  },
  {
   $graphLookup: {
      from: "parent_reference",
      startWith: "$_id",
      connectFromField: "_id",
      connectToField: "reports_to",
      as: "staff"
   }
 }
]

// CTOに関して、その子階層を抽出し、reportingHierarchyに格納
db.parent_reference.aggregate(condition)
{ _id: 2,
  name: 'Eliot',
  title: 'CTO',
  reports_to: 1,
  reportingHierarchy:
   [ { _id: 11,
       name: 'Cailin',
       title: 'VP Cloud Engineering',
       reports_to: 5 },
     { _id: 9, name: 'Shannon', title: 'VP Education', reports_to: 5 },
     { _id: 7, name: 'Elyse', title: 'COO', reports_to: 2 },
     { _id: 6, name: 'Ron', title: 'VP PM', reports_to: 2 },
     { _id: 10,
       name: 'Dan',
       title: 'VP Core Engineering',
       reports_to: 5 },
     { _id: 5, name: 'Andrew', title: 'VP Eng', reports_to: 2 } ] }


condition = [
  {
    $match: { title: "CTO" }
  },
  {
   $graphLookup: {
      from: "parent_reference",
      startWith: "$_id",
      connectFromField: "_id",
      connectToField: "reports_to",
      as: "staff"
    }
  },
  {
    $unwind: "$staff"
  }
]

// $unwinすると展開されます
db.parent_reference.aggregate(condition)
{ _id: 2,
  name: 'Eliot',
  title: 'CTO',
  reports_to: 1,
  staff: { _id: 9, name: 'Shannon', title: 'VP Education', reports_to: 5 } }
{ _id: 2,
  name: 'Eliot',
  title: 'CTO',
  reports_to: 1,
  staff: { _id: 7, name: 'Elyse', title: 'COO', reports_to: 2 } }
{ _id: 2,
  name: 'Eliot',
  title: 'CTO',
  reports_to: 1,
  staff: { _id: 5, name: 'Andrew', title: 'VP Eng', reports_to: 2 } }
{ _id: 2,
  name: 'Eliot',
  title: 'CTO',
  reports_to: 1,
  staff:
   { _id: 11,
     name: 'Cailin',
     title: 'VP Cloud Engineering',
     reports_to: 5 } }
{ _id: 2,
  name: 'Eliot',
  title: 'CTO',
  reports_to: 1,
  staff: { _id: 6, name: 'Ron', title: 'VP PM', reports_to: 2 } }
{ _id: 2,
  name: 'Eliot',
  title: 'CTO',
  reports_to: 1,
  staff:
   { _id: 10,
     name: 'Dan',
     title: 'VP Core Engineering',
     reports_to: 5 } }

Quiz: `$graphLookup`: Simple Lookup

Problem:
Which of the following statements is/are correct? Check all that apply.

こたえ

connectFromField value will be use to match connectToField in a recursive match
- connectFromField で指定したフィールドの値がconnectToFieldで指定したフィールドの値にマッチするかで検索します
connectToField will be used on recursive find operations
- connectToField は、再帰検索に利用されます

$graphLookup: maxDepth and depthField

グラフ探索で、深さを指定した探索や、どのフィールドを深さの基準にするかを指定することができます。


// CTOは2段階だったので、CTO直属の部下を探します
condition = [
  {
    $match: { title: "CTO" }
  },
  {
   $graphLookup: {
      from: "parent_reference",
      startWith: "$_id",
      connectFromField: "_id",
      connectToField: "reports_to",
      as: "staff",
      maxDepth: 0,
      depthField: "level"
   }
 }
]

// CTO直下の部下だけ抽出
// maxDepth: 0 だと、再帰を行わないので、直下のみ
db.parent_reference.aggregate(condition)
{ _id: 2,
  name: 'Eliot',
  title: 'CTO',
  reports_to: 1,
  staff:
   [ { _id: 7, name: 'Elyse', title: 'COO', reports_to: 2, level: 0 },
     { _id: 6, name: 'Ron', title: 'VP PM', reports_to: 2, level: 0 },
     { _id: 5,
       name: 'Andrew',
       title: 'VP Eng',
       reports_to: 2,
       level: 0 } ] }

maxDepth

Ref: https://docs.mongodb.com/manual/reference/operator/aggregation/graphLookup/#max-depth
Setting the maxDepth field to 0 is equivalent to a non-recursive $graphLookup search stage. (0だと再帰しません)

depthField

再帰検索した際に、どの深さで検出されたかを格納します

Quiz: `$graphLookup`: maxDepth and depthField

Problem:
Which of the following statements is/are correct? Check all that apply.

こたえ

maxDepth only takes $long values
depthField determines a field, which contains value of the number of documents matched by the recursive lookup
- 指定したフィールド名で、再起検索でヒットした階層の数をセットします

Lab: $graphLookup

この章の練習問題。

Determine the approach that satisfies the following question in the most efficient manner:

Find the list of all possible distinct destinations, with at most one layover, departing from the base airports of airlines from Germany, Spain or Canada that are part of the "OneWorld" alliance. Include both the destination and which airline services that location. As a small hint, you should find 158 destinations.

- OneWorldアライアンスに属する航空会社
- 少なくとも一回は乗り継ぎがある (layover)
- 起点はGermany, Spain or Canada
- 発着点の双方をリストアップしてください
- ヒントは158件であること

むずかしい....。今回は答えから逆に学んでいくパターンになりました。

また、今回も MongoDB Compass と aggregationを使いながらデータを確認していきます。


// まずは答え
db.air_alliances.aggregate([{
  $match: { name: "OneWorld" }
}, {
  $graphLookup: {
    startWith: "$airlines",
    from: "air_airlines",
    connectFromField: "name",
    connectToField: "name",
    as: "airlines",
    maxDepth: 0,
    restrictSearchWithMatch: {
      country: { $in: ["Germany", "Spain", "Canada"] }
    }
  }
}, {
  $graphLookup: {
    startWith: "$airlines.base",
    from: "air_routes",
    connectFromField: "dst_airport",
    connectToField: "src_airport",
    as: "connections",
    maxDepth: 1
  }
}, {
  $project: {
    validAirlines: "$airlines.name",
    "connections.dst_airport": 1,
    "connections.airline.name": 1
  }
},
{ $unwind: "$connections" },
{
  $project: {
    isValid: { $in: ["$connections.airline.name", "$validAirlines"] },
    "connections.dst_airport": 1
  }
},
{ $match: { isValid: true } },
{ $group: { _id: "$connections.dst_airport" } },
{ $count: 'id' }　// カウント条件を足すと158件
])

{ id: 158 }

正解がわかったところで、アグリゲーションの各ステージから答え合わせしていきます。

// まず　air_alliances を起点に、アライアンス名を指定
condition = [
  { $match: {
        "name": "OneWorld"
    }
  }
]

db.air_alliances.aggregate(condition)
// 1件のみ返ります。
// このアライアンスに属する航空会社のデータはネストしています。
{ _id: ObjectId("5980bef9a39d0ba3c650ae9d"),
  name: 'OneWorld',
  airlines:
   [ 'Air Berlin',
     'American Airlines',
     'British Airways',
     'Cathay Pacific',
     'Finnair',
     'Iberia Airlines',
     'Japan Airlines',
     'LATAM Chile',
     'LATAM Brasil',
     'Malaysia Airlines',
     'Canadian Airlines',
     'Qantas',
     'Qatar Airways',
     'Royal Jordanian',
     'SriLankan Airlines',
     'S7 Airlines' ] }

次に、航空会社（airlinesコレクション）とジョインさせます。
条件は、国籍が["Germany", "Spain", "Canada"]のいずれか。
$graphLookup ステージになります。


// 上記で抽出したデータのairlinesフィールドと、air_airlinesを使って再帰させる
condition.push(
{
    $graphLookup: {
        startWith: "$airlines",
        from: "air_airlines",
        connectFromField: "name",
        connectToField: "name",
        as: "airlines",
        maxDepth: 0,
        restrictSearchWithMatch: {
            country: {
                $in: ["Germany", "Spain", "Canada"]
            }
        }
    }
}
)

// 国籍が["Germany", "Spain", "Canada"]の航空会社と関連付け
// そんなに多くありません
db.air_alliances.aggregate(condition)
{ _id: ObjectId("5980bef9a39d0ba3c650ae9d"),
  name: 'OneWorld',
  airlines:
   [ { _id: ObjectId("56e9b497732b6122f87908cd"),
       airline: 1615,
       name: 'Canadian Airlines',
       alias: 'CP',
       iata: 'CDN',
       icao: 'CANADIAN',
       active: 'Y',
       country: 'Canada',
       base: 'LVI' },
     { _id: ObjectId("56e9b497732b6122f8790355"),
       airline: 214,
       name: 'Air Berlin',
       alias: 'AB',
       iata: 'BER',
       icao: 'AIR BERLIN',
       active: 'Y',
       country: 'Germany',
       base: 'KTE' },
     { _id: ObjectId("56e9b497732b6122f8790d83"),
       airline: 2822,
       name: 'Iberia Airlines',
       alias: 'IB',
       iata: 'IBE',
       icao: 'IBERIA',
       active: 'Y',
       country: 'Spain',
       base: 'BRN' } ] }

つぎの条件です。dst_airporとsrc_airportがあるもの。
maxDepth = 1 なので、孫階層（乗り継ぎが1回）あるものになります。

// maxDepth: 1 にすると、子階層だけでなく孫階層なので、路線の組み合わせがとても多くなります！（1000以上！）
condition.push({
  $graphLookup: {
    startWith: "$airlines.base",
    from: "air_routes",
    connectFromField: "dst_airport",
    connectToField: "src_airport",
    as: "connections",
    maxDepth: 1
  }
}
)

// いったんmaxDepth: 0 にした場合は、ベースの空港初の単一路線のみ
// airlines.base -> connections.airline.src_airport に対応
// maxDepth: 1にすると、さらに　connections.airline.src_airport -> connections.airline.dst_airport で再帰していきます
db.air_alliances.aggregate(condition)
{ _id: ObjectId("5980bef9a39d0ba3c650ae9d"),
  name: 'OneWorld',
  airlines:
   [ { _id: ObjectId("56e9b497732b6122f8790d83"),
       airline: 2822,
       name: 'Iberia Airlines',
       alias: 'IB',
       iata: 'IBE',
       icao: 'IBERIA',
       active: 'Y',
       country: 'Spain',
       base: 'BRN' },
     { _id: ObjectId("56e9b497732b6122f87908cd"),
       airline: 1615,
       name: 'Canadian Airlines',
       alias: 'CP',
       iata: 'CDN',
       icao: 'CANADIAN',
       active: 'Y',
       country: 'Canada',
       base: 'LVI' },
     { _id: ObjectId("56e9b497732b6122f8790355"),
       airline: 214,
       name: 'Air Berlin',
       alias: 'AB',
       iata: 'BER',
       icao: 'AIR BERLIN',
       active: 'Y',
       country: 'Germany',
       base: 'KTE' } ],
  connections:
   [ { _id: ObjectId("56e9b39c732b6122f87898d3"),
       airline: { id: 3378, name: 'Malaysia Airlines', alias: 'MH', iata: 'MAS' },
       src_airport: 'KTE',
       dst_airport: 'SZB',
       codeshare: 'Y',
       stops: 0,
       airplane: 'AT7' },
       .... [ 略 ] ....
     { _id: ObjectId("56e9b39b732b6122f87833fa"),
       airline: { id: 1355, name: 'British Airways', alias: 'BA', iata: 'BAW' },
       src_airport: 'LVI',
       dst_airport: 'JNB',
       codeshare: 'Y',
       stops: 0,
       airplane: '734' } ] }

// 実際はもっとたくさん！

次に必要なフィールドだけに絞り込み。

condition.push(
{
    $project: {
        validAirlines: "$airlines.name",
        "connections.dst_airport": 1,
        "connections.airline.name": 1
    }
}
)

// _id は、alliancesのなかの　OneWorld　のものになります
db.air_alliances.aggregate(condition)
{ _id: ObjectId("5980bef9a39d0ba3c650ae9d"),
  connections:
   [ { airline: { name: 'Iberia Airlines' }, dst_airport: 'AMM' },
     { airline: { name: 'SkyWork Airlines ' }, dst_airport: 'EBA' },
     { airline: { name: 'Air Berlin' }, dst_airport: 'RIX' },
     { airline: { name: 'Air Berlin' }, dst_airport: 'RHO' },
     { airline: { name: 'Ryanair' }, dst_airport: 'BLQ' },
     { airline: { name: 'Delta Air Lines' }, dst_airport: 'BOS' },
 ..... [ 略 ] ....
}

connectionsはネストしているので、$unwind　で行ごとに展開します。
それから、路線の組み合わせとして、ただしくないもの（！）があるので、路線と航空会社名の組み合わせが一致するかどうかのフィールドを設定します。


condition.push(
{ $unwind: "$connections" },
{
  $project: {
    isValid: {
      $in: ["$connections.airline.name", "$validAirlines"]
    },
    "connections.dst_airport": 1
  }
}
)

// こんな結果に
db.air_alliances.aggregate(condition)
{ _id: ObjectId("5980bef9a39d0ba3c650ae9d"),
  connections: { dst_airport: 'OLB' },
  isValid: true }
{ _id: ObjectId("5980bef9a39d0ba3c650ae9d"),
  connections: { dst_airport: 'ARN' },
  isValid: false }
{ _id: ObjectId("5980bef9a39d0ba3c650ae9d"),
  connections: { dst_airport: 'BIO' },
  isValid: true }
 ..... [ 略 ] ....

最後に、路線と航空会社名が一致するものだけを取り出します。


condition.push(
{ $match: { isValid: true } },
{
  $group: {
    _id: "$connections.dst_airport"
  }
}
)

// more になったので、まだ件数としてはあります
db.air_alliances.aggregate(condition)
{ _id: 'RGS' }
{ _id: 'BEG' }
{ _id: 'JTR' }
{ _id: 'AMS' }
{ _id: 'PUJ' }
{ _id: 'NAP' }
{ _id: 'LGW' }
{ _id: 'SCN' }
{ _id: 'AAL' }
{ _id: 'PEK' }
{ _id: 'LEI' }
{ _id: 'SCQ' }
{ _id: 'ILD' }
{ _id: 'IEV' }
{ _id: 'PMI' }
{ _id: 'AGP' }
{ _id: 'INN' }
{ _id: 'BGO' }
{ _id: 'FLR' }
{ _id: 'FNC' }
Type "it" for more

// あっているか件数を確認してみます
condition.push(
  {
    $count: 'results'
  }
)

// 158件なので、よさそうです
db.air_alliances.aggregate(condition)
{ results: 158 }

今回のメモ

aggregation() の学習の続きですが、今回は難しかった！
細切れで進めながら、10日以上かかりました。

$graphLookup はなかなか深いですね...。
また、便利そうに見えますが、いろいろ制約もあるようです。

無理にMQL側で処理せずに、アプリケーション側で加工というのも、もちろんありですね！
今回は階層構造のデータについて扱ったので、これとは別に、Node.jsで階層構造のデータを可視化してみたいと思います。

ここまで進めてみて、わからないながらもパイプラインの組み立てには、なんとか慣れてきました。

また、パイプラインに関しては、

いったん不要なデータも含めて加工
そのあとで精査や組み替え

をして、欲しいデータを作っていくんだなあ、というのを実感しています。

次はChapter4: Core Aggregation になります。まったく何が出るのかわからない....。

ただいまのコース

Chapter 3: Core Aggregation - Combining Information

The $group Stage

Lab - $group and Accumulators

$unwind

Lab - $unwind

Lab - Using $lookup

$graphLookup Introduction

Quiz: $graphLookup: Simple Lookup

$graphLookup: maxDepth and depthField

Quiz: $graphLookup: maxDepth and depthField

Lab: $graphLookup

今回のメモ

Discussion

`$unwind`

`$graphLookup` Introduction

Quiz: `$graphLookup`: Simple Lookup

Quiz: `$graphLookup`: maxDepth and depthField