CODEWITHSUNDEEP
node.jsmongodbexpressmongooseaggregation-framework
lhbib hbart
lhbib hbart

Reputation: 63

"query" count posts for each category expressjs mongoose

i am new at MERN stack i tried to write this query but i can't and search a lot on google without any solution

i have to table one for posts and the second for categories

table 1 posts

--------------------------
|id   | title  |  category |
--------------------------
| 1   | title1 |    1      |
| 2   | title2 |    2      |
| 3   | title3 |    1      |
| 4   | title4 |    1      |

================= table 2 categories

---------------
|id   | name   |
---------------
| 1   |  cat1  |
| 2   |  cat2  |

==================== the results i want is this

---------------------------------
|id   | name   | number of posts |
---------------------------------
| 1   |  cat1  |        3        |
| 2   |  cat2  |        1        |

if it's can a help i write this query in mysql 

SELECT categories.*,COUNT(posts.id) AS np FROM `categories` JOIN materials ON (categories.id = posts.category) GROUP BY categories.id

and thank you

Upvotes: 4

Views: 638

Answers (1)

Ashh
Ashh

Reputation: 46481

You can try below aggregation in mongodb 3.6 and above 

db.collection.aggregate([
  { "$lookup": {
    "from": "posts",
    "let": { "id", "$id" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": ["$category", "$$id"] }}},
      { "$count": "count" }
    ],
    "as": "count"
  }},
  { "$project": {
    "name": 1,
    "numberOfPosts": { "$arrayElemAt": ["$count.count", 0] }
  }}
])

Upvotes: 2

Related Questions