Select records matching concat value of two fields in mongodb

Question

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])

Asya Kamsky · Accepted Answer · 2018-02-22T20:47:41.903

33

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"]}]}})

edited Feb 22 '18 at 20:47

answered Jul 09 '14 at 19:45

Asya Kamsky

41,784
5
109
133

1

Awesome, this is almost what I need: check my edit, how can I turn the $match into a "in (array)" ? – Deleteman Jul 09 '14 at 19:59
2

nvm, found the $in operator! – Deleteman Jul 09 '14 at 20:04
Chaining in mongodb aggregate is where it's at! – tim-phillips Nov 01 '16 at 00:06
1

@AsyaKamsky - How to achieve this same functionality in C# using MongoDB.Drivers – prog1011 Feb 21 '18 at 13:52
2

Please ask C# as a separate question. – Asya Kamsky Feb 21 '18 at 16:11
Can the `$expr` solution use index? – FINDarkside Feb 08 '19 at 14:50
Like my answer says, there is no way to have index used on a computed expression. $expr would be able to use index with $eq if it was comparison with a constant. – Asya Kamsky Feb 10 '19 at 02:08
Tried to get a query to work for an hour, you're a champ, this helped solve it! – Green Cell Apr 16 '20 at 08:26
@Deleteman So how can one use the `$in` operator in this context? It needs the structure `{fieldname: {$in: [1,2,3]}}`, but in this case `fieldname` would have to be our `$concat` object, which (obviously) can't act as a key. How is this possible?? – Christallkeks Sep 17 '20 at 11:52
Use aggregation $in expression inside $expr – Asya Kamsky Sep 21 '20 at 02:46

score 7 · Answer 2 · answered Dec 02 '15 at 06:02

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.

score 1 · Answer 3 · edited Jul 10 '22 at 11:32

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"
}

]

Select records matching concat value of two fields in mongodb

3 Answers3

Linked

Related