MongoDB: Select element from array based on another property in the document

Question

I have a MongoDB collection with documents of the following structure (non-interesting bits left out):

{
  displayFieldId: "abcd",
  fields: [
    {
      fieldId: "efgh",
      value: "cake"
    },
    {
      fieldId: "abcd",
      value: "cheese"
    },
    ....
  ],
  ....
}

I would like to run a query on this collection to fetch only the element in the fields array which fieldId matches the document's displayFieldId. The result of the query on the document above should thus be:

{
  fields: [
    {
      fieldId: "abcd",
      value: "cheese"
    }
  ],
  ....
}

I constructed the following query. It does what I want, with the exception that the displayFieldValue is hard coded

db.containers.find({}, {
  fields: {
    $elemMatch: {
      fieldId: "abcd"
    }
  }
});

Is there a way to make it look at the document's displayFieldId and use that value instead of the hard coded "abcd"?

The server is running MongoDB 3.2.6

If possible, I would like to do this without aggregation, but if that can't be done, then aggregation will have to do

chridam · Accepted Answer · 2017-01-19 17:41:12Z

With aggregation framework:

db.containers.aggregate([
    {        
        "$redact": { 
            "$cond": [
                { 
                    "$anyElementTrue": [ 
                        {
                            "$map": {
                                "input": "$fields",
                                "as": "el",
                                "in": { 
                                    "$eq": ["$$el.fieldId", "$displayFieldId"]
                                }
                            }
                        }
                    ] 
                },
                "$$KEEP",
                "$$PRUNE"
            ]
        }
    },
    {
        "$project": {
            "displayFieldId": 1,
            "fields": {
                "$filter": {
                    "input": "$fields",
                    "as": "el",
                    "cond": {
                        "$eq": ["$$el.fieldId", "$displayFieldId"]
                    }
                }
            },
            "otherfields": 1,
            ....
        }
    }
])

MongoDB 3.4:

db.containers.aggregate([
    {        
        "$redact": { 
            "$cond": [
                { 
                    "$anyElementTrue": [ 
                        {
                            "$map": {
                                "input": "$fields",
                                "as": "el",
                                "in": { 
                                    "$eq": ["$$el.fieldId", "$displayFieldId"]
                                }
                            }
                        }
                    ] 
                },
                "$$KEEP",
                "$$PRUNE"
            ]
        }
    },
    {
        "$addFields": {
            "fields": {
                "$filter": {
                    "input": "$fields",
                    "as": "el",
                    "cond": {
                        "$eq": ["$$el.fieldId", "$displayFieldId"]
                    }
                }
            }
        }
    }
])

Without aggregation framework - using $where (the slow query):

db.containers.find({
    "$where": function() {
        var self = this;
        return this.fields.filter(function(f){
            return self.displayFieldId === f.fieldId;
        }).length > 0;
    }
}).map(function(doc){
    var obj = doc;
    obj.fields = obj.fields.filter(function(f){
        return obj.displayFieldId === f.fieldId;
    });
    return obj;
})

I notice I forgot to mention that I would like to return ALL documents, and not filter out those that do not have the display field. Those without it should return an empty fields array. I managed to do this by removing the $redact part of the aggregation query, and the $where part of the find query. Although, at least in the find query, this is no different from returning the whole database and process it on the client, is it? Looks like I have to take another approach

Collectives™ on Stack Overflow

MongoDB: Select element from array based on another property in the document

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related