{"id":1246,"date":"2019-09-03T16:32:10","date_gmt":"2019-09-03T14:32:10","guid":{"rendered":"http:\/\/www.lafabriquedecode.com\/blog\/?p=1246"},"modified":"2019-09-03T16:32:10","modified_gmt":"2019-09-03T14:32:10","slug":"mongodb-lookup-pipeline-agregation","status":"publish","type":"post","link":"http:\/\/www.lafabriquedecode.com\/blog\/2019\/09\/mongodb-lookup-pipeline-agregation\/","title":{"rendered":"MongoDB – lookup et l’agr\u00e9gation"},"content":{"rendered":"

Cette \u00e9tape de recherche (lookup<\/em>) permet d\u2019effectuer l\u2019\u00e9quivalent d\u2019une jointure externe dans un syst\u00e8me de gestion de bases de donn\u00e9es relationnelles (SGBDR) et ne peut op\u00e9rer que sur des collections non shard\u00e9es<\/em> situ\u00e9es dans la m\u00eame base de donn\u00e9es.
\n\"Look<\/a><\/p>\n

Syntaxe de lookup<\/h1>\n
\r\n{\r\n    $lookup:\r\n    {\r\n        from: < collection \u00e0 joindre >,\r\n        localField: < champ dans les documents de la collection de d\u00e9part >,\r\n        foreignField: < champ dans les documents de la collection \u00e0 joindre>,\r\n        as: < nom du tableau qui sera ajout\u00e9 aux documents du jeu de r\u00e9sultat >\r\n    }\r\n}\r\n<\/pre>\n
Dans chacun des documents re\u00e7us en entr\u00e9e, l\u2019\u00e9tape $lookup va rajouter un tableau contenant des donn\u00e9es en provenance de la collection sur laquelle la jointure a \u00e9t\u00e9 requise avant de passer ces documents \u00e0 la prochaine \u00e9tape du pipeline le cas \u00e9ch\u00e9ant. Le terme de jointure n\u2019est pas toujours tr\u00e8s appropri\u00e9 notamment parce qu’il est possible d’effectuer des requ\u00eates d\u00e9corr\u00e9l\u00e9es, c’est \u00e0 dire sans clause d’\u00e9galit\u00e9.<\/p>\n
Afin de voir $lookup en situation, nous allons utiliser trois collections qui stockeront des \u00e9l\u00e8ves, leurs devoirs individuels et les projets sur lesquels ils travaillent \u00e0 plusieurs.<\/p>\n
El\u00e8ves, au travail !<\/h1>\n
Les travaux individuels<\/h2>\n
Commen\u00e7ons par les travaux individuels de nos \u00e9l\u00e8ves, voici les collections impliqu\u00e9es, aucun index n’a \u00e9t\u00e9 mis pour garder les choses les plus simples possibles mais \u00e9videmment qu’il faudra y songer !<\/p>\n
\r\ndb.eleves.insertMany([\r\n    {"nom": "S\u00e9bastien Ferrandez", "code": "NAT123"},\r\n    {"nom": "Evelyne Durand", "code": "NAT125"},\r\n    {"nom": "Christian Ton", "code": "NAT120"},\r\n    {"nom": "Claire Annela", "code": "NAT127"}\r\n])\r\n\r\ndb.devoirs.insertMany([\r\n    {"code": "NAT123", "matiere": "SVT", "note": 12},\r\n    {"code": "NAT123", "matiere": "Maths", "note": 10},\r\n    {"code": "NAT125", "matiere": "Maths", "note": 11.75},\r\n    {"code": "NAT120", "matiere": "Fran\u00e7ais", "note": 18},\r\n    {"code": "NAT127", "matiere": "Latin", "note": 19}\r\n])\r\n<\/pre>\n
Nous allons partir d’eleves pour aller vers devoirs; dans les deux cas notre nom de champ de \u00ab\u00a0jointure\u00a0\u00bb est code:<\/p>\n
\r\ndb.eleves.aggregate([\r\n   {\r\n     $lookup: {\r\n         "from": "devoirs",\r\n         "localField": "code",\r\n         "foreignField": "code",\r\n         "as": "detail_notes"\r\n     }\r\n   }\r\n])\r\n<\/pre>\n
Voici le r\u00e9sultat produit par ce pipeline, il est assez difficilement lisible mais nous avons l’id\u00e9e g\u00e9n\u00e9rale : les notes sont \u00ab\u00a0raccroch\u00e9es\u00a0\u00bb \u00e0 l’\u00e9l\u00e8ve !<\/p>\n
\r\n{\r\n\t"_id": ObjectId("5d6e6115d9a18feb0291b605"),\r\n\t"nom": "S\u00e9bastien Ferrandez",\r\n\t"code": "NAT123",\r\n\t"detail_notes": [{\r\n\t\t"_id": ObjectId("5d6e6174d9a18feb0291b60e"),\r\n\t\t"code": "NAT123",\r\n\t\t"matiere": "SVT",\r\n\t\t"note": 12\r\n\t}, {\r\n\t\t"_id": ObjectId("5d6e6174d9a18feb0291b60f"),\r\n\t\t"code": "NAT123",\r\n\t\t"matiere": "Maths",\r\n\t\t"note": 10\r\n\t}]\r\n} {\r\n\t"_id": ObjectId("5d6e6115d9a18feb0291b606"),\r\n\t"nom": "Evelyne Durand",\r\n\t"code": "NAT125",\r\n\t"detail_notes": [{\r\n\t\t"_id": ObjectId("5d6e6174d9a18feb0291b610"),\r\n\t\t"code": "NAT125",\r\n\t\t"matiere": "Maths",\r\n\t\t"note": 11.75\r\n\t}]\r\n} {\r\n\t"_id": ObjectId("5d6e6115d9a18feb0291b607"),\r\n\t"nom": "Christian Ton",\r\n\t"code": "NAT120",\r\n\t"detail_notes": [{\r\n\t\t"_id": ObjectId("5d6e6174d9a18feb0291b611"),\r\n\t\t"code": "NAT120",\r\n\t\t"matiere": "Fran\u00e7ais",\r\n\t\t"note": 18\r\n\t}]\r\n} {\r\n\t"_id": ObjectId("5d6e6115d9a18feb0291b608"),\r\n\t"nom": "Claire Annela",\r\n\t"code": "NAT127",\r\n\t"detail_notes": [{\r\n\t\t"_id": ObjectId("5d6e6174d9a18feb0291b612"),\r\n\t\t"code": "NAT127",\r\n\t\t"matiere": "Latin",\r\n\t\t"note": 19\r\n\t}]\r\n}\r\n<\/pre>\n
Ajoutons une \u00e9tape project<\/em> pour rendre cet affichage plus \u00ab\u00a0digeste\u00a0\u00bb…Nous \u00e9liminons les id et les champs redondants:<\/p>\n
\r\ndb.eleves.aggregate([\r\n   {\r\n     $lookup: {\r\n         "from": "devoirs",\r\n         "localField": "code",\r\n         "foreignField": "code",\r\n         "as": "detail_notes"\r\n     }\r\n   },\r\n   {\r\n     $project: {\r\n         "_id": 0,\r\n         "detail_notes._id": 0,\r\n         "detail_notes.code": 0\r\n     }\r\n   }\r\n])\r\n<\/pre>\n
Voil\u00e0 qui est nettement mieux:<\/p>\n
\r\n{\r\n\t"nom": "S\u00e9bastien Ferrandez",\r\n\t"code": "NAT123",\r\n\t"detail_notes": [{\r\n\t\t"matiere": "SVT",\r\n\t\t"note": 12\r\n\t}, {\r\n\t\t"matiere": "Maths",\r\n\t\t"note": 10\r\n\t}]\r\n} {\r\n\t"nom": "Evelyne Durand",\r\n\t"code": "NAT125",\r\n\t"detail_notes": [{\r\n\t\t"matiere": "Maths",\r\n\t\t"note": 11.75\r\n\t}]\r\n} {\r\n\t"nom": "Christian Ton",\r\n\t"code": "NAT120",\r\n\t"detail_notes": [{\r\n\t\t"matiere": "Fran\u00e7ais",\r\n\t\t"note": 18\r\n\t}]\r\n} {\r\n\t"nom": "Claire Annela",\r\n\t"code": "NAT127",\r\n\t"detail_notes": [{\r\n\t\t"matiere": "Latin",\r\n\t\t"note": 19\r\n\t}]\r\n}\r\n<\/pre>\n
Les projets<\/h2>\n
Voici notre collection projets, elle contient les codes des \u00e9l\u00e8ves impliqu\u00e9s ainsi que la note obtenue:<\/p>\n
\r\ndb.projets.insertMany([\r\n    {"codes": ["NAT123", "NAT125"], "matiere": "Dessin", "note": 15}\r\n])\r\n<\/pre>\n
Le champ codes est un tableau ici, pour que chaque \u00e9l\u00e8ve puisse r\u00e9cup\u00e9rer sa note, il va falloir \u00e9clater ce tableau \u00e0 l’aide d’unwind avant d’utiliser lookup dessus, nous allons partir de projets cette fois:<\/p>\n
\r\ndb.projets.aggregate([\r\n   {\r\n      $unwind: "$codes"\r\n   },\r\n   {\r\n     $lookup: {\r\n         "from": "eleves",\r\n         "localField": "codes",\r\n         "foreignField": "code",\r\n         "as": "eleve"\r\n     }\r\n   },\r\n   {\r\n     $project: {\r\n         "_id": 0,\r\n         "codes": 0,\r\n         "eleve._id": 0\r\n     }\r\n   }\r\n])\r\n<\/pre>\n
Voil\u00e0 le jeu de r\u00e9sultat li\u00e9:<\/p>\n
\r\n{\r\n\t"matiere": "Dessin",\r\n\t"note": 15,\r\n\t"eleve": [{\r\n\t\t"nom": "S\u00e9bastien Ferrandez",\r\n\t\t"code": "NAT123"\r\n\t}]\r\n} {\r\n\t"matiere": "Dessin",\r\n\t"note": 15,\r\n\t"eleve": [{\r\n\t\t"nom": "Evelyne Durand",\r\n\t\t"code": "NAT125"\r\n\t}]\r\n}\r\n<\/pre>\n
Nous allons rajouter une \u00e9tape addFields pour que le tableau contenant un seul document (eleve) devienne un document tout simple, voici le pipeline final:<\/p>\n
\r\ndb.projets.aggregate([\r\n   {\r\n      $unwind: "$codes"\r\n   },\r\n   {\r\n     $lookup: {\r\n         "from": "eleves",\r\n         "localField": "codes",\r\n         "foreignField": "code",\r\n         "as": "eleve"\r\n     }\r\n   },\r\n   {\r\n     $addFields: {\r\n       "eleve": { $arrayElemAt: ["$eleve", 0] }\r\n     }\r\n   },\r\n   {\r\n     $project: {\r\n         "_id": 0,\r\n         "codes": 0,\r\n         "eleve._id": 0\r\n     }\r\n   }\r\n])\r\n<\/pre>\n
Et voil\u00e0 l’travail !<\/p>\n
\r\n{\r\n\t"matiere": "Dessin",\r\n\t"note": 15,\r\n\t"eleve": {\r\n\t\t"nom": "S\u00e9bastien Ferrandez",\r\n\t\t"code": "NAT123"\r\n\t}\r\n} {\r\n\t"matiere": "Dessin",\r\n\t"note": 15,\r\n\t"eleve": {\r\n\t\t"nom": "Evelyne Durand",\r\n\t\t"code": "NAT125"\r\n\t}\r\n}\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
Cette \u00e9tape de recherche (lookup) permet d\u2019effectuer l\u2019\u00e9quivalent d\u2019une jointure externe dans un syst\u00e8me de gestion de bases de donn\u00e9es relationnelles (SGBDR) et ne peut op\u00e9rer que sur des collections non shard\u00e9es situ\u00e9es dans la m\u00eame base de donn\u00e9es. Syntaxe de lookup { $lookup: { from: < collection \u00e0 joindre >, localField: < champ dans […]\n<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[78,26],"tags":[103,105,104],"yoast_head":"\nMongoDB - lookup et l'agr\u00e9gation - La Fabrique de code - Tech blog<\/title>\n<meta name=\"description\" content=\"R\u00e9alisez l'\u00e9quivalent d'une jointure avec MongoDB en utilisant $lookup !\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"http:\/\/www.lafabriquedecode.com\/blog\/2019\/09\/mongodb-lookup-pipeline-agregation\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MongoDB - lookup et l'agr\u00e9gation - La Fabrique de code - Tech blog\" \/>\n<meta property=\"og:description\" content=\"R\u00e9alisez l'\u00e9quivalent d'une jointure avec MongoDB en utilisant $lookup !\" \/>\n<meta property=\"og:url\" content=\"http:\/\/www.lafabriquedecode.com\/blog\/2019\/09\/mongodb-lookup-pipeline-agregation\/\" \/>\n<meta property=\"og:site_name\" content=\"La Fabrique de code - Tech blog\" \/>\n<meta property=\"article:published_time\" content=\"2019-09-03T14:32:10+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/www.lafabriquedecode.com\/blog\/wp-content\/uploads\/2019\/09\/Mongo-DB-lookup.jpg\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:card\" content=\"summary\" \/>\n<meta name=\"twitter:creator\" content=\"@LaFabrique2Code\" \/>\n<meta name=\"twitter:site\" content=\"@LaFabrique2Code\" \/>\n<meta name=\"twitter:label1\" content=\"\u00c9crit par\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"Dur\u00e9e de lecture estim\u00e9e\" \/>\n\t<meta name=\"twitter:data2\" content=\"6 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"http:\/\/www.lafabriquedecode.com\/blog\/2019\/09\/mongodb-lookup-pipeline-agregation\/\",\"url\":\"http:\/\/www.lafabriquedecode.com\/blog\/2019\/09\/mongodb-lookup-pipeline-agregation\/\",\"name\":\"MongoDB - lookup et l'agr\u00e9gation - La Fabrique de code - Tech blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.lafabriquedecode.com\/blog\/#website\"},\"datePublished\":\"2019-09-03T14:32:10+00:00\",\"dateModified\":\"2019-09-03T14:32:10+00:00\",\"author\":{\"@id\":\"https:\/\/www.lafabriquedecode.com\/blog\/#\/schema\/person\/83863c048b82fd9ccf6407bddd241162\"},\"description\":\"R\u00e9alisez l'\u00e9quivalent d'une jointure avec MongoDB en utilisant $lookup !\",\"breadcrumb\":{\"@id\":\"http:\/\/www.lafabriquedecode.com\/blog\/2019\/09\/mongodb-lookup-pipeline-agregation\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"http:\/\/www.lafabriquedecode.com\/blog\/2019\/09\/mongodb-lookup-pipeline-agregation\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"http:\/\/www.lafabriquedecode.com\/blog\/2019\/09\/mongodb-lookup-pipeline-agregation\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.lafabriquedecode.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MongoDB – lookup et l’agr\u00e9gation\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.lafabriquedecode.com\/blog\/#website\",\"url\":\"https:\/\/www.lafabriquedecode.com\/blog\/\",\"name\":\"La Fabrique de code - Tech blog\",\"description\":\"PHP objet, MySQL, Design Patterns, OOP...et plus !\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.lafabriquedecode.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"fr-FR\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.lafabriquedecode.com\/blog\/#\/schema\/person\/83863c048b82fd9ccf6407bddd241162\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\/\/www.lafabriquedecode.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"http:\/\/0.gravatar.com\/avatar\/fc2e1de7c8a1871b50ff9c6a6f8682a2?s=96&d=retro&r=g\",\"contentUrl\":\"http:\/\/0.gravatar.com\/avatar\/fc2e1de7c8a1871b50ff9c6a6f8682a2?s=96&d=retro&r=g\",\"caption\":\"admin\"},\"url\":\"http:\/\/www.lafabriquedecode.com\/blog\/author\/admin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"MongoDB - lookup et l'agr\u00e9gation - La Fabrique de code - Tech blog","description":"R\u00e9alisez l'\u00e9quivalent d'une jointure avec MongoDB en utilisant $lookup !","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"http:\/\/www.lafabriquedecode.com\/blog\/2019\/09\/mongodb-lookup-pipeline-agregation\/","og_locale":"fr_FR","og_type":"article","og_title":"MongoDB - lookup et l'agr\u00e9gation - La Fabrique de code - Tech blog","og_description":"R\u00e9alisez l'\u00e9quivalent d'une jointure avec MongoDB en utilisant $lookup !","og_url":"http:\/\/www.lafabriquedecode.com\/blog\/2019\/09\/mongodb-lookup-pipeline-agregation\/","og_site_name":"La Fabrique de code - Tech blog","article_published_time":"2019-09-03T14:32:10+00:00","og_image":[{"url":"http:\/\/www.lafabriquedecode.com\/blog\/wp-content\/uploads\/2019\/09\/Mongo-DB-lookup.jpg"}],"author":"admin","twitter_card":"summary","twitter_creator":"@LaFabrique2Code","twitter_site":"@LaFabrique2Code","twitter_misc":{"\u00c9crit par":"admin","Dur\u00e9e de lecture estim\u00e9e":"6 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"http:\/\/www.lafabriquedecode.com\/blog\/2019\/09\/mongodb-lookup-pipeline-agregation\/","url":"http:\/\/www.lafabriquedecode.com\/blog\/2019\/09\/mongodb-lookup-pipeline-agregation\/","name":"MongoDB - lookup et l'agr\u00e9gation - La Fabrique de code - Tech blog","isPartOf":{"@id":"https:\/\/www.lafabriquedecode.com\/blog\/#website"},"datePublished":"2019-09-03T14:32:10+00:00","dateModified":"2019-09-03T14:32:10+00:00","author":{"@id":"https:\/\/www.lafabriquedecode.com\/blog\/#\/schema\/person\/83863c048b82fd9ccf6407bddd241162"},"description":"R\u00e9alisez l'\u00e9quivalent d'une jointure avec MongoDB en utilisant $lookup !","breadcrumb":{"@id":"http:\/\/www.lafabriquedecode.com\/blog\/2019\/09\/mongodb-lookup-pipeline-agregation\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["http:\/\/www.lafabriquedecode.com\/blog\/2019\/09\/mongodb-lookup-pipeline-agregation\/"]}]},{"@type":"BreadcrumbList","@id":"http:\/\/www.lafabriquedecode.com\/blog\/2019\/09\/mongodb-lookup-pipeline-agregation\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.lafabriquedecode.com\/blog\/"},{"@type":"ListItem","position":2,"name":"MongoDB – lookup et l’agr\u00e9gation"}]},{"@type":"WebSite","@id":"https:\/\/www.lafabriquedecode.com\/blog\/#website","url":"https:\/\/www.lafabriquedecode.com\/blog\/","name":"La Fabrique de code - Tech blog","description":"PHP objet, MySQL, Design Patterns, OOP...et plus !","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.lafabriquedecode.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"fr-FR"},{"@type":"Person","@id":"https:\/\/www.lafabriquedecode.com\/blog\/#\/schema\/person\/83863c048b82fd9ccf6407bddd241162","name":"admin","image":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/www.lafabriquedecode.com\/blog\/#\/schema\/person\/image\/","url":"http:\/\/0.gravatar.com\/avatar\/fc2e1de7c8a1871b50ff9c6a6f8682a2?s=96&d=retro&r=g","contentUrl":"http:\/\/0.gravatar.com\/avatar\/fc2e1de7c8a1871b50ff9c6a6f8682a2?s=96&d=retro&r=g","caption":"admin"},"url":"http:\/\/www.lafabriquedecode.com\/blog\/author\/admin\/"}]}},"_links":{"self":[{"href":"http:\/\/www.lafabriquedecode.com\/blog\/wp-json\/wp\/v2\/posts\/1246"}],"collection":[{"href":"http:\/\/www.lafabriquedecode.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.lafabriquedecode.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.lafabriquedecode.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.lafabriquedecode.com\/blog\/wp-json\/wp\/v2\/comments?post=1246"}],"version-history":[{"count":20,"href":"http:\/\/www.lafabriquedecode.com\/blog\/wp-json\/wp\/v2\/posts\/1246\/revisions"}],"predecessor-version":[{"id":1267,"href":"http:\/\/www.lafabriquedecode.com\/blog\/wp-json\/wp\/v2\/posts\/1246\/revisions\/1267"}],"wp:attachment":[{"href":"http:\/\/www.lafabriquedecode.com\/blog\/wp-json\/wp\/v2\/media?parent=1246"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.lafabriquedecode.com\/blog\/wp-json\/wp\/v2\/categories?post=1246"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.lafabriquedecode.com\/blog\/wp-json\/wp\/v2\/tags?post=1246"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}