$eq inside $filter does not work on array fields

Working with Data
1

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",
"productMetadata": {
    "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 ??

2

Hello @hesam_rasouli, welcome to the MongoDB Community forum!

Here is a way to work with your issue of matching an array within the $filter array operator. The following code snippet from your aggregation:

 "$eq": [
     "$$p.productMetadata.category.name",
     "somevalue"
]

may be substituted with this:

$gt: [ { 
    $size: { 
       $filter: { 
           input: "$$p.productMetadata.category", 
           as: "metaCat", 
           cond: {
              $eq: [ "$$metaCat.name", "somevalue" ]
           }
       }
    }
}, 0 ]

The query works fine. Nesting of $filter or any other operators is possible to get the desired output.

1 Like
3

Thanks for your reploy, I replaced your code snippet with mine and I get this error

the argument to $size must be an array

The problem is category array for some products is null
I’m trying to blend it with $ifNull but no luck so far.

Do you have any idea how to bypass null category fields in the $filter you gave me ??

4

You can replace the $filter’s input value to use the $ifNull as follows:

input: "$$p.productMetadata.category"

with this:

input: { $ifNull: [ "$$p.productMetadata.category", [ ] ] }

1 Like
5

It worked like magic, you’re life saver.
Thank you so so much

6

This topic was automatically closed 5 days after the last reply. New replies are no longer allowed.