Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Select records matching concat value of two fields in mongodb

Tags:

concat

mongodb

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])
like image 867
Deleteman Avatar asked Jul 09 '14 19:07

Deleteman


People also ask

How do I concatenate strings in MongoDB?

Concatenates strings and returns the concatenated string. $concat has the following syntax: { $concat: [ <expression1>, <expression2>, ... ] } The arguments can be any valid expression as long as they resolve to strings.

Which operator is used Combine 2 expressions in MongoDB?

The logical NOR operator will join two or more queries and return documents that do not match the given query conditions.

Can we use $match in Find MongoDB?

Basically, MongoDB provides the different match operators such as $match and $count, etc, to the user and we can utilize them as per our requirement. We can also use a match operator for the aggregation pipeline.

What is aggregation query in MongoDB?

What is Aggregation in MongoDB? Aggregation is a way of processing a large number of documents in a collection by means of passing them through different stages. The stages make up what is known as a pipeline. The stages in a pipeline can filter, sort, group, reshape and modify documents that pass through the pipeline.


3 Answers

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"]}]}})
like image 66
Asya Kamsky Avatar answered Oct 22 '22 02:10

Asya Kamsky


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.

like image 28
Sridhar Avatar answered Oct 22 '22 01:10

Sridhar


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

]

like image 24
Ali Abbas Avatar answered Oct 22 '22 01:10

Ali Abbas