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

Mongodb – Need to get for each document some info from all containing collections

Collection House:

{
    "_id": ObjectId('0001'),
    "fruit": "apple",
    "entries": [
        DBRef('room', ObjectId('1234')),
        DBRef('room', ObjectId('5678'))
    ]
}

Collection rooms:

{
    "_id": ObjectId('1234'),
    "some": "data"
}

I now want all the rooms normal output (simple find) BUT enriched with a fruit array containing all fruits of houses that contain the rooms (array because a room could be in multiple houses) like:

[
    {
        "some": "data",
        "fruits": ["apple"]
    }
]

So I tried aggregating on rooms, doing a lookup on the house, but as I need to search the array, I could not use a normal lookup but one with pipeline. Within the pipeline I would need to create some match-making BUT within that (using map) I cannot get the damn ObjectId out of the DBRef. I know, DBRef is apparently outdated but it’s the DB we have now and I need to work with.

Any help? DB is on version 4.0.22

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Playground with the "actual" solution: https://mongoplayground.net/p/HUm05wtOsMJ

    Data:

    db={
  "houses": [
    {
      "_id": 1001,
      "fruit": "apple",
      "dummy": "ignoreMe",
      "rooms": [
        {
          $ref: "rooms",
          $id: ObjectId("1bcdefbcdefbcdefbcdefbcd")
        },
        {
          $ref: "rooms",
          $id: ObjectId("2bcdefbcdefbcdefbcdefbcd")
        }
      ]
    },
    {
      "_id": 1002,
      "fruit": "pear",
      "dummy": "ignoreMe",
      "rooms": [
        {
          $ref: "rooms",
          $id: ObjectId("2bcdefbcdefbcdefbcdefbcd")
        },
        {
          $ref: "rooms",
          $id: ObjectId("3bcdefbcdefbcdefbcdefbcd")
        }
      ]
    }
  ],
  "rooms": [
    {
      "_id": ObjectId("1bcdefbcdefbcdefbcdefbcd"),
      "description": "room 1"
    },
    {
      "_id": ObjectId("2bcdefbcdefbcdefbcdefbcd"),
      "description": "room 2"
    },
    {
      "_id": ObjectId("3bcdefbcdefbcdefbcdefbcd"),
      "description": "room 3"
    }
  ]
}

    Query:

    db.rooms.aggregate([
  {
    "$lookup": {
      from: "houses",
      "let": {
        id: "$_id"
      },
      "pipeline": [
        {
          $match: {
            $expr: {
              $in: [
                "$$id",
                {
                  $map: {
                    "input": "$rooms",
                    "as": "room",
                    "in": "$$room.$id"
                  }
                }
              ]
            }
          }
        },
        {
          $project: {
            a: "$fruit"
          }
        }
      ],
      "as": "houseFruits"
    }
  }
])

    However, it does not work in my real-life example because "aggregation" does not work with "DBRef"... What it tells as error is that "fieldNames" may not contain "$"... Yeah, thanks for nothing. Apparently mapReduce needs to be used then.

    And we need to refactor this DB.


  2. 0

    When the example data is inserted with insertMany(), the object with fields { $ref: ..., $id: ... } get inserted as DBRef objects anyway (image below). And the aggregation pipeline you’ve provided in your answer works in MongoDB 5/6/7, and possibly 4.3.3 onwards.

    So it’s an issue with "DB is on version 4.0.22" rather than "does not work with DBRef".

    Workaround: Since you can’t access $-prefixed fields in Mongo 4.0.22, use the technique from this StackOverflow answer (that answer deserves an upvote!)

    1. Map each DBRef object to an array, so each room becomes an array of

    • { k: <field_name>, v: <field_value> } objects

      {
  "rooms": [
    [
      { "k": "$ref", "v": "rooms" },
      { "k": "$id", "v": { "$oid": "2bcdefbcdefbcdefbcdefbcd" } }
    ],
    [
      { "k": "$ref", "v": "rooms" },
      { "k": "$id", "v": { "$oid":  "3bcdefbcdefbcdefbcdefbcd" } }
    ]
  ]
}

    • Note that the $ref field is always the 1st object (index 0) in the array and $id is always the 2nd (index 1).

    • If $db existed, it would be the 3rd.

    • Here’s the single demo-stage to do and Mongo Playground:

      db.houses.aggregate([
  {
    $addFields: {
      rooms: {
        $map: {
          input: "$rooms",
          as: "room",
          in: { $objectToArray: "$$room" }
        }
      }
    }
  }
])

    2. Re-add those fields Map that 2nd value $id (index 1) to a field you can lookup, with a non-$-name:

    • Single-demo stage & playground: 
      db.houses.aggregate([
  {
    $addFields: {
      rooms: {
        $map: {
          input: "$rooms",
          as: "room",
          in: { $arrayElemAt: [{ $objectToArray: "$$room" }, 1] }
        }
      }
    }
  }
])

    3. Use that as the pipeline in your rooms aggregation $lookup stage and match each room’s id with "$rooms.v":

    db.rooms.aggregate([
  {
    $lookup: {
      from: "houses",
      let: { room_id: "$_id" },
      pipeline: [
        {
          $addFields: {
            rooms: {
              $map: {
                input: "$rooms",
                as: "room",
                in: { $arrayElemAt: [{ $objectToArray: "$$room" }, 1] }
              }
            }
          }
        },
        {
          $match: {
            $expr: { $in: ["$$room_id", "$rooms.v"] }
          }
        },
        {
          $project: {
            a: "$fruit"
          }
        }
      ],
      as: "houseFruits"
    }
  }
])

    Final Mongo Playground working MongoDB 4.0.22

    Edit: Btw, once you upgrade to at least MongoDB 5.0, then using the $lookup Correlated Subqueries Using Concise Syntax, this is what the full aggregation can be simplified to:

    db.rooms.aggregate([
  {
    $lookup: {
      from: "houses",
      localField: "_id",
      foreignField: "rooms.$id",
      pipeline: [
        { $project: { a: "$fruit" } }
      ],
      as: "houseFruits"
    }
  }
])

    Mongo Playground for 5.0 onwards.

    Example data inserted with DBRef objects:

    $ref $id fields as DBRef

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

Back To Top
Search