So I’m trying to aggregate two documents matched on an id and based on the value of the first.

Document 1

{
“id”:3
“Whats for dinner”: “dinner”,
“What is for dinner tonight”: “dinner”,
“Whats for lunch”:“lunch”
}

Document 2

{
“Id”:3
“dinner” : “We are having roast!”,
“lunch” : “We are having sandwiches”
}

I’d like to start by matching the id and test if the question exists in doc1. then return the question from doc1 and the answer from doc 2 . Like

{“Whats for dinner”:“We are having roast!”}

I’ve tried:

{ “$match”: { “id”: 3, “Whats for dinner”:{"$exists":True}} },

{
    "$lookup": {
        "from": "doc 2", 
        "localField": "id", 
        "foreignField": "id", 
        "as": "qa"
    }
}

But from here I can’t figure out how to use the value from doc1 as key in doc2

It might be simple! but I’m a new to this, and just can’t get it to work!?

CodePudding user response：

Crazy data model! This would be a solution:

db.doc1.aggregate([
  { $project: { data: { $objectToArray: "$$ROOT" } } },
  { $unwind: "$data" },
  {
    $lookup: {
      from: "doc2",
      pipeline: [
        { $project: { data: { $objectToArray: "$$ROOT" } } }      
      ],
      as: "answers"
    }
  },
  {
    $set: {
      answers: {
        $first: {
          $filter: {
            input: { $first: "$answers.data" },
            cond: { $eq: [ "$$this.k", "$data.v" ] }
          }
        }
      }
    }
  },
  { $match: { answers: { $exists: true } } },
  {
    $project: {
      data: [
        {
          k: "$data.k",
          v: "$answers.v"
        }
      ]
    }
  },
  { $replaceWith: { $arrayToObject: "$data" } }
])

Mongo Playground