get object with the matching objects in sub array in mongoose-CodePudding

data store in the DB:

{
    "_id": "61c1c0efc204bb170e280d2f",
    "title": "Searching Relevant Cases",
    "question": "Your Industry",
    "vendor_id": "61b8324040fb21d80f3e6702",
    "answer_type": "is_radio",
    "optional_answer": false,
    "sub_question_type": "none",
    "view": "list_view",
    "is_active": true,
    "is_delete": false,
    "sub_question": [],
    "draggable_list": [],
    "__v": 0
},
{
    "_id": "61c1c142c204bb170e280d45",
    "title": "Searching Relevant Cases",
    "question": "Employee size",
    "vendor_id": "61b8324040fb21d80f3e6702",
    "answer_type": "is_radio",
    "optional_answer": false,
    "sub_question_type": "none",
    "view": "list_view",
    "is_active": true,
    "is_delete": false,
    "sub_question": [],
    "draggable_list": [],
    "__v": 0
},
{
    "_id": "61c1c24ac204bb170e280d5e",
    "title": "Dependent",
    "question": "On previous Chosen option",
    "vendor_id": "61b8324040fb21d80f3e6702",
    "answer_type": "is_radio",
    "optional_answer": false,
    "sub_question_type": "has_parent",
    "sub_question": [
        {
            "question_id": "61c1c0efc204bb170e280d2f",
            "_id": "61c1c24ac204bb170e280d5f",
            "answer": "Bank"
        },
        {
            "question_id": "61c1c142c204bb170e280d45",
            "_id": "61c1c24ac204bb170e280d60",
            "answer": "SMB (<100)"
        }
    ],
    "view": "list_view",
    "is_active": true,
    "is_delete": false,
    "draggable_list": [],
    "__v": 0
},
{
    "_id": "61c1c2d7c204bb170e280d7e",
    "title": "Dependent 1",
    "question": "On previous Chosen option 1",
    "vendor_id": "61b8324040fb21d80f3e6702",
    "answer_type": "is_radio",
    "optional_answer": false,
    "sub_question_type": "has_parent",
    "sub_question": [
        {
            "question_id": "61c1c142c204bb170e280d45",
            "_id": "61c1c2d7c204bb170e280d7f",
            "answer": "Mid (100 - 1000)"
        },
        {
            "question_id": "61c1c0efc204bb170e280d2f",
            "_id": "61c1c2d7c204bb170e280d80",
            "answer": "Tech"
        }
    ],
    "view": "list_view",
    "is_active": true,
    "is_delete": false,
    "draggable_list": [],
    "__v": 0
}

Query parameters are:

obj: {
    "question_id": "61c1c0efc204bb170e280d2f",
    "answer": "Bank"
},
obj1: {
    "question_id": "61c1c142c204bb170e280d45",
    "answer": "SMB (<100)"
}

The result I'm expecting is:

{
    "_id": "61c1c24ac204bb170e280d5e",
    "title": "Dependent",
    "question": "On previous Chosen option",
    "vendor_id": "61b8324040fb21d80f3e6702",
    "answer_type": "is_radio",
    "optional_answer": false,
    "sub_question_type": "has_parent",
    "sub_question": [
        {
            "question_id": "61c1c0efc204bb170e280d2f",
            "_id": "61c1c24ac204bb170e280d5f",
            "answer": "Bank"
        },
        {
            "question_id": "61c1c142c204bb170e280d45",
            "_id": "61c1c24ac204bb170e280d60",
            "answer": "SMB (<100)"
        }
    ],
    "view": "list_view",
    "is_active": true,
    "is_delete": false,
    "draggable_list": [],
    "__v": 0
}

The answer what I get from the user (user:14732669) on StackOverflow is: https://mongoplayground.net/p/pAV76ctwhfC this query is working but not returning the resulted object instead it returns two objects.

I have to match the question_id as well as the answer of (obj and obj1) for both the objects which are in the sub_question array

CodePudding user response：

$match

db.collection.aggregate([
  {
    "$match": {
      "$and": [
        {
          "sub_question.question_id": "61c1c0efc204bb170e280d2f",
          "sub_question.answer": "Bank"
        },
        {
          "sub_question.question_id": "61c1c142c204bb170e280d45",
          "sub_question.answer": "SMB (<100)"
        }
      ]
    }
  }
])

mongoplayground