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

Mongodb – Mongo query for simple tree with only parent and first-level children using aggregation pipeline

Assume I have a collection, things, with the following documents:

[
  {
    "_id": 1,
    "name": "one",
    "others": [2, 3]
  },
  {
    "_id": 2,
    "name": "two"
  },
  {
    "_id": 3,
    "name": "three"
  },
  {
    "_id": 4,
    "name": "four"
  }
]

As you can see, the first document forms a tree by virtue of its others array, which contains the ids of other documents that are the first document’s children.

I’m trying to write a query that returns a flat list of documents where the first one is the parent and the rest are documents whose ids are in the first document’s others array. I do not need to recurse; I only need the immediate children. For this example, the resultant array for this query, passing a value of 1, would just be

[
  {
    "_id": 1,
    "name": "one",
    "others": [2, 3]
  },
  {
    "_id": 2,
    "name": "two"
  },
  {
    "_id": 3,
    "name": "three"
  }
]

Seems pretty straightforward, but I’m just not sure of the syntax.

I’ve tried

db.things.aggregate([{
  $match: { _id: 1 }
}, {
  $match: { _id: { $in: '$others'}}
}
])

This fails with the message $in needs an array, which, of course, it does. What’s the correct syntax here to get a list of documents whose first element is the parent, and whose other elements are the first-level children?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Once I changed the collection name to things and reordered, this worked well. Thanks, @ray! Here's my final query:

    db.things.aggregate([
  {
    "$match": {
      "_id": 1
    }
  },
  {
    "$unionWith": {
      "coll": "things",
      "pipeline": [
        {
          "$match": {
            "_id": 1
          }
        },
        {
          "$lookup": {
            "from": "things",
            "localField": "others",
            "foreignField": "_id",
            "as": "othersLookup"
          }
        },
        {
          "$unwind": "$othersLookup"
        },
        {
          "$replaceRoot": {
            "newRoot": "$othersLookup"
          }
        }
      ]
    }
  }
])

    Results:

    [
  {
    "_id": 1,
    "name": "one",
    "others": [2, 3]
  },
  {
    "_id": 2,
    "name": "two",
    "others": [5]
  },
  {
    "_id": 3,
    "name": "three"
  }
]

  2. 0

    One way to do so would be performing a self lookup then $unionWith the root record(id : 1)

    db.collection.aggregate([
  {
    "$match": {
      "_id": 1
    }
  },
  {
    "$lookup": {
      "from": "collection",
      "localField": "others",
      "foreignField": "_id",
      "as": "othersLookup"
    }
  },
  {
    "$unwind": "$othersLookup"
  },
  {
    "$replaceRoot": {
      "newRoot": "$othersLookup"
    }
  },
  {
    "$unionWith": {
      "coll": "collection",
      "pipeline": [
        {
          "$match": {
            "_id": 1
          }
        }
      ]
    }
  }
])

    Mongo Playground

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

Back To Top
Search