Nested MongoDB query with Golang mgo.v2

Question

I'm querying a json structure with a nested array of items. I'd like to return the entire structure but only include the items of the nest which match the query criteria.

So - sample structure is

{
     parentId:1,
     items: [
            {
               field1: 1
               field2: 2
            },
            {
               field1: 3
               field2: 4
            }
            ]
}

I would like to query with something like this ->

db.getCollection('mycollection').find({"items.field1":1, "items.field2":2}) 

this works but it also brings back the second child item, what I want it to actually return is this ->

{
     parentId:1,
     items: [
            {
               field1: 1
               field2: 2
            }
            ]
}

I have been able to create a query in MongoDB itself which achieves what I want ->

db.getCollection('mycollection').aggregate(
  { $unwind : "$items" },
  { $match : {
     "items.field1": 1,
     "items.field2": 2,
  }}
)

but when trying to set this up using mgo.v2 it's proving a bit of a mare. The collection.Find method doesn't seem to like the $unwind command and can't seem to get the right syntax to get it working with the Pipe method.

Does anyone have any suggestions how this can be created? Can I just create the string and pass it for execution?

Answer 1

Referring to chridam's answer, I believe the one with $filter can be simplified a bit:

c := session.DB("test").C("mycollection")

pipe := c.Pipe([]bson.M{{
    "$project": bson.M{
        "parentId": 1,
        "items": bson.M{
            "$filter": bson.M{
                "input": "$items",
                "as":    "item",
                "cond": bson.M{
                    "$and": []bson.M{
                        {"$eq": []interface{}{"$$item.field1", 1}},
                        {"$eq": []interface{}{"$$item.field2", 2}},
                    },
                },
            },
        },
    },
}})

resp := []bson.M{}
err = pipe.All(&resp)

if err != nil {
    panic(err)
}

fmt.Println(resp)

The other with $unwind:

c := session.DB("test").C("mycollection")

pipe := c.Pipe([]bson.M{
    {"$unwind": "$items"},
    {"$match": bson.M{
        "items.field1": 1,
        "items.field2": 2,
    }},
    {"$group": bson.M{
        "_id":      "$_id",
        "parentId": bson.M{"$first": "$parentId"},
        "items":    bson.M{"$push": "$items"},
    }},
})

resp := []bson.M{}
err = pipe.All(&resp)

if err != nil {
    panic(err)
}

fmt.Println(resp) 

Both compile without error and return

[map[
    _id:ObjectIdHex(".....") 
    parentId:1 
    items:[map[
        field2:2 
        field1:1
    ]]
]]

go 1.6, mongo 3.2

Answer 2

The actual pipeline you need involves the use of the $filter operator, which selects a subset of the array to return based on the specified condition. It returns an array with only those elements that match the condition.

In your case, you need to run the following aggregation operation

db.mycollection.aggregate([
   {
        "$project": {
            "parentId": 1,          
            "items": {
                "$filter": {
                    "input": "$items",
                    "as": "item",
                    "cond": { 
                        "$and": [
                            { "$eq": ["$$item.field1", 1] },
                            { "$eq": ["$$item.field2", 2] }
                        ]
                    }
                }
            }
        }
    }
])

Testing

---

For MongoDB versions which do not support the $filter operator, you can use a combination of set operators as:

db.mycollection.aggregate([
   {
        "$project": {
            "parentId": 1,          
            "items": {
                "$setDifference": [
                    { "$map": {
                        "input": "$items",
                        "as": "item",
                        "in": {
                            "$cond": [
                               { 
                                    "$and": [
                                        { "$eq": ["$$item.field1", 1] },
                                        { "$eq": ["$$item.field2", 2] }
                                    ]
                                },
                                "$$item",
                                false
                            ]
                        }
                    }},
                    [false]
                ]
            }
        }
    }
])

---

As a last resort, you can use the $unwind operator as the operator produces a copy of each document per array entry which uses more memory (possible memory cap on aggregation pipelines of 10% total memory) and thus also takes time to produce as well as "time" to process. You can run as:

db.mycollection.aggregate([
    { "$unwind" : "$items" },
    { "$match" : {
        "items.field1": 1,
        "items.field2": 2,
    }},
    {
        "$group": {
            "_id": "$_id",
            "parentId": { "$first": "$parentId" },
            "items": { "$push": "$items" }
        }
    }
])

which you can run in mgo as a pipeline:

pipeline := []bson.M{   
    bson.M{ "$unwind": "$items" },
    bson.M{
        "$match": bson.M{ 
            "items.field1": 1,
            "items.field2": 2
        }
    },
    bson.M{
        "$group": bson.M{
            "_id": "$_id",
            "parentId": bson.M{ "$first": "$parentId" },
            "items": bson.M{ "$push": "$items" }
        }
    }   
}
pipe := mycollection.Pipe(pipeline)
iter := pipe.Iter()

---

Testing in Robomongo