cartoonheart91
cartoonheart91

Reputation: 75

MongoDB compare document fields in aggregation pipeline

I have a collection of documents like the following:

{ name : "John" , 
  age : 25.0 , 
  bornIn : "Milan" , 
  city : [ { 
        name : "Roma" , 
        state : "IT" , 
        mayor : "John"
        }]
 }
{ name : "Jim" , 
  age : 35.0 , 
  bornIn : "Madrid" , 
  city : [ { 
        name : "Madrid" , 
        state : "ESP" , 
        mayor : "Jim"
        }]
 }

I want to retrieve all the documents that have the field $bornIn equal to the field $city.name. I need to do this as an intermediate stage of a pipeline, so I can't use the $where operator.

I searched online and I found a suggestion to implement something like this:

 { $project:
       { matches:
            { $eq:[ '$bornIn', '$city.name' ] }
       } 
  }, 
  { $match:
        { matches:true }

  } ) 

But it didn't work neither via shell nor via Java driver as it marks the fields as different. For the sake of completeness I report my code:

final DBObject eq = new BasicDBObject();
LinkedList eqFields = new LinkedList();
eqFields.add("$bornIn");
eqFields.add("$city.name");
eq.put("$eq", eqFields);
projectFields.put("matches", eq);
final DBObject proj = new BasicDBObject("$project", projectFields);
LinkedList agg = new LinkedList();
agg.add(proj);
final AggregationOutput aggregate = table.aggregate( agg);

Do you have any suggestion? I'm using MongoDB 3.2, and I need to do this via Java Driver.

Thanks!!

PS. It is not relevant but actually the documents above are the output of a $lookup stage among collections "cities" and "persons", with join on $name/$mayor.. it is super cool!! :D :D

Upvotes: 0

Views: 627

Answers (1)

I'm a little rusty on how Mongo deals with deep equality searching arrays of objects, but this is definitely doable with $unwind

db.foo.aggregate([
  {$unwind: "$city"},
  { $project:
       { matches:
            { $eq:[ '$bornIn', '$city.name' ] }
       } 
  }, 
  { $match:
        { matches:true }

  }
]);

I'm not on a computer with Mongo right now, so my syntax might be off a bit.

Upvotes: 1

Related Questions