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

Question

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
            }
        }
    ]

}

Do you want to iterate to next element if item's name is null or blank? Or just want to add an info for blank or null name field? — barbakini
– barbakini, Commented Sep 29, 2017 at 8:44
I want to ignore it if the field is blank or null and only return items which have a name — NZJames
– NZJames, Commented Sep 29, 2017 at 9:12

ervidio · Accepted Answer · 2017-09-29 09:58:30Z

1

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 : ""}},
      ]
    }},

edited Sep 29, 2017 at 9:58

answered Sep 29, 2017 at 9:05

ervidio

6017 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

NZJames Over a year ago

Thanks for the response. It's actually a field of the object I need to check against. So the checks needs to be 1) Items has at least one element and 2) Items[0].Details.ItemName != null or blank. So it isnt a check that Items[0] is not null or blank, its against Items[0].Details.ItemName

ervidio Over a year ago

Thank you very much for your clarification. In that case, you can do it using the operator $and in your $match. I have edited my response to include an example just in case it can help you.

Collectives™ on Stack Overflow

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

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related