MongoDB 强大的连表查询之$lookup
<p></p><div class="toc"><div class="toc-container-header">目录</div><ul><li>1、lookup简介</li><li>2、模拟数据</li><li>3、查询操作</li></ul></div><p></p><h1 id="1lookup简介">1、lookup简介</h1>
<p>MongoDB作为一个nosql类型的数据库，并没有像关系型数据库那样有着非常强大的外键(表与表)关联，但是MongoDB 3.2中增加了<code>$lookup</code>这个很牛逼的操作符，能够让你实现多表关联查询，而且它被放到了<code>aggregate</code>这种轻量级的pipeline框架上，一个字：牛</p>
<p>Aggregation支持的操作符</p>
<p>假设我们在devops库中有一个hosts集合以及一个gameapp集合，我们的gameapp集合中的文档<code>host_id</code>字段关联到了<code>hosts</code>文档的<code>_id</code>字段。</p>
<h1 id="2模拟数据">2、模拟数据</h1>
<pre><code class="language-js">// 进入到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()
</code></pre>
<h1 id="3查询操作">3、查询操作</h1>
<p>查询指定游戏服所关联的主机信息，最后只显示主机的host、private_ip和status信息</p>
<p>1）$lookup使用</p>
<pre><code class="language-js">db.gameapp.aggregate([
{
$match: {name: 'qysg10001'}
},{
$lookup: {
from: 'hosts',
localField: 'host_id',
foreignField: '_id',
as: 'host_info'
}
}
])
</code></pre>
<p>简单介绍<code>$lookup</code>中的参数：</p>
<pre><code class="language-py">form：需要关联的表（hosts）
localField：gameapp关联到hosts的键(字段)
foreignField：hosts被关联到gameapp的localField的键(字段)
as：对应的外键集合数据（可能存在一对多的情况）
</code></pre>
<p>返回的数据</p>
<pre><code class="language-js">{
"_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
}]
}
</code></pre>
<p>2）可以看到<code>host_info</code>返回元素装在列表中，需要先把<code>host_info</code>外层列表除去，通过<code>$unwind</code>实现对<code>host_info</code>拆分</p>
<pre><code class="language-js">db.gameapp.aggregate([
{
$match: {name: 'qysg10001'}
},{
$lookup: {
from: 'hosts',
localField: 'host_id',
foreignField: '_id',
as: 'host_info'
}
},{
$unwind: {
path: '$host_info',
preserveNullAndEmptyArrays: true,
}
}
])
</code></pre>
<p>返回数据：</p>
<pre><code class="language-js">{
"_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
}
}
</code></pre>
<p>3）现在通过<code>$project</code>对数据进行过滤，我们只需要拿到<code>host_info</code>下的host、private_ip和status信息</p>
<pre><code class="language-js">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,
}
}
])
</code></pre>
<p>返回结果：</p>
<pre><code class="language-js">{
"host_info":
{
"host": "hdss7-21",
"private_ip": "172.16.7.21",
"status": true
}
}
</code></pre><br><br>
来源：https://www.cnblogs.com/jasonminghao/p/13178087.html
頁:
[1]