Inner array total count in mongodb

Question

I have a journal doc. Which contains details of journal_volumes, journal issues and journal articles. I have to list the journals along with the count of volumes, issues and articles in each journal.

Here is my doc:

{
"_id" : ObjectId("5c470fc3135edb4413b0ea24"),
"jnl_code" : "KEG",
"jnl_volumes" : [ 
    {
        "name" : "1",
        "created_date" : "2019-03-01",
        "status" : "0",
        "issue_flag" : "0",
        "jnl_issues" : [ 
            {
                "issue_name" : "1",
                "created_date" : "2019-03-04",
                "jnl_articles" : [ 
                    "test", 
                    "test2"
                ]
            }, 
            {
                "issue_name" : "2",
                "created_date" : "2019-03-04",
                "jnl_articles" : [ 
                    "a"
                ]
            }, 
            {
                "issue_name" : "3",
                "created_date" : "2019-03-04",
                "jnl_articles" : [ 
                    "b"
                ]
            }, 
            {
                "issue_name" : "3",
                "created_date" : "2019-03-05",
                "jnl_articles" : [ 
                    "Q"
                ]
            }
        ]
    }, 
    {
        "name" : "2",
        "created_date" : "2019-03-01",
        "status" : "0",
        "issue_flag" : "0",
        "jnl_issues" : [ 
            {
                "issue_name" : "1",
                "created_date" : "2019-03-05",
                "jnl_articles" : [ 
                    "W"
                ]
            }, 
            {
                "issue_name" : "1",
                "created_date" : "2019-03-05",
                "jnl_articles" : [ 
                    "S"
                ]
            }, 
            {
                "issue_name" : "1",
                "created_date" : "2019-03-05",
                "jnl_articles" : [ 
                    "R"
                ]
            }, 
            {
                "issue_name" : "1",
                "created_date" : "2019-03-05",
                "jnl_articles" : [ 
                    "R"
                ]
            }
        ]
    }, 
    {
        "name" : "3",
        "created_date" : "2019-03-05",
        "status" : "0",
        "issue_flag" : "0"
    }
]

}

My requirement is to get the count of jnl_volumes, total jnl_issues count and total jnl_articles count in single query..

Thanks to Neil Lunn to redirect me to the similar question (Calculate the count of nested objects with C# ). I referred the answer and wrote a query:

db.getCollection('rvh_journals').aggregate([
{
    $project: {
        "volumes" : { "$size" : { "$ifNull" : [ "$jnl_volumes", [] ] } },
        "issues" : {  
            "$sum" : {
                "$map" : {
                    "input" : "$jnl_volumes",
                    "in": { "$size" : { "$ifNull" : [ "$$this.jnl_issues", [] ] } }
                }
            }
        },
        "articles" : {
            "$sum" : {
                "$map" : {
                    "input" : "$jnl_volumes.jnl_issues.jnl_articles",
                    "in" : { "$size" : { "$ifNull" : [ "$$this", [] ] } }
                }
            }
        }
    }
}

])

This returns with an incorrect article count. Actual article count is 9 but the query returns 8

{
"_id" : ObjectId("5c470fc3135edb4413b0ea24"),
"volumes" : 3,
"issues" : 8,
"articles" : 8

}

Neil Lunn · Accepted Answer · 2019-03-05T12:19:54.963

Yes I got curious after your edit to the previous question, and noticed your statement was incorrect.

This one is correct:

db.getCollection('rvh_journals').aggregate([
  { "$project": {
    "volumes": { "$size":  "$jnl_volumes" },
    "issues": { 
      "$sum": {
        "$map": {
          "input": "$jnl_volumes",
          "in": { "$size": { "$ifNull": ["$$this.jnl_issues", [] ] } }
        }
      }
    },
    "articles": {
      "$sum": {
        "$map": {
          "input": "$jnl_volumes",
          "in": {
            "$sum": {
              "$map": {
                "input": { "$ifNull": [ "$$this.jnl_issues", [] ] },
                "in": { "$size": { "$ifNull": [ "$$this.jnl_articles", [] ] } }
              }
            }
          }
        }
      }
    }
  }}
])

Returns:

{
        "_id" : ObjectId("5c470fc3135edb4413b0ea24"),
        "volumes" : 3,
        "issues" : 8,
        "articles" : 9
}

Note the traversal of the arrays.

You might go and read some of the actual words I used on that original linked answer, because I would have explained that nesting arrays like this is not a good idea. More details on why it's not a good idea and practical approaches to take otherwise are on Updating a Nested Array with MongoDB

score 0 · Answer 2 · answered May 29 '20 at 14:14

0

enter image description here

.itcount() - is showing distinct count. Need to show total count of count variable which is declare in $group Currently it is showing based on company.

answered May 29 '20 at 14:14

Parmod Prajapati

3
2

Inner array total count in mongodb

2 Answers2