CODEWITHSUNDEEP
mongodbaggregation-frameworkaggregate-functions
Code_Worm
Code_Worm

Reputation: 4470

$eq inside filter does not work on array fields - Mongodb

I have an aggregate query below. I would have to filter out the result of aggregation on Product collection because for some customers, there are huge number of products and fetching all the customer's products without filter (in a single aggregate query) would result in Bson too large exception. The problem is one of the fields by which I want to perform the filter is array (p.metadata.category) and Mongo $eg seems not working on array fields, it seems it works only on simple values and objects fields.

db.getCollection('customer').aggregate([

{
    $lookup: {
            from: 'Product',
            localField: '_id',
            foreignField: 'partiesList._id',
            as: 'products',
    }
},
{
    $match: {
        "_id": {$in: [
                "C123",
                "C456"
            ]
        }
    }
},
{
    "$project": {
        "products": {
            "$filter": {
                "input": "$products",
                "as": "p",
                "cond": {
                    $and:[
                        {
                            "$eq": ["$$p.metadata.category.name","somevalue"]
                        },
                        {
                            "$eq": ["$$p.isMain",true]
                        }
                    ]
                }
            }
        }
    }
 }
])

So result of above query would be list of customers with empty products array (although products actually exist) but if I remove metadata.category.name condition from $and array in above query it works like charm and the p.isMain filter works fine and filters out the products as expected and shows only products with isMain set to true.

Here is my sample data :

Customer :

{
  "_id" : "C123",
  "name" : "coooo"
 }

Product (Customer's product) :

{
"_id" : "PR123",
"isMain" : true,
"name" : "My Product",
"metadata" : {
    "category" : [ 
        {
            "name" : "somevalue",
            "version" : "1",
            "referredType" : "Category",
            "type" : "Category"
        }, 
        {
            "name" : "someOtherValue",
            "version" : "1",
            "referredType" : "Category",
            "type" : "Category"
        }
    ]
},
"partiesList" : [ 
    {
        "_id" : "C123",
        "role" : "Customer"
        "referredType" : "Customer"
    }
 ]
}

Any ideas or alternatives ??

Upvotes: 2

Views: 1428

Answers (1)

Joe
Joe

Reputation: 28326

Since Product.metadata.category is an array,"$$p.metadata.category.name" is an array of all of the name values in each of those elements.

That $eq is then testing ["somevalue", "someOtherValue"] == "somevalue" which is always going to be false.

To check if a value is contained in an array, use $in, like

{$in: ["somevalue", "$$p.metadata.category.name"]}

Unrelated performance note: Since the match statement is considering the _id of the document from the input collection, placing the $match before the $lookup in the pipeline will result in fewer documents retrieved during the lookup, and therefore faster performance.

Upvotes: 3

Related Questions