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])
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.
The logical NOR operator will join two or more queries and return documents that do not match the given query conditions.
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 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.
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"]}]}})
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.
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"
}
]
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With