Filter out empty element documents

Question

We have a shop collection as below.
We need to return all UNIQUE 'shop' having NO batch as empty([]).

Since this is a stage in the aggregation - it would not be able to avail of index.
So we need to find the most efficient way to do the above.

{
    "_id": ObjectId("xxxxxx"),
    "shop": "Q4",
    "batch": ["5647"],
},
{
    "_id": ObjectId("xxxxxx"),
    "shop": "Q4",
    "batch": ["2314", "0121"],
},
{
    "_id": ObjectId("xxxxxx"),
    "shop": "Q1",
    "batch": ["5647"],
},
{
    "_id": ObjectId("xxxxxx"),
    "shop": "Q1",
    "batch": [],
}

Expected result : Q4

Answer 1

Try this :

db.shop.aggregate([{
    $group: {
        _id: '', shop: { $addToSet: '$shop' },
        emptyBatchShops: { $push: { $cond: [{ $eq: ['$batch', []] }, '$shop', null] } }
    }
}, { $project: { _id :0, uniques: { $setDifference: ["$shop", "$emptyBatchShops"] } } }])

Collection Data :

/* 1 */
{
    "_id" : ObjectId("5e33c4daa855df1587760621"),
    "shop" : "Q4",
    "batch" : [ 
        "5647"
    ]
}

/* 2 */
{
    "_id" : ObjectId("5e33c4daa855df1587760622"),
    "shop" : "Q4",
    "batch" : [ 
        "2314", 
        "0121"
    ]
}

/* 3 */
{
    "_id" : ObjectId("5e33c4daa855df1587760623"),
    "shop" : "Q1",
    "batch" : [ 
        "5647"
    ]
}

/* 4 */
{
    "_id" : ObjectId("5e33c4daa855df1587760624"),
    "shop" : "Q1",
    "batch" : []
}

/* 5 */
{
    "_id" : ObjectId("5e33c8fca855df1587760625"),
    "shop" : "Q5",
    "batch" : [ 
        "5647"
    ]
}

/* 6 */
{
    "_id" : ObjectId("5e33c8fca855df1587760626"),
    "shop" : "Q5",
    "batch" : [ 
        "5647"
    ]
}

/* 7 */
{
    "_id" : ObjectId("5e33c907a855df1587760627"),
    "shop" : "Q6",
    "batch" : []
}

/* 8 */
{
    "_id" : ObjectId("5e33c907a855df1587760628"),
    "shop" : "Q6",
    "batch" : [ 
        "5647"
    ]
}

/* 9 */
{
    "_id" : ObjectId("5e33c90ea855df1587760629"),
    "shop" : "Q7",
    "batch" : []
}

/* 10 */
{
    "_id" : ObjectId("5e33c90ea855df158776062a"),
    "shop" : "Q8",
    "batch" : [ 
        "5647"
    ]
}

Result :

{
    "uniques" : [ 
        "Q5", 
        "Q4", 
        "Q8"
    ]
}

Answer 2

I tried this aggregation:

db.shops.aggregate( [
  { 
      $group: { 
          _id: "$shop", 
          hasEmptyBatches: { $addToSet: { $cond: [ {$eq: [ { $size: "$batch" }, 0 ] }, true, false ] } } 
      } 
  },
  { 
      $match: { hasEmptyBatches: { $ne: true } } 
  }
] )

Answer 3

Here is the aggregate query,

Input:

{
    "_id" : ObjectId("5e33c96b08591b1809677543"),
    "shop" : "Q5",
    "batch" : [
        "5857",
        "5837"
    ]
},

{
    "_id" : ObjectId("5e33c95a08591b1809677542"),
    "shop" : "Q5",
    "batch" : [
        "5657"
    ]
},

{
    "_id" : ObjectId("5e33c93008591b1809677540"),
    "shop" : "Q1",
    "batch" : [
        "5648",
        "5628"
    ]
},

{
    "_id" : ObjectId("5e33c2fe08591b180967753f"),
    "shop" : "Q1",
    "batch" : [ ]
},

{
    "_id" : ObjectId("5e33c2fe08591b180967753e"),
    "shop" : "Q1",
    "batch" : [
        "5647"
    ]
},

{
    "_id" : ObjectId("5e33c2fe08591b180967753d"),
    "shop" : "Q4",
    "batch" : [
        "2314",
        "0121"
    ]
},

{
    "_id" : ObjectId("5e33c2fe08591b180967753c"),
    "shop" : "Q4",
    "batch" : [
        "5647"
    ]
}

Query:

const query = [
    {
        $group: {
            _id: "$shop",
            batch: { $push: "$batch" }
        }
    },
    {
        $match: {
            'batch': { $ne: [] }
        }
    },
    {
        $project: {
            _id: 1
        }
    }

];

db.trades.aggregate(query)

Result:

{
    "_id" : "Q5"
},     
{
    "_id" : "Q4"
}