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

Mongodb – How to use graphLookup to get the leaves of a tree-like data spread over 3 documents?

I have the following MongoDB collections:

items:
  _id: ObjectId

boxes: 
  _id: ObjectId
  items: ObjectId[] 
  boxes: ObjectId[]

containers:
  _id: ObjectId
  boxes: ObjectId[]

The items can be directly contained in boxes, and boxes can contain other boxes indefinitely deep.

I am trying to write a query that finds all items, both directly and indirectly contained, within boxes that are inside containers.

By directly contained, I mean an item is in a box. Indirectly contained means an item is in a box that is itself in another box, and this can go indefinitely deep.

This is the query I have tried:

db.containers.aggregate([
  {
    $graphLookup: {
      from: "boxes",  
      startWith: "$boxes",
      connectFromField: "boxes", 
      connectToField: "_id",
      as: "allBoxes"
    }
  },
  {
   $graphLookup: {
     from: "items",
     startWith: "$allBoxes.items",
     connectFromField: "items",
     connectToField: "_id",
     as: "result"
   }
  }
])

However, this returns an empty array for the "result" field. I would appreciate if anyone could help fix this query.

[EDIT] Here’s a sample data:

db={
  "items": [
    {
      "_id": 1
    },
    {
      "_id": 2
    },
    {
      "_id": 3
    },
    {
      "_id": 4
    },
    {
      "_id": 5
    },
    {
      "_id": 6
    },
    {
      "_id": 7
    },
    {
      "_id": 8,
      name: "eighth"
    }
  ],
  "boxes": [
    {
      "_id": 1,
      "items": [
        1,
        2
      ],
      "boxes": [
        2
      ]
    },
    {
      "_id": 2,
      "items": [
        3,
        4
      ],
      "boxes": [
        3
      ]
    },
    {
      "_id": 3,
      "items": [],
      "boxes": [
        4
      ]
    },
    {
      "_id": 4,
      "items": [
        5,
        6
      ],
      "boxes": []
    },
    {
      "_id": 5,
      "items": [],
      "boxes": [
        6
      ]
    },
    {
      "_id": 6,
      items: [
        8
      ]
    },
    {
      "_id": 7
    },
    {
      "_id": 8
    },
    {
      "_id": 9
    },
    {
      "_id": 10
    }
  ],
  "containers": [
    {
      "_id": 1,
      "boxes": [
        1,
        10
      ]
    },
    {
      "_id": 2,
      "boxes": [
        5,
        9
      ]
    },
    {
      "_id": 3
    }
  ]
}
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I had to use $lookup instead of the second $graphLookup:

    [
  {
    $graphLookup: {
      from: "boxes",
      startWith: "$boxes",
      connectFromField: "boxes",
      connectToField: "_id",
      as: "allBoxes"
    }
  },
  {
    $unwind: "$allBoxes"
  },
  {
    $unwind: "$allBoxes.items"
  },
  {
    $project: {
      "item": "$allBoxes.items"
    }
  },
  {
    $group: {
      "_id": null,
      "containedItemIds": {
        $addToSet: "$item"
      }
    }
  },
  {
    $lookup: {
      from: "items",
      localField: "containedItemIds",
      foreignField: "_id",
      as: "contentItems"
    }
  }
]

    Following is the result of the query for the provided data sample:

    [
  {
    "_id": null,
    "containedItemIds": [
      2,
      5,
      3,
      8,
      1,
      4,
      6
    ],
    "contentItems": [
      {
        "_id": 1
      },
      {
        "_id": 2
      },
      {
        "_id": 3
      },
      {
        "_id": 4
      },
      {
        "_id": 5
      },
      {
        "_id": 6
      },
      {
        "_id": 8,
        "name": "eighth"
      }
    ]
  }
]

  2. 0

    This assumes you are storing child _id in the parent’s array field. (i.e. if box A contains Box B, Box A’s boxes field contains "B". The same applies to containers, boxes)

    I think you just need use 1 $graphLookup to recursively lookup the boxes collection. Then, iterate through the lookup result with $reduce to get all items.

    db.containers.aggregate([
  {
    "$graphLookup": {
      "from": "boxes",
      "startWith": "$boxes",
      "connectFromField": "boxes",
      "connectToField": "_id",
      "as": "insideBoxes"
    }
  },
  {
    "$project": {
      "allItems": {
        "$reduce": {
          "input": "$insideBoxes",
          "initialValue": [],
          "in": {
            "$setUnion": [
              "$$value",
              "$$this.items"
            ]
          }
        }
      }
    }
  }
])

    Mongo Playground

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

Back To Top
Search