How to find array greater than document size in MongoDB

Question

I have schema like below

[
{
 id:"111"
 tags:[222,333,444,555]
},
{
 id: "222"
 tags:[312,345,534]
},
{
 id:"333"
 tags:[111,222,333,444,555]
},

]

I want to find all documents where tags array size is greater than document size returned by $match in aggregation pipeline, so in above Ex. the number of documents are 3 so i want to return all documents having tags array size greater that 3

[
{
 id:"111"
 tags:[222,333,444,555]
},
{
 id:"333"
 tags:[111,222,333,444,555]
},

]

I am using aggregation pipeline to process other info, I am stuck at how to have store document size so that i can find all tags greater than document size

below is query which i am using, i want to do it in aggregation and in one call

.aggregate([
        
   {
      "$match":{
         "ids":{
            "$in":[
               "111",
               "222",
                "333"
            ]
         }
      }
   })]

Does this answer your question? [Mongo DB aggregation array size greater than match](https://stackoverflow.com/questions/48847800/mongo-db-aggregation-array-size-greater-than-match) — Kunal Mukherjee, Nov 04 '20 at 15:06
no it does not as there is static size, in my case the size of array depends on document size returned by "match" in aggregate — Akash Salunkhe, Nov 04 '20 at 15:10
Do you mean collection size (document count), as in the number of documents in a collections? — chridam, Nov 04 '20 at 15:12
yes, the number of documents returned by $match in aggregation pipeline — Akash Salunkhe, Nov 04 '20 at 15:14

score 3 · Answer 1 · answered Nov 04 '20 at 15:16

Facet helps you to solve this problem.

$facet helps to categorize the incoming documents. We use totalDoc for counting the document and allDocuments for getting all the documents
$arrayElemAt helps to get the first object from totalDoc where we already know that only one object should be inside the totalDoc. Because when we group it, we use _id:null
$unwind helps to de-structure the allDocuments array

Here is the code

db.collection.aggregate([
  {
    $facet: {
      totalDoc: [
        {
          $group: {
            _id: null,
            count: {
              $sum: 1
            }
          }
        }
      ],
      allDocuments: [
        {
          $project: {
            tags: 1
          }
        }
      ]
    }
  },
  {
    $addFields: {
      totalDoc: {
        "$arrayElemAt": [
          "$totalDoc",
          0
        ]
      }
    }
  },
  {
    $unwind: "$allDocuments"
  },
  {
    $addFields: {
      sizeGtDoc: {
        $gt: [
          {
            $size: "$allDocuments.tags"
          },
          "$totalDoc.count"
        ]
      }
    }
  },
  {
    $match: {
      sizeGtDoc: true
    }
  },
  {
    "$replaceRoot": {
      "newRoot": "$allDocuments"
    }
  }
])

Working Mongo playground

This seems to work,but query is too large for small task, i will accept this answer if i don't get any shorter answer — Akash Salunkhe, Nov 04 '20 at 15:25

score 1 · Answer 2 · answered Nov 04 '20 at 16:01

You could use $facet to get two streams i.e. one with the filtered documents and the counts using $count. The resulting streams can then be aggregated further with a $filter as follows to get the desired result

db.getCollection('collection').aggregate([
    { '$facet': {
        'counts': [
            { '$match': { 'id': { '$in': ['111', '222', '333'] } } }, 
            { '$count': "numberOfMatches" }
        ],
        'docs': [
            { '$match': { 'id': { '$in': ['111', '222', '333'] } } }, 
        ]  
    } },
    {  '$project': {
        'result': {
            '$filter': {
                'input': '$docs',
                'cond': {
                    '$gt': [
                        { '$size': '$$this.tags' },
                        { '$arrayElemAt': ['$counts.numberOfMatches', 0] }
                    ]
                }
            }
        } 
    } }
])

turivishal · Accepted Answer · 2020-11-04T17:49:30.173

You can try,

$match you condition
$group by null and make root array of documents and get count of root documents in count
$unwind deconstruct root array
$match tags size and count greater than or not using $expr expression match
$replaceRoot to replace root object in root

db.collection.aggregate([
  { $match: { id: { $in: ["111", "222", "333"] } } },
  {
    $group: {
      _id: null,
      root: { $push: "$$ROOT" },
      count: { $sum: 1 }
    }
  },
  { $unwind: "$root" },
  { $match: { $expr: { $gt: [{ $size: "$root.tags" }, "$count"] } } },
  { $replaceRoot: { newRoot: "$root" } }
])

Playground

Second option:

first 2 stages $match and $group both are same as like above query,
$project to filter root array match condition if tags size and count greater than or not, this will return filtered root array
$unwind deconstruct root array
$replaceRoot replace root object to root

db.collection.aggregate([
  { $match: { id: { $in: ["111", "222", "333"] } } },
  {
    $group: {
      _id: null,
      root: { $push: "$$ROOT" },
      count: { $sum: 1 }
    }
  },
  {
    $project: {
      root: {
        $filter: {
          input: "$root",
          cond: { $gt: [{ $size: "$$this.tags" }, "$count"] }
        }
      }
    }
  },
  { $unwind: "$root" },
  { $replaceRoot: { newRoot: "$root" } }
])

Playground

You can skip $unwind and $replaceRoot stages if you want because this query always return one document in root, so you can easily access like this result[0]['root'], you can save 2 stages processing and execution time.

How to find array greater than document size in MongoDB

3 Answers3