Count records of statuses(approved, pending, rejected) and return values into their own individual variables

I am trying to count database records in mongoDB (using mongoose) where records have a status of pending and approved as well as rejected. So, I am basically trying to get a result where I can show a count of each and display it in my view ie:

Pending: 35
 Approved: 97
 Rejected: 12

And I want to hold these in variables that I can use in different places ie:

const pending = ....
const approved = ....
const rejected = .....

I have tried to use the aggregate function and run a loop which gets me all the data I need but I don't know how to actually get the data into the three variables like above.

    Product.aggregate([
        { $group: { _id: { status: "$status" }, totalStatus: { $sum: 1 } } }

    ])

Open in new window


The above code gives me back this (I have no rejected values yet)

[
    {
        _id: {
            status: 'pending'
        },
        totalStatus: 15
    },
    {
        _id: {
            status: 'approved'
        },
        totalStatus: 27
    }
  ]

Open in new window


I then ran a loop and restructured the data:

        .then(status => {
            for (const current of status) {
                const [status, total] = [current._id.status, current.totalStatus];
                console.log(status + ': ' + total);
            }
            next();
        })

Open in new window


That gives me:

pending: 15
approved: 27

But it still isn't what I actually want. I need to get those values into their own variables. How can I do that?
LVL 1
Black SulfurAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Zakaria AcharkiAnalyst DeveloperCommented:
You could store them in object literal like :

.then(status => {
    const result = {};
    for (const current of status) {
        result[current._id.status] = current.totalStatus;
    }
    next();
})

Open in new window


Then show them after that based in key (status) like :

console.log('Pending : ' + result.pending);
console.log('Approved : ' + result.approved);
console.log('Rejected : ' + result.rejected);

Open in new window


Else if you want really to put them to variables you could do something like :

.then(status => {
    const pending,approved,rejected;

    for (const current of status) {
        switch(status){
          case 'pending':
             pending = current.totalStatus;
             break;
          case 'approved':
             approved = current.totalStatus;
             break;
          case 'rejected':
             rejected = current.totalStatus;
             break;
        }
    }
    next();
})

Open in new window


Then show them like :

console.log('Pending : ' + pending);
console.log('Approved : ' + approved);
console.log('Rejected : ' + rejected);

Open in new window


I prefer the object option since you don't have to create variable manually and keep them empty if you don't have status in your DB.

You could show the content of the object like :

for(var status in result){
   console.log( status + ' : ' + result[status]);
}

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Black SulfurAuthor Commented:
Thanks for this answer. Is there a way to do it all in Mongoose though instead of me having to loop and use destructuring?
Zakaria AcharkiAnalyst DeveloperCommented:
As I know that is not possible if you want a specific structure you need to adjust it manually after receiving the mongoose's result.
Maximize Customer Retention with Superior Service

The IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more to help build customer satisfaction and retention.

Michael VasilevskySolutions ArchitectCommented:
You should be able to get the result directly from the db. Something like:

const pending = Product.aggregate( [
    { $match: { status: "Pending" } },
    { $group: { _id: "_id", total: { $sum: 1} } }
] );

Open in new window


See docs here
Black SulfurAuthor Commented:
Thanks Michael, that would only return the pending status and not approved and rejected though?
Michael VasilevskySolutions ArchitectCommented:
Yes you're matching on the status field = "Pending". All three constants would look like:

const pending = Product.aggregate( [
    { $match: { status: "Pending" } },
    { $group: { _id: "_id", total: { $sum: 1} } }
] );
const approved = Product.aggregate( [
    { $match: { status: "Approved" } },
    { $group: { _id: "_id", total: { $sum: 1} } }
] );
const rejected = Product.aggregate( [
    { $match: { status: "Rejected" } },
    { $group: { _id: "_id", total: { $sum: 1} } }
] );

Open in new window

Black SulfurAuthor Commented:
Got it. But that is 3 different queries now. I had wanted to do it all in one query and try extract that way so I only hit the database once as opposed to 3 times. Or is there not much difference in performance?
Michael VasilevskySolutions ArchitectCommented:
Got it, I misunderstood. So your question is really how to get the values from your results array:
[   {
        _id: {
            status: 'pending'
        },
        totalStatus: 15
    },
    {
        _id: {
            status: 'approved'
        },
        totalStatus: 27
    }  ]

Open in new window


Zakaria Acharki provided an answer on that. You could also use .filter, something like:

var result = [ {
        _id: {
            status: 'pending'
        },
        totalStatus: 15
    },
    {
        _id: {
            status: 'approved'
        },
        totalStatus: 27
} ];
const pending = result.filter(item => item._id.status === "pending")[0].totalStatus;

Open in new window


Of course you're going to have to handle instances were the status is not present, like rejected. Something like:

var rejected = 0;
if (result.filter(item => item._id.status === "rejected")) {
  rejected = result.filter(item => item._id.status === "rejected")[0].totalStatus;
}

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Node.js

From novice to tech pro — start learning today.