mongodb aggregation match multiple $and on the same field

Question

i have a document like this :

{
    "ExtraFields" : [
    {
        "value" : "print",
        "fieldID" : ObjectId("5535627631efa0843554b0ea")
    },
    {
        "value" : "14",
        "fieldID" : ObjectId("5535627631efa0843554b0eb")
    },
    {
        "value" : "POLYE",
        "fieldID" : ObjectId("5535627631efa0843554b0ec")
    },
    {
        "value" : "30",
        "fieldID" : ObjectId("5535627631efa0843554b0ed")
    },
    {
        "value" : "0",
        "fieldID" : ObjectId("5535627631efa0843554b0ee")
    },
    {
        "value" : "0",
        "fieldID" : ObjectId("5535627731efa0843554b0ef")
    },
    {
        "value" : "0",
        "fieldID" : ObjectId("5535627831efa0843554b0f0")
    },
    {
        "value" : "42",
        "fieldID" : ObjectId("5535627831efa0843554b0f1")
    },
    {
        "value" : "30",
        "fieldID" : ObjectId("5535627831efa0843554b0f2")
    },
    {
        "value" : "14",
        "fieldID" : ObjectId("5535627831efa0843554b0f3")
    },
    {
        "value" : "19",
        "fieldID" : ObjectId("5535627831efa0843554b0f4")
    }
],
        "id" : ObjectId("55369e60733e4914550832d0"), "title" : "A product"
}

what i want is to match one or more sets from the ExtraFields array. For example, all the products that contain the values print and 30. Since a value may be found in more than one fieldID (like 0 or true) we need to create a set like

WHERE (fieldID : ObjectId("5535627631efa0843554b0ea"), value : "print")

Where i'm having problems is when querying more than one fields. The pipeline i came up with is :

db.products.aggregate([
    {'$unwind': '$ExtraFields'},

{
    '$match': {
        '$and': [{
            '$and': [{'ExtraFields.value': {'$in': ["A52A2A"]}}, {
                'ExtraFields.fieldID': ObjectId("5535627631efa0843554b0ea")
            }]
        }

            ,
            {
                '$and': [{'ExtraFields.value': '14'}, {'ExtraFields.fieldID': ObjectId("5535627631efa0843554b0eb")}]
            }

        ]
    }
},


]);

This returns zero results, but this is what i want to do in theory. Match all items that contain set 1 AND all that contain set 2.

The end result should look like a faceted search output :

[ 
    {
        "_id" : {
            "values" : "18",
            "fieldID" : ObjectId("5535627831efa0843554b0f3")
        },
        "count" : 2
    }, 
    {
        "_id" : {
            "values" : "33",
            "fieldID" : ObjectId("5535627831efa0843554b0f2")
        },
        "count" : 1
    }
]

Any ideas?

Answer 1

You could try the following aggregation pipeline

db.products.aggregate([
    {
        "$match": {
            "ExtraFields.value": { "$in": ["A52A2A", "14"] },
            "ExtraFields.fieldID": { 
                "$in": [
                    ObjectId("5535627631efa0843554b0ea"),
                    ObjectId("5535627631efa0843554b0eb")
                ] 
            }
        }
    },
    {
        "$unwind": "$ExtraFields"
    },
    {
        "$match": {
            "ExtraFields.value": { "$in": ["A52A2A", "14"] },
            "ExtraFields.fieldID": { 
                "$in": [
                    ObjectId("5535627631efa0843554b0ea"),
                    ObjectId("5535627631efa0843554b0eb")
                ] 
            }
        }
    },
    {
        "$group": {
            "_id": {
                "value": "$ExtraFields.value",
                "fieldID": "$ExtraFields.fieldID"
            },
            "count": {
                "$sum": 1
            }
        }
    }
])

With the sample document provided, this gives the output:

/* 1 */
{
    "result" : [ 
        {
            "_id" : {
                "value" : "14",
                "fieldID" : ObjectId("5535627631efa0843554b0eb")
            },
            "count" : 1
        }
    ],
    "ok" : 1
}