mongodbcollectionsnosql

MongoDB update field with another field from another collection


I have two collections : books and categories. Categories collections represent a tree structure which make them nested categories using parents and children.

The book can have multiple categories and stores them in an array.

Example: Book has category and I want to retire it and set it to the parent category.

This is how categories collection is populated.

db.categories.insertMany([{
    _id: "Space Opera",
    ancestors: ["Science Fiction", "Fiction", "Science Fiction & Fantasy"],
    parent: ["Science Fiction"],
}, {
    _id: "Dystopian",
    ancestors: ["Science Fiction", "Fiction", "Science Fiction & Fantasy"],
    parent: ["Science Fiction"],
}, {
    _id: "Cyberpunk",
    ancestors: ["Science Fiction", "Fiction", "Science Fiction & Fantasy"],
    parent: ["Science Fiction"],
}, {
    _id: "Science Fiction",
    ancestors: ["Fiction", "Science Fiction & Fantasy"],
    parent: ["Fiction", "Science Fiction & Fantasy"],
}, {
    _id: "Fantasy",
    ancestors: ["Science Fiction & Fantasy"],
    parent: ["Science Fiction & Fantasy"],
}, {
    _id: "Science Fiction & Fantasy",
    ancestors: [],
    parent: [],
}, {
    _id: "Fiction",
    ancestors: [],
    parent: [],
}]);

Also, how do I query this one and get only the value "Science Fiction" (Note that it is stored in an array)?

db.categories.find({
    _id: "Space Opera"
}, {
    _id: 0, 
    parent: 1
})[0].parent // Did not work  
db.categories.find({
    _id: "Space Opera"
}, {
    _id: 0, 
    parent: 1
}) // find parent

// result 
[
  {
    "parent": [
      "Science Fiction"
    ]
  }
]
db.books.update({
    title: "Book1"
}, {
    $set: {
        category: [**PARENT CATEGORY**]
    }
})

I believe I can use code above inside books.update()

I could store this in a separate variable but in vscode it gives me undefined. And the inner query does not give me the right value as stated before, but I think you got the idea.

db.books.update({
    title: "Book1"
}, {
    $set: {
        category: [
            db.categories.find({
                _id: "Space Opera"
            }, {
                _id: 0, 
                parent: 1
            })
        ]
    }
})

Solution

  • The parent you can get with this aggregation pipeline:

    db.categories.aggregate([
       { $match: { _id: "Space Opera" } },
       { $project: { _id: 0, parent: { $first: "$parent" } } }
    ])
    

    or even

    db.categories.aggregate([
       { $match: { _id: "Space Opera" } },
       { $project: { _id: 0, parent: { $first: "$parent" } } }
    ]).toArray().shift().parent
    

    In order to join collections you have to use the $lookup operator. Bear in mind, NoSQL databases like MongoDB are not optimized for join/lookup. In real life you should have a look for a better design.

    db.books.aggregate([
       { $match: { title: "Book1" } },
       {
          $lookup:
             {
                from: "categories",
                pipeline: [
                   { $match: { _id: "Space Opera" } },
                   { $project: { _id: 0, parent: { $first: "$parent" } } }
                ],
                as: "category"
             }
       },
       { $set: { category: { $first: "$category.parent" } } }
    ])
    

    If you like to update existing collection, then you have to create a loop for it:

    db.books.aggregate([...]).forEach(function (doc) {
       db.books.updateOne({ _id: doc._id }, { $set: { category: doc.category } });
    })