Mongodb how to calculate array size?

Question

I have two collection to get data from them, my first collection is posts, and second one is company, I want to list posts, and also its brief details of company (rating, totalReviews), What I have tried so far is:

db.getCollection('posts').aggregate([
    {"$match":{
        // My match conditions
    }},
    {"$lookup":{
        "from":"companies",
        "localField":"company.id",
        "foreignField":"_id",
        "as":"companyDetails"
    }},
    {"$match":{
        "companyDetails":{"$ne":[]}
    }},
    {"$project":{
      "companyDetails.totalReviews":{"$size":{"$ifNull":["$companyDetails.reviews",[]]}}}}

])

Inside company collection reviews field is an array that contain objects.

"reviews" : [ 
        {
            "description" : "Five star company",
            "userId" : ObjectId("5a82831c0b74a9276c3f826b"),
            "_id" : ObjectId("5a9e6d2c38368b365c22a49d"),
            "rating" : 4
        }, 
        {
            "description" : "The best IT solution company.",
            "userId" : ObjectId("5a827d868219d03730085803"),
            "_id" : ObjectId("5aacb5335b9f3e52dc99e1f2"),
            "rating" : 5
        }
    ],

What I want is get length of this array, for example its 2, but my query always return 1, in case of reviews existence, and 0 for not existence.

can you show me the query which return's you 1 as the array length — Kannan T
– Kannan T, Commented Mar 17, 2018 at 9:25
@Kannan Query that I posted in my question return reviews length 1 for that data, but as you see my reviews array has two object. — jones
– jones, Commented Mar 17, 2018 at 9:28

s7vr · Accepted Answer · 2018-03-18 04:22:18Z

1

You can try below project stage.

Since lookup returns array use $let to get company detail document followed by projection to output reviews.

Rest of the code stays same.

{
  "$project": {
    "companyDetails.totalReviews": {
      "$size": {
        "$ifNull": [
          {
            "$let": {
              "vars": {
                "companyDetailOne": {
                  "$arrayElemAt": [
                    "$companyDetails",
                    0
                  ]
                }
              },
              "in": "$$companyDetailOne.reviews"
            }
          },
          []
        ]
      }
    }
  }
}

edited Mar 18, 2018 at 4:22

answered Mar 17, 2018 at 14:58

s7vr

76.2k12 gold badges118 silver badges135 bronze badges

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

1 Comment

jones Over a year ago

It has a syntax error, unexpected string in "in":"$$companyDetailOne.reviews" }

Collectives™ on Stack Overflow

Mongodb how to calculate array size?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related