MongoDB query to find on field combination of FirstName and LastName?

To query MongoDB for a field combination of FirstName and LastName, use $concat to combine the fields into a single string and $eq to check equality against your target value.

Syntax

db.collection.aggregate([
    {
        "$redact": {
            "$cond": [
                { "$eq": [
                    { "$concat": ["$FirstName", " ", "$LastName"] },
                    "Target Full Name"
                ]},
                "$$KEEP",
                "$$PRUNE"
            ]
        }
    }
])

Sample Data

db.demo502.insertMany([
    {"FirstName":"John","LastName":"Smith"},
    {"FirstName":"David","LastName":"Miller"},
    {"FirstName":"John","LastName":"Doe"}
]);

{
    "acknowledged": true,
    "insertedIds": [
        ObjectId("5e875534987b6e0e9d18f56d"),
        ObjectId("5e87553e987b6e0e9d18f56e"),
        ObjectId("5e875543987b6e0e9d18f56f")
    ]
}

Verify Sample Data

db.demo502.find();

{ "_id" : ObjectId("5e875534987b6e0e9d18f56d"), "FirstName" : "John", "LastName" : "Smith" }
{ "_id" : ObjectId("5e87553e987b6e0e9d18f56e"), "FirstName" : "David", "LastName" : "Miller" }
{ "_id" : ObjectId("5e875543987b6e0e9d18f56f"), "FirstName" : "John", "LastName" : "Doe" }

Example: Find "John Doe"

Query for documents where the combination of FirstName and LastName equals "John Doe" ?

db.demo502.aggregate([
    {
        "$redact": {
            "$cond": [
                { "$eq": [
                    { "$concat": ["$FirstName", " ", "$LastName"] },
                    "John Doe"
                ]},
                "$$KEEP",
                "$$PRUNE"
            ]
        }
    }
])

{ "_id" : ObjectId("5e875543987b6e0e9d18f56f"), "FirstName" : "John", "LastName" : "Doe" }

Key Points

• $concat joins FirstName and LastName with a space separator.
• $redact with $cond filters documents based on the concatenated string match.
• $$KEEP includes matching documents, $$PRUNE excludes non-matching ones.

Conclusion

Use MongoDB's $concat operator within an aggregation pipeline to combine multiple fields and query against the resulting string. This approach effectively searches for exact matches on field combinations.