MongoDB aggregation, use value from one document as key in another

Question

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!?

Answer 1

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

Better don't use any user data as key names, you will always have to juggle with $objectToArray and $arrayToObject

Maybe consider this:

questions: { 
   guildid: 3, 
   text: [
      "Whats for dinner",
      "What is for dinner tonight",
      "Whats for lunch"
   ], 
   "nospace": 1
}