Querying into an array after lookedup mongodb-CodePudding

I have a parent table called 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
}

and a child table 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
}

Using pipeline wanted to get a task with an array of all the cardList but in $match i am able to check the status of parent class that is taskList but unable to check the status of listCard.

Below is the code I tried.

        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,
    }

This gives output perfect but cannot filter the listCards whose status is not 1

Below is the output i am getting.

        {
        "_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
            }
        ]
    }

How do I make a query that returns the task with all listCards but the status of listCards must be 1. means ON.

CodePudding user response：

use $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