MongoDB Lookup MainField to FieldA or FieldB

Question

I need to join two MongoDB Colletions with lookup, the MainField to join from de first collection has to join with the other collection through Field A or Field B.

MainField is an array, with this structure [Doc1.FieldA, Doc2.FieldA, Doc3.FieldB,...].
FieldA is Unique-Index.
FieldB is Non-Unique-Index, it is for group FieldB with a unique value.

The problem is that I need to keep the order of the MainField Array.

I like to do something like this:

db.getCollection("collection1").aggregate([
    $lookup: {
       from: "collection2",
       localField: "mainField",
       foreignField: $or:["fieldA","FieldB"]
       as: "mainFieldInfo"
   }]

Is it possible to do this lookup or I need a different approach?

Collections examples, the documents are simplified there are more fields in each document.

Collection Machines (1 example) :

{
    "_id" : ObjectId("5c793a188021710636865c33"),
    "MachineName" : "CER3A",
    "NextJobs" : [  //--> MainField
        "ST105862", // match with FIELD B - Flags.STS
        "OFT083520", // match with FIELD A - Lote
        "OFT083365", 
        "ST105946"
    ]
}

Collection Works (2 example, 1 to match with FieldA, 1 to match Field B):

Field A example: FieldB*(Flags.STS)* is empty

{
    "_id" : ObjectId("5c1b89d0b6e97d001816595e"),
    "Lote" : "OFT083520", //--> FIELD A 
    "Flags" : {
        "ShipsFinished" : true,
        "PlanFinished" : true,
        "Finished" : true,
        "IdDefecto" : false,
        "EstadoOF" : 4,
        "GCT" : "GCT018929",
        "PedidoVenta" : "",
        "STS" : "", //--> FIELD B
    }
}

Field B Example (2 docs): FieldA*(Lote)* is diferent in each document, FieldB*(Flags.STS)* is equal

{
    "_id" : ObjectId("5dcd78e2a2061070185400e2"),
    "Lote" : "OFT083671", //--> FIELD A
    "Flags" : {
        "B2" : 1,
        "EstadoOF" : 4,
        "Finished" : false,
        "GCT" : "GCT024270",
        "LaSI" : 0,
        "PedidoVenta" : "P056048",
        "SPO" : "PO23579",
        "STS" : "ST105862", //--> FIELD B
        "Inks" : "true",
    }
}

{
    "_id" : ObjectId("5dcd78e2a2061070185401f0"),
    "Lote" : "OFT083672", //--> FIELD A
    "Flags" : {
        "B2" : 1,
        "EstadoOF" : 4,
        "Finished" : false,
        "STS" : "ST105862", //--> FIELD B
        "ShipsFinished" : false,
        "TipoOF" : 1,
        "EstatIQC" : 1,
    }
}

Does this answer your question? [Mongodb aggregation lookup with conditions](https://stackoverflow.com/questions/48518215/mongodb-aggregation-lookup-with-conditions) — whoami - fakeFaceTrueSoul, Dec 02 '19 at 17:53

score 1 · Accepted Answer · answered Dec 02 '19 at 17:03

You have to use the other form of $lookup stage, which allow to perform multiple conditions for the lookup stage.

Here's the query you have to run :

db.machines.aggregate([
  {
    $lookup: {
      from: "works",
      let: {
        "nj": "$NextJobs"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $or: [
                {
                  $in: [
                    "$Lote",
                    "$$nj"
                  ]
                },
                {
                  $in: [
                    "$Flags.STS",
                    "$$nj"
                  ]
                }
              ]
            }
          }
        }
      ],
      as: "linkedWorks"
    }
  }
])

You can test it here

Thank you very much, it works. But I have a question, is it possible to keep the order of NextJobs Array? — Sergi Nadal, Dec 03 '19 at 10:42
By $lookup design, no, but you can find full answer here https://stackoverflow.com/questions/55033804/aggregate-lookup-does-not-return-elements-original-array-order — matthPen, Dec 03 '19 at 13:52

MongoDB Lookup MainField to FieldA or FieldB

1 Answers1