MongoDB: Minimum attribute match

Question

I have a MongoDB collection like this two binary strings 10110, and 01111

 { element_id:a, field_1:1, field_2:0, field_3:1, field_4:1, field_5:0} #binary string: 10110
 { element_id:b, field_1:0, field_2:1, field_3:1, field_4:1, field_5:1} #binary string: 01111

I want to query MongoDB for a candidate binary string "11111" but my minimum match(AND) value is 4 (not all 5). That means only four matching one position are required to select that doc from DB.

So the expected output will be - element_id:b

I could not find anything on MongoDB site or Google. Can anyone help?

score 2 · Accepted Answer · edited May 23 '17 at 12:28

MapReduce is a good approach as per the earlier answer from Chien-Wei. In MongoDB 2.2 you can also consider using the Aggregation Framework.

For example, if you were always matching 11111 then you could $add the values of the fields of interest and then only $match those that had at least 4:

db.element.aggregate(
    // Could use an initial $match here to find candidate documents (using indexed query)

    // Use $project to add calculated total
    { $project: {
        _id: 0,
        element_id: 1,
        // Assume we are matching 11111 and field values are always 0 or 1
        total: { $add: [ "$field_1", "$field_2", "$field_3", "$field_4", "$field_5" ] }
    }},

    // Filter to interesting results (at least 4 fields with '1')
    { $match: {
        total : { $gte : 4 }
    }}
)

Sample output:

{ "result" : [ { "element_id" : "b", "total" : 4 } ], "ok" : 1 }

If you want a more generic comparison you could use $cond to conditionally match against a target array, eg:

var targetArray = [1,1,1,1,1];
db.element.aggregate(
    // Could use an initial $match here to find candidate documents (using indexed query)

    // Use $project to add calculated total
    { $project: {
        _id: 0,
        element_id: 1,
        total: { $add: [
            { $cond:[{$eq:["$field_1", targetArray[0]]}, 1, 0 ]},
            { $cond:[{$eq:["$field_2", targetArray[1]]}, 1, 0 ]},
            { $cond:[{$eq:["$field_3", targetArray[2]]}, 1, 0 ]},
            { $cond:[{$eq:["$field_4", targetArray[3]]}, 1, 0 ]},
            { $cond:[{$eq:["$field_5", targetArray[4]]}, 1, 0 ]}
        ]}
    }},

    // Filter to interesting results (at least 4 fields with a match)
    { $match: {
        total : { $gte : 4 }
    }}
)

For a general comparison of the aggregation options and current limitations, see the related StackOverflow question: MongoDB aggregation comparison: group(), $group and MapReduce.

It may also be worth voting on & watching the feature request [SERVER-3518: Bitwise query operators](https://jira.mongodb.org/browse/SERVER-3518), as this could allow you to work with the binary strings directly. — Stennie, Nov 28 '12 at 13:16

score 1 · Answer 2 · edited Nov 28 '12 at 13:16

This is my method, a little ugly though...

I use map/reduce: ( f is your input, you have to set the array in the function)

m = function() { 
    c=0; 
    f = [1,1,1,1,1]; 
    if(f[0]==this.field_1){c++;} 
    if(f[1]==this.field_2){c++;} 
    if(f[2]==this.field_3){c++;} 
    if(f[3]==this.field_4){c++;} 
    if(f[4]==this.field_5){c++;} 
    if(c>=4){
        emit(this.element_id, c)
    }
};

r = function(key, values) { return {key:values}; };

db.test.mapReduce(m, r, {out:{inline:1}})['results']

And the result:

[ { "_id" : "b", "value" : 4 } ]

score 1 · Answer 3 · edited Nov 28 '12 at 22:35

You can programmatically build complex query with $or modifier to match all possible combinations of requested elements:

buildQuery = function(arr){
  var N, i, j, query, subquery;
  N = arr.length;
  query = {$or: []};
  for (i = 1; i <= N; ++i) {
    subquery = {};
    for (j = 1; j <= N; ++j) {
      if (i === j) continue;
      subquery['field_' + j] = arr[j-1];
    }
    query.$or.push(subquery);
  }
  return query;
}

query = buildQuery([1,1,1,1,1]);
db.collection.find(query)

buildQuery output will look like this:

{ '$or': 
   [ { field_2: 1, field_3: 1, field_4: 1, field_5: 1 },
     { field_1: 1, field_3: 1, field_4: 1, field_5: 1 },
     { field_1: 1, field_2: 1, field_4: 1, field_5: 1 },
     { field_1: 1, field_2: 1, field_3: 1, field_5: 1 },
     { field_1: 1, field_2: 1, field_3: 1, field_4: 1 } ] }

MongoDB: Minimum attribute match

3 Answers3