时间戳

首页 > 解决方案 > 2个集合之间的mongoDB连接

mongodb - 2个集合之间的mongoDB连接

问题描述

我正在尝试将两个加入收藏。首先是电影列表：

[
  {
    "_id": "movie:1",
    "actors": [
      {
        "_id": "artist:15",
        "role": "John Ferguson"
      },
      {
        "_id": "artist:16",
        "role": "Madeleine Elster"
      },
      {
        "_id": "artist:282",
        "role": null
      }
    ],
    "country": "DE",
    "director": {
      "_id": "artist:3"
    },
    "genre": "drama",
    "summary": "Scottie Ferguson, ancien inspecteur de police, est sujet au vertige depuis qu'il a vu mourir son collègue. Elster, son ami, le charge de surveiller sa femme, Madeleine, ayant des tendances suicidaires. Amoureux de la jeune femme Scottie ne remarque pas le piège qui se trame autour de lui et dont il va être la victime... ",
    "title": "Vertigo",
    "year": 1958
  },
  {
    "_id": "movie:2",
    "actors": [
      {
        "_id": "artist:5",
        "role": "Ripley"
      }
    ],
    "country": "USA",
    "director": {
      "_id": "artist:4"
    },
    "genre": "Science-fiction",
    "summary": "Près d'un vaisseau spatial échoué sur une lointaine planète, des Terriens en mission découvrent de bien étranges \"oeufs\". Ils en ramènent un à bord, ignorant qu'ils viennent d'introduire parmi eux un huitième passager particulièrement féroce et meurtrier. ",
    "title": "Alien",
    "year": 1979
  },
  {
    "_id": "movie:5",
    "actors": [
      {
        "_id": "artist:11",
        "role": "Sean Archer/Castor Troy"
      },
      {
        "_id": "artist:12",
        "role": "Castor Troy/Sean Archer"
      }
    ],
    "country": "USA",
    "director": {
      "_id": "artist:10"
    },
    "genre": "Action",
    "summary": "Directeur d'une unité anti-terroriste, Sean Archer recherche Castor Troy, un criminel responsable de la mort de son fils six ans plus tôt. Il parvient à l'arrêter mais apprend que Troy a caché une bombe au Palais des Congrès de Los Angeles. Seul le frère de Troy peut la désamorcer et, pour l'approcher, Archer se fait greffer le visage de Troy. ",
    "title": "Volte/Face",
    "year": 1997
  },
  {
    "_id": "movie:4",
    "actors": [],
    "country": "FR",
    "director": {
      "_id": "artist:9"
    },
    "genre": "drama",
    "summary": null,
    "title": "Sacrifice",
    "year": 1986
  },
  {
    "_id": "movie:3",
    "actors": [
      {
        "_id": "artist:109",
        "role": "Rose DeWitt Bukater"
      },
      {
        "_id": "artist:110",
        "role": "Jack Dawson"
      }
    ],
    "country": "USA",
    "director": {
      "_id": "artist:6"
    },
    "genre": "drama",
    "summary": "Conduite par Brock Lovett, une expédition américaine fouillant l'épave du Titanic remonte à la surface le croquis d'une femme nue. Alertée par les médias la dame en question, Rose DeWitt Bukater, aujourd'hui centenaire, rejoint les lieux du naufrage, d'où elle entreprend de conter le récit de son fascinant, étrange et tragique voyage... ",
    "title": "Titanic",
    "year": 1997
  },
 {
    "_id": "movie:19",
    "actors": [
      {
        "_id": "artist:44",
        "role": "Terminator"
      }
    ],
    "country": "USA",
    "director": {
      "_id": "artist:6"
    },
    "genre": "Science-fiction",
    "summary": "Deux creatures venues du futur debarquent sur terre. L'une d'entre elles, le Terminator, doit eliminer une certaine Sarah Connor, qui doit enfanter celui qui sera le chef d'un groupe de resistants. L'autre, Kyle Reese, est charge par les rebelles de defendre Sarah... ",
    "title": "Terminator",
    "year": 1984
  },
…

第二个系列由每位艺术家创作（艺术家可以是艺术家和/或电影导演）：

[
  {
    "_id": "artist:1",
    "birth_date": "1971",
    "first_name": "Sofia",
    "last_name": "Coppola"
  },
  {
    "_id": "artist:2",
    "birth_date": null,
    "first_name": "Kirsten",
    "last_name": "Dunst"
  },
  {
    "_id": "artist:3",
    "birth_date": "1899",
    "first_name": "Alfred",
    "last_name": "Hitchcock"
  },
  {
    "_id": "artist:4",
    "birth_date": "1937",
    "first_name": "Ridley",
    "last_name": "Scott"
  },
  {
    "_id": "artist:5",
    "birth_date": "1949",
    "first_name": "Sigourney",
    "last_name": "Weaver"
  },
  {
    "_id": "artist:6",
    "birth_date": "1954",
    "first_name": "James",
    "last_name": "Cameron"
  },
  {
    "_id": "artist:7",
    "birth_date": "1973",
    "first_name": "Richard ",
    "last_name": "Fleischer"
  },
  {
    "_id": "artist:8",
    "birth_date": "1923",
    "first_name": "Charlton ",
    "last_name": "Hestone"
  },
  {
    "_id": "artist:9",
    "birth_date": "1932",
    "first_name": "Andrei",
    "last_name": "Tarkovski"
  },

我想知道每个导演拍的电影，大概是这样的：

[{"first_name": "James", "last_name": "Cameron", "films": ["Titanic", "Terminator"]},
 …
]

我提出以下要求：

db.artists.aggregate([
   {$lookup: {from: "jointure",
              localField: "artists._id",
               foreignField : "movies.director",
               as: "films"}
    },
    {$project: {"_id":0,
                "first_name": 1,
                "last_name": 1,
                "films.title" : 1}
    }
]);

结果，不正确（我有导演的所有电影，而不仅仅是导演制作的电影）：

{
    "films": [
      {
        "title": "Sacrifice"
      },
      {
        "title": "Alien"
      },
      {
        "title": "Titanic"
      },
      {
        "title": "Volte/Face"
      },
      {
        "title": "American Beauty"
      },
      {
        "title": "Sleepy Hollow"
      },
      {
        "title": "Vertigo"
      },
      {
        "title": "Piège de cristal"
      },
      {
        "title": "Impitoyable"
      },
      {
        "title": "58 minutes pour vivre"
      },
      {
        "title": "Van Gogh"
      },
      {
        "title": "Seven"
      },
      … 
   ],
    "first_name": "James",
    "last_name": "Cameron"
  },

似乎每个导演都有所有电影。

更多的：

我有一个 map/reduce 解决方案，但我不满意（自 4.2 起已弃用）：

var mapJoin = function() {
if (this._id.indexOf("artist") != -1) {
 this.type="artist";
 emit(this._id, this);
}
else {
 this.type="film";
 delete this.summary;
 delete this.actors;
 emit(this.director._id, this);
}
}

var reduceJoin = function(id, items) {
var director = null, films={result: []}
for (var idx = 0; idx < items.length; idx++) {
 if (items[idx].type=="artist") {
 director = items[idx];
 }
}
for (var idx = 0; idx < items.length; idx++) {
 if (items[idx].type=="film" && director != null) {
 items[idx].director = director;
 films.result.push (items[idx]);
 }
}
return films;
};

db.jointure.mapReduce(mapJoin, reduceJoin, {out: {"inline": 1}});

标签： mongodbjoinaggregation-framework

解决方案

我不确定“关节”应该是什么。

如果你有actors和movies你在该示例中显示的文档的集合，那么一个简单的$lookup就足够了：

db.artists.aggregate([
  {$lookup: {
      from: "movies",
      localField: "_id",
      foreignField: "director._id",
      as: "films"
  }},
  {$project: {
      "_id": 0,
      "first_name": 1,
      "last_name": 1,
      "films.title": 1
  }}
])

如果您只想要至少有一部电影的艺术家，请在$match最后添加一个舞台，例如

  {$match: {
      $expr: {
        $gt: [
          {$size: "$films"},
          0
        ]
      }
  }}

操场

推荐阅读