Mongo matching only where first element of array has certain field value

user_1122909 0
Viewed 2804

I have a query below that extracts a couple of values from a large nested document. It tells me the user id and the first item name for each order.

This works fine, however I want it to only return the record where the first item's name is not null and is not blank. I can't figure out how to add a second query to the $match operator below to achieve this

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

Edited to show sample document

{
    "_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
            }
        }
    ]

}
mongodb arrays
1 Answers

You need to add the two conditions to your existing $match (not null and not blank) to check the Items as:

 $match : { "Items.1" : { $exists : true, "$ne": null,"$ne":""}

If you want to check the element Items[0].Details.ItemName you can doing using the operator $and

{ $match : { 
      $and: [ 
          {"Items.1" : { $exists : true }},
          {"Items.Details.ItemName" : { $ne : null}},
          {"Items.Details.ItemName" : { $ne : ""}},
      ]
    }},
user_44533840
Related