mongodb根据表名查询_MongoDB联表查询

创建表user、order

db.user.insertMany([

{

_id: ObjectId("5af2b2c6b138c267e414c072"),

uid: "uid000",

name: "小红",

age: 26

},

{

_id: ObjectId("5af2b2c6b138c267e414c073"),

uid: "uid001",

name: "小芳",

age: 27

}

]);

db.order.insertMany([

{

_id: ObjectId("4af2b2c6b138c267e414c071"),

uid: "uid000",

product: "产品1",

money: 100

},

{

_id: ObjectId("4af2b2c6b138c267e414c072"),

uid: "uid000",

product: "产品2",

money: 200

},

{

_id: ObjectId("4af2b2c6b138c267e414c073"),

uid: "uid001",

product: "产品1",

money: 100

},

{

_id: ObjectId("4af2b2c6b138c267e414c074"),

uid: "uid001",

product: "产品2",

money: 200

}

]);

查询

联表查询

db.user.aggregate([{

$lookup: { // 左连接

from: "order", // 关联到order表

localField: "uid", // user 表关联的字段

foreignField: "uid", // order 表关联的字段

as: "orders"

}

}]);

返回结果:

// 1

{

"_id": ObjectId("5af2b2c6b138c267e414c072"),

"uid": "uid000",

"name": "小红",

"age": 26,

"orders": [

{

"_id": ObjectId("4af2b2c6b138c267e414c071"),

"uid": "uid000",

"product": "产品1",

"money": 100

},

{

"_id": ObjectId("4af2b2c6b138c267e414c072"),

"uid": "uid000",

"product": "产品2",

"money": 200

}

]

}

// 2

{

"_id": ObjectId("5af2b2c6b138c267e414c073"),

"uid": "uid001",

"name": "小芳",

"age": 27,

"orders": [

{

"_id": ObjectId("4af2b2c6b138c267e414c073"),

"uid": "uid001",

"product": "产品1",

"money": 100

},

{

"_id": ObjectId("4af2b2c6b138c267e414c074"),

"uid": "uid001",

"product": "产品2",

"money": 200

}

]

}

拆分orders

db.user.aggregate([{

$lookup: { // 左连接

from: "order", // 关联到order表

localField: "uid", // user 表关联的字段

foreignField: "uid", // order 表关联的字段

as: "orders"

}

},

{

$unwind: { // 拆分子数组

path: "$orders",

preserveNullAndEmptyArrays: true // 空的数组也拆分

}

}

]);

返回结果：

// 1

{

"_id": ObjectId("5af2b2c6b138c267e414c072"),

"uid": "uid000",

"name": "小红",

"age": 26,

"orders": {

"_id": ObjectId("4af2b2c6b138c267e414c071"),

"uid": "uid000",

"product": "产品1",

"money": 100

}

}

// 2

{

"_id": ObjectId("5af2b2c6b138c267e414c072"),

"uid": "uid000",

"name": "小红",

"age": 26,

"orders": {

"_id": ObjectId("4af2b2c6b138c267e414c072"),

"uid": "uid000",

"product": "产品2",

"money": 200

}

}

// 3

{

"_id": ObjectId("5af2b2c6b138c267e414c073"),

"uid": "uid001",

"name": "小芳",

"age": 27,

"orders": {

"_id": ObjectId("4af2b2c6b138c267e414c073"),

"uid": "uid001",

"product": "产品1",

"money": 100

}

}

// 4

{

"_id": ObjectId("5af2b2c6b138c267e414c073"),

"uid": "uid001",

"name": "小芳",

"age": 27,

"orders": {

"_id": ObjectId("4af2b2c6b138c267e414c074"),

"uid": "uid001",

"product": "产品2",

"money": 200

}

}

分组求和并返回字段数据

db.user.aggregate([{

$lookup: { // 左连接

from: "order", // 关联到order表

localField: "uid", // user 表关联的字段

foreignField: "uid", // order 表关联的字段

as: "orders"

}

}, {

$unwind: { // 拆分子数组

path: "$orders",

preserveNullAndEmptyArrays: true // 空的数组也拆分

}

}, { // 分组求和并返回

$group: { // 分组查询

_id: "$_id",

name: { $first: "$name" },

age: { $first: "$age" },

money: {$sum: "$orders.money"}

}

}]);

返回结果：

// 1

{

"_id": ObjectId("5af2b2c6b138c267e414c073"),

"name": "小芳",

"age": 27,

"money": 300

}

// 2

{

"_id": ObjectId("5af2b2c6b138c267e414c072"),

"name": "小红",

"age": 26,

"money": 300

}

查询用户的订单信息(订单id、产品、价格、用户名)

db.order.aggregate([{

$lookup: {

from: "user",

localField: "openid",

foreignField: "openid",

as: "u"

}

}, {

$unwind: "$u"

}, {

$addFields: { name: "$u.name" }

}, {

$project: {

_id: 1,

product: 1,

money: 1,

name: 1

}

}]);

返回结果：

// 1

{

"_id": ObjectId("4af2b2c6b138c267e414c071"),

"product": "产品1",

"money": 100,

"name": "小红"

}

// 2

{

"_id": ObjectId("4af2b2c6b138c267e414c071"),

"product": "产品1",

"money": 100,

"name": "小芳"

}

// 3

{

"_id": ObjectId("4af2b2c6b138c267e414c072"),

"product": "产品2",

"money": 200,

"name": "小红"

}

// 4

{

"_id": ObjectId("4af2b2c6b138c267e414c072"),

"product": "产品2",

"money": 200,

"name": "小芳"

}

// 5

{

"_id": ObjectId("4af2b2c6b138c267e414c073"),

"product": "产品1",

"money": 100,

"name": "小红"

}

// 6

{

"_id": ObjectId("4af2b2c6b138c267e414c073"),

"product": "产品1",

"money": 100,

"name": "小芳"

}

// 7

{

"_id": ObjectId("4af2b2c6b138c267e414c074"),

"product": "产品2",

"money": 200,

"name": "小红"

}

// 8

{

"_id": ObjectId("4af2b2c6b138c267e414c074"),

"product": "产品2",

"money": 200,

"name": "小芳"

}