Pymongo "and" operator not filtering query results

I have this monogdb schema:

groups = {
    "bsonType": "object",
    "required": [
        "created_at", "group_name", "owner",
        "members", "currency", "country",
        "group_username"
    ],
    "properties": {
        "created_at": {
            "bsonType": "date",
            "description": "The date and time when the group was created"
        },
        "group_name": {
            "bsonType": "string",
            "description": "Name of group"
        },
        "group_username": {
            "bsonType": "string",
            "description": "groups username"
        },
        "country": {
            "bsonType": "string",
            "description": "User's country and is required"
        },
        "currency": {
            "bsonType": "string",
            "description": "User's currency type and is required"
        },
        "description": {
            "bsonType": "string",
            "description": "Brief description of what the group does"
        },
        "block": {
            "bsonType": "bool",
            "description": "Field to check if user disabled from group activity"
        },
        "owner": {
            "bsonType": "object",
            "required": ["user_id", "status"],
            "properties": {
                "user_id": {
                    "bsonType": "objectId",
                    "description": "User id of the owner of the group"
                },
                "status": {
                    "bsonType": "string",
                    "description": "Default value is 'owner'",
                }
            },

        },
        "group_invite_link": {
            "bsonType": "string",
            "description": "Link used to invite new members"
        },
        "members": {
            "bsonType": "array",
            "items": {
                "bsonType": "object",
                "required": ["user_id", "name", "status", "confirmed", "joined_at"],
                "properties": {
                    "user_id": {
                        "bsonType": "objectId"
                    },
                    "name": {
                        "bsonType": "string",
                        "description": "Name of group member"
                    },
                    "status": {
                        "enum": ["admin", "member"],
                        "description": "can only be one of the enum values and is required"
                    },
                    "confirmed": {
                        "bsonType": "bool",
                        "description": "Confirms if user is fully authorized to operate in group"
                    },
                    "joined_at": {
                        "bsonType": "date",
                        "description": "Date user joined group"
                    }
                }
            }

        },
        "group_wallet_id": {
            "bsonType": "objectId",
            "description": "Id of wallet associated with group"
        }
    }

}

and I am querying it to return only documents that match the below query:

db.groups.find({
            "$and": [
                {
                    "members.user_id": ObjectId(user_id)
                },
                {
                    "members.confirmed": True
                }

            ]
        })

It returns this:

{
"sucess": true,
"message": "Users groups",
"data": [
    {
        "_id": "63987c4263d7eba1e79a1df1",
        "created_at": "2022-12-13 13:21:03",
        "group_name": "Demo 1",
        "is_owner": true,
        "balance": {
            "$numberDecimal": "250.00"
        },
        "members": [
            {
                "user_id": "63987b7c63d7eba1e79a1dd7",
                "name": "Joy Kudosen",
                "status": "admin",
                "confirmed": true,
                "joined_at": "2022-12-13 13:21:03"
            },
            {
                "user_id": "63987b7c63d7eba1e79a1dd8",
                "name": "Joy Kudosen",
                "status": "member",
                "confirmed": true,
                "joined_at": "2022-12-14 13:22:03"
            },
            {
                "user_id": "63987b7c63d7eba1e79a1dd7",
                "name": "Joy Kudosen",
                "status": "admin",
                "confirmed": true,
                "joined_at": "2022-12-14 11:05:39"
            }
        ]
    },
    {
        "_id": "63987c5a63d7eba1e79a1df4",
        "created_at": "2022-12-13 13:21:27",
        "group_name": "Demo 2",
        "is_owner": true,
        "balance": {
            "$numberDecimal": "100"
        },
        "members": [
            {
                "user_id": "63987b7c63d7eba1e79a1dd7",
                "name": "Joy Kudosen",
                "status": "admin",
                "confirmed": true,
                "joined_at": "2022-12-13 13:21:27"
            }
        ]
    },
    {
        "_id": "6399ae06ebaca8cd5fbcd639",
        "created_at": "2022-12-14 11:05:39",
        "group_name": "Demo 4",
        "is_owner": true,
        "balance": {
            "$numberDecimal": "0"
        },
        "members": [
            {
                "user_id": "63987b7c63d7eba1e79a1dd7",
                "name": "Joy Kudosen",
                "status": "admin",
                "confirmed": true,
                "joined_at": "2022-12-14 11:05:39"
            }
        ]
    },
    {
        "_id": "63d1810699edcb7379f21c27",
        "created_at": "2023-01-25 19:20:33",
        "group_name": "Hello world",
        "is_owner": false,
        "balance": {
            "$numberDecimal": "0"
        },
        "members": [
            {
                "user_id": "63d180c399edcb7379f21c23",
                "name": "Joy Kudosen",
                "status": "admin",
                "confirmed": true,
                "joined_at": "2023-01-25 19:20:33"
            },
            {
                "user_id": "63987b7c63d7eba1e79a1dd7",
                "name": "Joy Kudosen",
                "status": "member",
                "confirmed": false,
                "joined_at": "2023-01-25 19:23:31"
            }
        ]
    }
]

}

I want to return only array objects that match the user’s id and "confirmed" value of "true" in the members array like this:

{
    "sucess": true,
    "message": "Users groups",
    "data": [
        {
            "_id": "63987c4263d7eba1e79a1df1",
            "created_at": "2022-12-13 13:21:03",
            "group_name": "Demo 1",
            "is_owner": true,
            "balance": {
                "$numberDecimal": "250.00"
            },
            "members": [
                {
                    "user_id": "63987b7c63d7eba1e79a1dd7",
                    "name": "Joy Kudosen",
                    "status": "admin",
                    "confirmed": true,
                    "joined_at": "2022-12-13 13:21:03"
                },
                {
                    "user_id": "63987b7c63d7eba1e79a1dd8",
                    "name": "Joy Kudosen",
                    "status": "member",
                    "confirmed": true,
                    "joined_at": "2022-12-14 13:22:03"
                },
                {
                    "user_id": "63987b7c63d7eba1e79a1dd7",
                    "name": "Joy Kudosen",
                    "status": "admin",
                    "confirmed": true,
                    "joined_at": "2022-12-14 11:05:39"
                }
            ]
        },
        {
            "_id": "63987c5a63d7eba1e79a1df4",
            "created_at": "2022-12-13 13:21:27",
            "group_name": "Demo 2",
            "is_owner": true,
            "balance": {
                "$numberDecimal": "100"
            },
            "members": [
                {
                    "user_id": "63987b7c63d7eba1e79a1dd7",
                    "name": "Joy Kudosen",
                    "status": "admin",
                    "confirmed": true,
                    "joined_at": "2022-12-13 13:21:27"
                }
            ]
        },
        {
            "_id": "6399ae06ebaca8cd5fbcd639",
            "created_at": "2022-12-14 11:05:39",
            "group_name": "Demo 4",
            "is_owner": true,
            "balance": {
                "$numberDecimal": "0"
            },
            "members": [
                {
                    "user_id": "63987b7c63d7eba1e79a1dd7",
                    "name": "Joy Kudosen",
                    "status": "admin",
                    "confirmed": true,
                    "joined_at": "2022-12-14 11:05:39"
                }
            ]
        }
    ]
}

I have used different forms of this query but I am unable to filter out the "hello world" group information. I have tried aggregation and the "or" operation and still get the same results.

>Solution :

"$elemMatch" is used to find documents that match multiple fields within an array element.

For example, like this:

db.groups.find({
  "members": {
    "$elemMatch": {
      "user_id": "63987b7c63d7eba1e79a1dd7",
      "confirmed": true
    }
  }
})

Try it on mongoplayground.net.

Leave a Reply