mongodbのaggregateで、join結合のような処理の例 #mongodb

■ 概要:

DB関連で、RDBのJOIN(結合処理)　のような処理の例となり。
noSQLは、データ一貫性の面は難しいイメージでしたが。
mongoの場合は、aggregateの様な機能で。ある程度対応できそうでしたので
調べてみました

■ 環境

mongoDb : 3.6.3

■ 参考

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

https://docs.mongodb.com/manual/reference/operator/aggregation-pipeline/

■ データの追加

db.books.insertMany([
 { code: "0101", name: "book-1", price: 120 },
 { code: "0102", name: "book-2", price: 130  },
 { code: "0201", name: "book-3", price: 180  },
]);
db.orders.insertMany([
 { book_code: "0101", quantity: 2 },
 { book_code: "0102", quantity: 6 }
]);

・aggregate実行 , ordersに結合する例

from : 結合されるcollection
localField: 結合する項目
foreignField :結合されるcollection 項目
as : 結合結果の項目名

db.orders.aggregate([
   { $lookup:
       {
           from:"books",
           localField:"book_code",
           foreignField:"code",
           as:"book"
       },
   },
]);

・結合結果

book に、結合先データが表示できました。

 { "_id" : ObjectId("5fb31b267068fbef60f9715d"), "book_code" : "0101", "quantity" : 2, 
 "book" : [ { "_id" : ObjectId("5fb31ae87068fbef60f9715a"), 
   "code" : "0101", "name" : "book-1", "price" : 120 } ] }

{ "_id" : ObjectId("5fb31b267068fbef60f9715e"), "book_code" : "0102", "quantity" : 6, "
 book" : [ { "_id" : ObjectId("5fb31ae87068fbef60f9715b"), 
   "code" : "0102", "name" : "book-2", "price" : 130 } ] }

■ $match　で、検索する場合
$match　を追加すると、検索もできました。

> db.orders.aggregate([
...     { $match: { book_code: '0101' } },
...     { $lookup:
...         {
...             from:"books",
...             localField:"book_code",
...             foreignField:"code",
...             as:"book"
...         },
...     },
... ]);
{ "_id" : ObjectId("5fb31b267068fbef60f9715d"), "book_code" : "0101",
 "quantity" : 2, 
 "book" : [ { "_id" : ObjectId("5fb31ae87068fbef60f9715a"), "code" : "0101", "name" : "book-1", "price" : 120 } ] }

■ 複数 collectionを、一覧に対して結合

・データ追加, orders削除し。下記追加

db.order_users.insertMany([
 { code: "1", name: "hoge taro"},
 { code: "2", name: "huga taro"},
]);
db.orders.insertMany([
 { book_code: "0101", order_user_code: "1", quantity: 2 },
 { book_code: "0102", order_user_code: "2", quantity: 6 }
]);

・aggregate を、複数collectionに設定変更

db.orders.aggregate([
   { $lookup:
       {
           from:"books",
           localField:"book_code",
           foreignField:"code",
           as:"book"
       },
   },
   { $lookup:
       {
           from:"order_users",
           localField:"order_user_code",
           foreignField:"code",
           as:"user"
       },
   },    
]);

order_users, books collectionが結合できました

{ "_id" : ObjectId("5fb33a794e58dd9b18a7ce52"), "book_code" : "0101", "order_user_code" : "1", "quantity" : 2, 
 "book" : [ { "_id" : ObjectId("5fb31ae87068fbef60f9715a"), "code" : "0101", "name" : "book-1", "price" : 120 } ], 
 "user" : [ { "_id" : ObjectId("5fb33a194e58dd9b18a7ce50"), "code" : "1", "name" : "hoge taro" } ] }
{ "_id" : ObjectId("5fb33a794e58dd9b18a7ce53"), "book_code" : "0102", "order_user_code" : "2", "quantity" : 6, 
 "book" : [ { "_id" : ObjectId("5fb31ae87068fbef60f9715b"), "code" : "0102", "name" : "book-2", "price" : 130 } ], 
 "user" : [ { "_id" : ObjectId("5fb33a194e58dd9b18a7ce51"), "code" : "2", "name" : "huga taro" } ] }                

■ ObjectID で、結合する場合

jsで、MongoClientを使用して。ordersを追加する例となります

var ObjectID = require('mongodb').ObjectID;
const MongoClient = require('mongodb').MongoClient;

//
function join_test2(){
   var URL = "mongodb://localhost:27017";
   MongoClient.connect(URL, function(err, client){
       var db = client.db("db1");
       const collection = db.collection("orders");
       var item = {
           book_id: new ObjectID( "5fb31ae87068fbef60f9715a"),
           quantity: 4,
       }
       collection.insertOne( item ).then(function(doc) {
           console.log(docs);
       });
   });
}               

book_id : books collectionの "_id" を、記載
collection.insertOne　で、追加

・aggregate

db.orders.aggregate([
   { $lookup:
       {
           from:"books",
           localField:"book_id",
           foreignField:"_id",
           as:"book"
       },
   },
]);

orders.book_id , books._id を結合する

・aggregate　結果,

{ "_id" : ObjectId("5fb34415be2844335b25cc5e"), "book_id" : ObjectId("5fb31ae87068fbef60f9715a"), "quantity" : 2, 
 "book" : [ { "_id" : ObjectId("5fb31ae87068fbef60f9715a"), "code" : "0101", "name" : "book-1", "price" : 120 } ] }
{ "_id" : ObjectId("5fb3445784f83d342c7dd5b3"), "book_id" : ObjectId("5fb31ae87068fbef60f9715a"), "quantity" : 4, 
 "book" : [ { "_id" : ObjectId("5fb31ae87068fbef60f9715a"), "code" : "0101", "name" : "book-1", "price" : 120 } ] }

今度は、IDで結合する結果となりました。

...