0

I'm trying to fetch data from 3 collection by embedded relation in mongodb.

user_collection :

{
    _id:1,
    name:"Noah",
    city_id:2,
    job_id:3
},
{
    _id:2,
    name:"Oliver",
    city_id:2,
    job_id:1
},
{
    _id:3,
    name:"William",
    city_id:1,
    job_id:3
},

city_collection :

{
    _id:1,
    name:"Tokyo",

},
{
    _id:2,
    name:"Mexico City",

},
{
    _id:3,
    name:"Los Angeles",

},

job_collection :

{
    _id:1,
    title:"Architect",
},
{
    _id:2,
    title:"Barista",
},
{
    _id:3,
    title:"Farmer",
},

i have this 3 collection and want to list my user by city and set theirs job in user part of returned JSON

i want to fetch a nested JSON.

city_collection to user_collection to job_collection

like this :

[
    {
        _id:1,
        name:"Tokyo",
        users:[{
            _id:3,
            name:"William",
            job:[{
                _id:1,
                title:"Architect",
            }]
        }]
    
    },
    {
        _id:2,
        name:"Mexico City",
        users:[{
            _id:1,
            name:"Noah",
            job:[{
                _id:3,
                title:"Farmer",
            }]
        },
        {
            _id:2,
            name:"Oliver",
            job:[{
                _id:1,
                title:"Architect",
            }]
        }]
    
    },
    {
        _id:3,
        name:"Los Angeles",
        users:[]
    
    },
]

i use $lookup but it did not return what i want.

farzam
  • 35
  • 3

1 Answers1

2

Use double $lookup

db.city.aggregate([
  {
    $lookup: {
      from: "user",
      localField: "_id",
      foreignField: "city_id",
      as: "users",
      pipeline: [
        {
          $lookup: {
            from: "job",
            localField: "job_id",
            foreignField: "_id",
            as: "jobs"
          }
        }
      ]
    }
  }
])

mongoplayground

YuTing
  • 6,555
  • 2
  • 6
  • 16