1

I have a database in SQL Server which is working fine.

These are my tables:

Categories table

enter image description here

Products table

enter image description here

Supplier table

enter image description here

Ratings table

enter image description here

I am using this query to get required data 

select 
    c.Id, c.CatName,
    s.Name as SupplierName,
    count(distinct(p.Id)) as ProductCount,
    r.Rating  
from 
    Categories c
inner join 
    Products p on p.CategoryId = c.Id
inner join 
    Supplier s on s.Id = p.SupplierId
inner join 
    Ratings r on r.SupplierId = s.Id
Group by 
    c.Id, c.CatName, s.Name, r.Rating

This query returns this data

enter image description here

This is exactly what I want - but in MongoDb.

I have the same structure of my mongodb collection like the SQL Server tables.

I want the same query in MongoDb...

Here is what i am trying

Catrgories.aggregate([{
     $lookup: {
        from: 'Products',
        localField: 'CategoryId',
        foreignField: 'id',
        as: 'p'
       }        
    },
    {
        $lookup: {
            from: 'Supplier',
            localField: 'SupplierId',
            foreignField: 'Id',
            as: 's'
        }      
    },  
    {
        $lookup: {
            from: 'products',          
            localField:'supplier',
            foreignField:'owner',
            as: 'prooo'
        },
    },
    {
        $lookup: {
            from: 'ratings',
            localField: 'SupplierId',
            foreignField: 'Id',
            as: 'r'
        }

    }, 
    {
        $project: {         
            "Id":"$c.Id",
            "CatName":"$c.CatName"          
        }
    },
])
Faisal
  • 584
  • 3
  • 11
  • 33
  • 1
    Well - show us that **YOU** have tried so far! Where are you stuck? Where can we help you? Remember: we'll **help** if you have a problem - but we're not a free code writing service that'll just write the whole code for you, based on your requirements.... – marc_s Nov 13 '19 at 18:59
  • 1
    MongoDB doesn't support JOINs, take a look at the $lookup (aggregation) operator (https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/). This Answer here may help you: https://stackoverflow.com/questions/4563205/how-to-join-query-in-mongodb. But more importantly, if you have relational data, use a relational (SQL) database! – Jorge Garcia Nov 14 '19 at 05:33
  • @marc_s i have posted my code which is not working for me. see if you can help now – Faisal Nov 14 '19 at 05:55

0 Answers0