MondoDB aggregation - remapping inner documents

Question

How could I remap inner documents like this in the following example:

db.customers.aggregate(
[
    {
        $project: {
            "_id": 0,
            "First Name": "customerDetails.firstName",
            "Last Name": "customerDetails.lastName",
            "Full Name": { $concat : [ "customerDetails.firstName", " ", "customerDetails.lastName" ] }
        }
    }
]

)

Placing the $ like this "$customer.firstName" doesn't work and instead of taking the value of the inner field firstName basically it doesn't return anything.

Example of the structure of customer collection:

{
    "_id" : ObjectId("5fb3c41454742e0d3c9f7605"),
    "customerDetails" : {
        "firstName" : "Robert",
        "lastName" : "Green",
        "phoneNumber" : "0878712375",
        "email" : "robert.green@gmail.com"
    }
}

Expected result:

{
    "First Name": "Robert",
    "Last Name": "Green",
    "Full Name": "Robert Green"
}

Actual result:

{
    "First Name": "customerDetails.firstName",
    "Last Name": "customerDetails.lastName",
    "Full Name": "customerDetails.firstName customerDetails.lastName"
}

would be great if you can give sample of your documents and the current and expected output :) — ray, Dec 17 '20 at 09:29
Of course `$customer.firstName` does not work if the field is called `customer**Details**` — Wernfried Domscheit, Dec 17 '20 at 09:51

score 2 · Accepted Answer · answered Dec 17 '20 at 09:47

2

Reference the fields with $, should work:

db.customers.aggregate(
[
    {
        $project: {
            "_id": 0,
            "First Name": "$customerDetails.firstName",
            "Last Name": "$customerDetails.lastName",
            "Full Name": { $concat : [ "$customerDetails.firstName", " ", "$customerDetails.lastName" ] }
        }
    }
]

answered Dec 17 '20 at 09:47

Wernfried Domscheit

54,457
9
76
110

Again, what do you mean by "will not work"? – Wernfried Domscheit Dec 17 '20 at 10:10
Sorry - my mistake. It is working. I was looking on the records where these fields are not present and have wrongly assumed that it is not working. It is quite normal to be missing if they are not present. – nenito Dec 17 '20 at 12:07

MondoDB aggregation - remapping inner documents

1 Answers1