MongoDB 强大的连表查询之$lookup
1、lookup简介
MongoDB作为一个nosql类型的数据库,并没有像关系型数据库那样有着非常强大的外键(表与表)关联,但是MongoDB 3.2中增加了$lookup
这个很牛逼的操作符,能够让你实现多表关联查询,而且它被放到了aggregate
这种轻量级的pipeline框架上,一个字:牛
假设我们在devops库中有一个hosts集合以及一个gameapp集合,我们的gameapp集合中的文档host_id
字段关联到了hosts
文档的_id
字段。
2、模拟数据
// 进入到devops库
use devops
// 插入数据
db.hosts.insertMany([
{host: 'hdss7-21', private_ip: '172.16.7.21', public_ip: '202.100.23.21', region_id: '杭州', status: true},
{host: 'hdss7-22', private_ip: '172.16.7.22', public_ip: '202.100.23.22', region_id: '杭州', status: true},
])
db.gameapp.insertMany([
{host_id:ObjectId('5ef070ee381fbaacea727429'), name: 'qysg10001', version: '37184', port: 80001},
{host_id:ObjectId('5ef070ee381fbaacea727429'), name: 'qysg10002', version: '37184', port: 80002},
{host_id:ObjectId('5ef070ee381fbaacea727429'), name: 'qysg10003', version: '37184', port: 80003},
{host_id:ObjectId('5ef070ee381fbaacea72742a'), name: 'qysg10004', version: '37184', port: 80004},
{host_id:ObjectId('5ef070ee381fbaacea72742a'), name: 'qysg10005', version: '37184', port: 80005},
{host_id:ObjectId('5ef070ee381fbaacea72742a'), name: 'qysg10006', version: '37184', port: 80006},
{host_id:ObjectId('5ef070ee381fbaacea72742a'), name: 'qysg10007', version: '37184', port: 80007},
])
// 验证数据是否正确
db.hosts.find()
db.gameapp.find()
3、查询操作
查询指定游戏服所关联的主机信息,最后只显示主机的host、private_ip和status信息
1)$lookup使用
db.gameapp.aggregate([
{
$match: {name: 'qysg10001'}
},{
$lookup: {
from: 'hosts',
localField: 'host_id',
foreignField: '_id',
as: 'host_info'
}
}
])
简单介绍$lookup
中的参数:
form:需要关联的表(hosts)
localField:gameapp关联到hosts的键(字段)
foreignField:hosts被关联到gameapp的localField的键(字段)
as:对应的外键集合数据(可能存在一对多的情况)
返回的数据
{
"_id": ObjectId("5ef0737e381fbaacea727432"),
"host_id": ObjectId("5ef070ee381fbaacea727429"),
"name": "qysg10001",
"version": "37184",
"port": 80001,
"host_info": [{
"_id": ObjectId("5ef070ee381fbaacea727429"),
"host": "hdss7-21",
"private_ip": "172.16.7.21",
"public_ip": "202.100.23.21",
"region_id": "杭州",
"status": true
}]
}
2)可以看到host_info
返回元素装在列表中,需要先把host_info
外层列表除去,通过$unwind
实现对host_info
拆分
db.gameapp.aggregate([
{
$match: {name: 'qysg10001'}
},{
$lookup: {
from: 'hosts',
localField: 'host_id',
foreignField: '_id',
as: 'host_info'
}
},{
$unwind: {
path: '$host_info',
preserveNullAndEmptyArrays: true,
}
}
])
返回数据:
{
"_id": ObjectId("5ef0737e381fbaacea727432"),
"host_id": ObjectId("5ef070ee381fbaacea727429"),
"name": "qysg10001",
"version": "37184",
"port": 80001,
"host_info": {
"_id": ObjectId("5ef070ee381fbaacea727429"),
"host": "hdss7-21",
"private_ip": "172.16.7.21",
"public_ip": "202.100.23.21",
"region_id": "杭州",
"status": true
}
}
3)现在通过$project
对数据进行过滤,我们只需要拿到host_info
下的host、private_ip和status信息
db.gameapp.aggregate([
{
$match: {name: 'qysg10001'}
},{
$lookup: {
from: 'hosts',
localField: 'host_id',
foreignField: '_id',
as: 'host_info'
}
},{
$unwind: {
path: '$host_info',
preserveNullAndEmptyArrays: true,
}
},{
$project: {
_id: 0,
'host_info.host': 1,
'host_info.private_ip': 1,
'host_info.status': 1,
}
}
])
返回结果:
{
"host_info":
{
"host": "hdss7-21",
"private_ip": "172.16.7.21",
"status": true
}
}