$lookup with two $concat fields

Question

Given those documents:

{
    "name" : "User",
    "class" : "clazz",
    "reference" : "ref"
}

{
    "classReference" : "clazz+ref",
    "room" : "123"
}

How can I do a $lookup to retrieve a document like this (without changing the documents):

{
    "name" : "User",
    "class" : "clazz",
    "reference" : "ref",
    "classReference" : "clazz+ref",
    "room" : "123"
}

Both concatenated (poor symbol choice). Edited the question to better reflect my desire. — Felipe, Nov 30 '18 at 10:02
the field classReference is the concatenation of both class and reference of the first document. In SQL I would get something like `SELECT * FROM user, room WHERE room.classReference = CONCAT(user.name, user.class)` — Felipe, Nov 30 '18 at 10:06

Ashh · Accepted Answer · 2018-11-30T10:35:46.980

You can use below aggregation with mongodb 3.6 and above

db.user.aggregate([
  { "$lookup": {
    "from": Room.collection.name,
    "let": { "ref": { "$concat": ["$class", "$reference"] } },
    "pipeline": [
      { "$match": { "$expr": { "$eq": ["$classReference", "$$ref"] } } }
    ],
    "as": "ref"
  }},
  { "$project": {
    "classReference": { "$arrayElemAt": ["$ref.classReference", 0] },
    "room": { "$arrayElemAt": ["$ref.room", 0] },
    "name": 1,
    "class": 1
    "reference": 1
  }}
])

Basically you need to $concat both the class and reference and then pass it to $lookup pipeline.

$lookup with two $concat fields

1 Answers1