MongoDb $addFields and $match

user_5732791 0
Viewed 19199

In my mongodb query, I use $addFields to add ID field which concatenated of other three fields. My problem is that I get no result if I $match that new added field with value that I want to query. For other fields, they work just fine.

order of aggregation

what is in aggregation

data = await model.aggregate([
            {
                $project: {
                    projectName: 1,
                    price: 1,
                    'document': '$$ROOT'
                }
            },
            {
                $addFields:{
                    'document.id': {$concat: ['$document.propertyId.prefix','$document.propertyId.number']}
                }
            },
            {
                $match: {
                    $and: [
                        {
                            $or: [
                                {id: {$regex: '.*' + req.query.search + '.*', $options: "i"}},
                                {projectName: {$regex: '.*' + req.query.search + '.*', $options: "i"}},

                                /*This also doesnt work*/
                                // {'document.id': {$regex: '.*' + req.query.search + '.*', $options: "i"}},
                                // {'document.projectName': {$regex: '.*' + req.query.search + '.*', $options: "i"}},
                            ]
                        }
                    ]
                }
            },
            {
                $replaceRoot: {newRoot: "$document"}
            },
            {
                $sort: {
                    [sortBy]: sortType
                }
            },
        ]);
node.js mongodb aggregation
1 Answers

Next time please add a sample of your document so people can reproduce your problem. Having said that I don't see where your problem is. I created some data to reproduce your usecase. So I added the following document: 

{ 
    "_id" : ObjectId("5a0d5d376c9b762a7c035ec4"), 
    "projectName" : "some stack test", 
    "price" : NumberInt(45), 
    "propertyId" : {
        "prefix" : "a", 
        "number" : "7"
    }
}

Then I executed your script (without the sort) and it works fine:

db.yourCollectionName.aggregate([
    {
        $project: {
            "projectName": 1,
            "price": 1,
            "document": '$$ROOT'
        }
    },
    {
        $addFields: {
            "document.id": {
                $concat: ['$document.propertyId.prefix', '$document.propertyId.number']
            }
        }
    },
    {
        $match: {
            $and: [{
                $or: [{
                        "projectName": {
                            $regex: '.*' + "some stack test"
                        }
                    },

                    {
                        "document.id": {
                            $regex: '.*' + "a" + '.*',
                            $options: "7"
                        }
                    }
                ]
            }]

        }
    },
    {
        $replaceRoot: {
            newRoot: "$document"
        }
    }
])

The fact that you get no results is probably due to your request parameters. Besides how can the "projectName"have the same search parameters as your "document.id" Do they even match? Check your match pipeline again:

{"projectName": {$regex: '.*' + req.query.search + '.*', $options: "i"}},       
{'document.id': {$regex: '.*' + req.query.search + '.*', $options: "i"}}
user_16906570
Related