Select records matching concat value of two fields

问题:

Is there a way to do something like this on MongoDB?

select * from table where concat(field1, field2) = 'value'

To clarify, I have an array of full names, but the documents have firstname and lastname separate, so I want to do something like

select * from table where concat(firstname, lastname) in ([ARRAY OF NAMES])

回答1:

You can only do it with aggregation framework, not with regular find.

db.coll.aggregate({$project:{newField:{$concat:["$field1","$field2"]}}},
                  {$match:{newField:"value"}} 
);

Note that this will not be able to use any indexes, since there is no support for indexes on computed values in MongoDB (yet).

If you have an index on field1 and you know how many characters you expect field1 to contribute to value you can improve performance of this aggregation like this:

db.coll.aggregate({$match:{field1:/^val/}},
                  {$project:{newField:{$concat:["$field1","$field2"]}}},
                  {$match:{newField:"value"}} 
);

where val is first part of "value" string (you must not compare more characters than the shortest possible value of field1 though.

EDIT as of version 3.6 you can do this in find using the $expr expression:

db.coll.find({$expr:{$eq:["value", {$concat:["$field1", "$field2"]}]}})

回答2:

If the argument ($field1 or $field2) resolves to a value of null or refers to a field that is missing, $concat returns null. Since mongoDB3.0, a new $ifNull is provided to check if the argument is null and provide an alternative

{ $ifNull: [ <expression>, <replacement-expression-if-null> ] }

db.coll.aggregate({$project:{newField:{$concat:[{$ifNull:["$field1",""]},{$ifNull:["$field2",""]}]}}},
              {$match:{newField:"value"}})

The results may not differ if we are using $match, but this results will vary if you want to use $regex.

回答3:

In Mongoose you can like that

let clause =  [                                               
            {$project:{name:{$concat["$first_name"," ","$last_name"]}}},
            {$match:{name:new RegExp("ab", 'i')}}                       
         ];                                                                             
Model.aggregate(clause)

OutPut will be like that

[
{
    "_id": "5911db2b9235272052fdd2e1",
    "name": "Ali Abbas"
},
{
    "_id": "5912f7dd8bdbc24aa37b5239",
    "name": "Ali Abad"
},
{
    "_id": "59229e0322abdb311818e419",
    "name": "Syed Ali Abbas Shah"
},
{
    "_id": "592837da188fa969cc135ddc",
    "name": "Abu Akbar"
}

]