我有一個名為taskList的父表
{
"_id": {
"$oid": "6180d047fb305ac50de9f277"
},
"projectId": {
"$oid": "61651c527128e5f5311ef4ca"
},
"title": "To Do111",
"sortOrder": 2,
"status": 1,
"createdAt": {
"$date": "2021-11-02T05:44:39.493Z"
},
"updatedAt": {
"$date": "2021-11-02T06:06:09.103Z"
},
"__v": 0
}
和一個子表listCard
{
"_id": {
"$oid": "6180db4b19f515587d9adf73"
},
"taskListId": {
"$oid": "6180d047fb305ac50de9f277"
},
"title": "To Do111",
"sortOrder": 2,
"status": 1,
"createdAt": {
"$date": "2021-11-02T06:31:39.513Z"
},
"updatedAt": {
"$date": "2021-11-02T07:03:16.102Z"
},
"__v": 0
}
{
"_id": {
"$oid": "6180e76872559ff97b05f8ef"
},
"taskListId": {
"$oid": "6180d047fb305ac50de9f277"
},
"title": "To Do 2",
"sortOrder": 0,
"status": 1,
"createdAt": {
"$date": "2021-11-02T07:23:20.559Z"
},
"updatedAt": {
"$date": "2021-11-02T07:23:20.559Z"
},
"__v": 0
}
使用管道想要獲得一個包含所有 cardList 陣列的任務，但在 $match 中我能夠檢查父類的狀態，即 taskList 但無法檢查 listCard 的狀態。
下面是我試過的代碼。
pipeline.push(
{
$lookup: {
from: "listCard",
localField: "_id",
foreignField: "taskListId",
as: "listCard"
}
},
{
$match: {
status: {$eq: ACTIVE_STATUS},
"listCard.status":{$eq : ACTIVE_STATUS},
},
},
);
//projection with condition checking user liked post or not
let projection = {
projectId : 1,
title: 1,
sortOrder : 1,
status : 1,
listCard : 1,
}
這給出了完美的輸出，但不能過濾狀態不是 1 的 listCards
下面是我得到的輸出。
{
"_id": "6180d047fb305ac50de9f277",
"projectId": "61651c527128e5f5311ef4ca",
"title": "To Do111",
"sortOrder": 2,
"status": 1,
"listCard": [
{
"_id": "6180db4b19f515587d9adf73",
"taskListId": "6180d047fb305ac50de9f277",
"title": "To Do111",
"sortOrder": 2,
"status": 1,
"createdAt": "2021-11-02T06:31:39.513Z",
"updatedAt": "2021-11-02T07:03:16.102Z",
"__v": 0
},
{
"_id": "6180e76872559ff97b05f8ef",
"taskListId": "6180d047fb305ac50de9f277",
"title": "To Do 2",
"sortOrder": 0,
"status": 1,
"createdAt": "2021-11-02T07:23:20.559Z",
"updatedAt": "2021-11-02T07:23:20.559Z",
"__v": 0
}
]
}
我如何進行查詢以回傳包含所有 listCards 的任務，但 listCards 的狀態必須為 1。表示 ON。
uj5u.com熱心網友回復：
用 $filter
db.taskList.aggregate([
{
$lookup: {
from: "listCard",
localField: "_id",
foreignField: "taskListId",
as: "listCard"
}
},
{
"$set": {
"listCard": {
"$filter": {
"input": "$listCard",
"as": "lc",
"cond": {
$eq: [
"$$lc.status",
1
]
}
}
}
}
}
])
mongoplayground
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/344954.html