子查询替换包裹在列表中collaction一种在集合体B MongoDB的关键
问题描述:
我有MongoDB的查询问题值子查询替换包裹在列表中collaction一种在集合体B MongoDB的关键
有2个集合在我的数据库,名称status
和menu
在status
主键_id
对于买表的值menu
收集
外键对于status
集合:
{
"_id": "green", "description": "running"
}
{
"_id": "yellow", "description": "prepareing"
}
{
"_id": "black", "description": "closing"
}
{
"_id": "red", "description": "repairing"
}
对于menu
集合:
{
"name": "tony",
"bought": [
{
"notebook": "green"
},
{
"cellphone": "red"
}
]
}
{
"name": "andy",
"bought": [
{
"fan": "black"
}
]
}
我怎样才能查询得到以下答案吗?
(只需更换description
为_id
)
{
"name": "tony",
"bought": [
{
"notebook": "running"
},
{
"cellphone": "repairing"
}
]
}
是它的NoSQL子查询问题?我如何使用关键词来谷歌?
答
下面是使用聚合版本:
我们先从$unwind阶段提取一个单独的行
然后,$objectToArray各买正常化买场。
然后我们可以执行$lookup加入状态。
然后我们使用$group按名称
而且$arrayToObject重新集结到复位买了非规范化的风格
> db.menu.find()
{ "_id" : ObjectId("5a102b0b49b317e3f8d6268b"), "name" : "tony", "bought" : [ { "notebook" : "green" }, { "cellphone" : "red" } ] }
{ "_id" : ObjectId("5a102b0b49b317e3f8d6268c"), "name" : "andy", "bought" : [ { "fan" : "black" } ] }
> db.status.find()
{ "_id" : "green", "description" : "running" }
{ "_id" : "yellow", "description" : "prepareing" }
{ "_id" : "black", "description" : "closing" }
{ "_id" : "red", "description" : "repairing" }
> db.menu.aggregate([
{$unwind: '$bought'},
{$project: {name: 1, bought: {$objectToArray: '$bought'}}}, {$unwind: '$bought'},
{$lookup: {from: 'status', localField: 'bought.v', foreignField: '_id', as: "status"}},
{$project: {name: 1, bought: ["$bought.k", { $arrayElemAt: ["$status.description", 0]}]}},
{$addFields: {b: {v: {$arrayElemAt: ['$bought', 1]}, k: { $arrayElemAt: ['$bought', 0]}}}},
{$group: {_id: { name: '$name', _id: "$_id"}, b: {$push: "$b"}}},
{$project: {_id: "$_id._id", name: "$_id.name", bought: {$arrayToObject: "$b"}}}
])
{ "_id" : ObjectId("5a102b0b49b317e3f8d6268c"), "name" : "andy", "bought" : { "fan" : "closing" } }
{ "_id" : ObjectId("5a102b0b49b317e3f8d6268b"), "name" : "tony", "bought" : { "notebook" : "running", "cellphone" : "repairing" } }
我认为它可以在simplier的方式进行,但我不知道如何(我很高兴知道)。
谢谢!它对我有用。我也想知道一个更简单的答案。 –