Question 115193 in Mongodb Question posted in
The official documentation can be found here.

mongodb if field has given value in one collection then search corresponding field in another collections and return combined values from many collec

let’s say I have 3 collections in MongoDB

  "Col1": [
    {
      "accountCode": "xyz",
      "ITSM": "SNOW",
      "anotherFieldxxx": "x"
    },
    {
      "accountCode": "abc",
      "ITSM": "SNOW",
      "anotherFieldxxx": "x"
    }
  ],
  "Col2": [
    {
      "accountCode": "xyz",
      "manager": "John",
      "anotherFieldyyy": "yyy"
    },
    {
      "accountCode": "abc",
      "manager": "Lisa",
      "anotherFieldyyy": "yyy"
    }
  ],
  "Col3": [
    {
      "accountCode": "xyz",
      "admin": "Peter",
      "anotherFieldzzz": "z"
    },
    {
      "accountCode": "abc",
      "admin": "Mona",
      "anotherFieldyyy": "yyy"
    }
  ]

In the query I want to give only one value and it’s "ITSM" : "SNOW"
If there is many documents in Col1 with "ITSM" : "SNOW" then I want to search for "accountCode" in Col2 and get "manager" value, then search for the same "accountCode" in Col3 and get "admin" value. Finally I want to get output like:

{"accountCode" : "xyz",
"ITSM" : "SNOW",
"manager" : "John",
"admin" : "Peter"},
{"accountCode" : "abc",
"ITSM" : "SNOW",
"manager" : "Paul",
"admin" : "Stephen"}

I tried with Aggregation but I am just beginner in MongoDb and finally I gave up.
Can you suggest what I should use to get my result?

—update

I have such query

db.Col1.aggregate([
    {$project: {"anotherFieldxxx": 0}},
    {$match: {"accountCode": "xyz"}},   
    {$lookup:   {   
                    from: "Col2",
                    localField: "accountCode",
                    foreignField: "accountCode",
                    as: "Col2"
     }},
      {     $unwind:    "$Col2" },
      {     $project: { "Col2.anotherFieldyyy" : 0  }},
      {     $match:     { "accountCode" : "xyz"}},
  {
    $lookup: {
                    from: "Col3",
                    localField: "accountCode",
                    foreignField: "accountCode",
                    as: "Col3"
    }},
  {$unwind: "$Col3"},
  {$project: {"Col2.anotherFieldzzz": 0}}
])Pretty()

but what if there are also documents with "accountCode" : "abc" in all collections?

Tags:

2

Answers


  1. 0 
    db.Col1.aggregate([
  {
    $project: { "anotherFieldxxx": 0 }
  },
  {
    $match: { "accountCode": "xyz" }
  },
  {
    $lookup: {
      from: "Col2",
      localField: "accountCode",
      foreignField: "accountCode",
      as: "Res2"
    }
  },
  {
    $lookup: {
      from: "Col3",
      localField: "accountCode",
      foreignField: "accountCode",
      as: "Res3"
    }
  },
  { $unwind: "$Res2" },
  { $unwind: "$Res3" },
  {
    $project: {
      accountCode: 1,
      ITSM: 1,
      manager: "$Res2.manager",
      admin: "$Res3.admin"
    }
  }
])

    Playground

    Login or Signup to reply.
  2. 0

    One option is to $match documents with your wanted value and then use there accountCode for $lookup. No need to limit the search to one specific accountCode:

    db.Col1.aggregate([
  {$match: {"ITSM": "SNOW"}},
  {$lookup: {
      from: "Col2",
      localField: "accountCode",
      foreignField: "accountCode",
      pipeline: [{$project: {manager: 1, _id: 0}}],
      as: "Res2"
  }},
  {$lookup: {
      from: "Col3",
      localField: "accountCode",
      foreignField: "accountCode",
      pipeline: [{$project: {admin: 1, _id: 0}}],
      as: "Res3"
  }},
  {$project: {
      _id: 0,
      accountCode: 1,
      ITSM: 1,
      manager: {$first: "$Res2.manager"},
      admin: {$first: "$Res3.admin"}
  }}
])

    See how it works on the playground example

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search