Mongo aggregation, project a subfield of the first

2020-07-20 04:03发布

问题:

I have a sub collection of elements and I want to project a certain subfield of the FIRST item in this collection. I have the following but it only projects the field for ALL elements in the array.

Items is the subcollection of Orders and each Item object has a Details sub object and an ItemName below that. I want to only return the item name of the FIRST item in the list. This returns the item name of every item in the list.

How can I tweak it?

db.getCollection('Orders').aggregate
([
{ $match : { "Instructions.1" : { $exists : true }}},
{ $project: { 
    _id:0, 
    'UserId': '$User.EntityId', 
    'ItemName': '$Items.Details.ItemName'
   }
}
]);

Updated:

{
    "_id" : "order-666156",
    "State" : "ValidationFailed",
    "LastUpdated" : {
        "DateTime" : ISODate("2017-09-26T08:54:16.241Z"),
        "Ticks" : NumberLong(636420128562417375)
    },
    "SourceOrderId" : "666156",
    "User" : {
        "EntityId" : NumberLong(34450),
        "Name" : "Bill Baker",
        "Country" : "United States",
        "Region" : "North America",
        "CountryISOCode" : "US",
    },
    "Region" : null,
    "Currency" : null,
    "Items" : [ 
        {
            "ClientOrderId" : "18740113",
            "OrigClientOrderId" : "18740113",
            "Quantity" : NumberDecimal("7487.0"),
            "TransactDateTime" : {
                "DateTime" : Date(-62135596800000),
                "Ticks" : NumberLong(0)
            },
            "Text" : null,
            "LocateRequired" : false,
            "Details" : {
                "ItemName" : "Test Item 1",
                "ItemCost" : 1495.20
            }
        },
        {
            "ClientOrderId" : "18740116",
            "OrigClientOrderId" : "18740116",
            "Quantity" : NumberDecimal("241.0"),
            "TransactDateTime" : {
                "DateTime" : Date(-62135596800000),
                "Ticks" : NumberLong(0)
            },
            "Text" : null,
            "LocateRequired" : false,
            "Details" : {
                "ItemName" : "Test Item 2",
                "ItemCost" : 2152.64
            }
        }
    ]

}

回答1:

In case your are using at least MongoDB v3.2 you can use the $arrayElemAt operator for that. The below query does what you want. It will, however, not return any data for the sample you provided because the "Instructions.1": { $exists: true } filter removes the sample document.

db.getCollection('Orders').aggregate([{
    $match: {
        "Instructions.1": {
            $exists: true
        }
    }
}, {
    $project: { 
        "_id": 0, 
        "UserId": "$User.EntityId", 
        "ItemName": { $arrayElemAt: [ "$Items.Details.ItemName", 0 /* first item! */] }
    }
}])


标签: mongodb