Mongo DB aggregation to convert books to array of authors

Question

My book collection looks like this:

{
    "book": {
        "title": "Book1",
        "author": "Tom"
    },
    "quantity": 3
},
{

    "book": {
        "title": "Book2",
        "author": "Tom"
    },
    "quantity": 4
},
{

    "book": {
        "title": "Book3",
        "author": "Dick"
    },
    "quantity": 9
},
{

    "book": {
        "title": "Book4",
        "author": "Harry"
    },
    "quantity": 6
},
{

    "book": {
        "title": "Book5",
        "author": "Chris"
    },
    "quantity": 7
},
{

    "book": {
        "title": "Book6",
        "author": "Dick"
    },
    "quantity": 10
}

This collection has book documents with title, author and quantity.

I want help in coming up with aggregation which would output the array of authors with their books and quantity. Example - Tom has authored "Book1" and "Book2", "Dick" has authored "Book6" and "Book3".

authors: [
    {
        "name": "Tom",
        "books": [
            {
                    "title": "Book1",
                    "quantity": "3"
            },
            {
                    "title": "Book2",
                    "quantity": "4"
            }
        ]
    },
    {
        "name": "Dick",
        "books": [
            {
                    "title": "Book6",
                    "quantity": "10"
            },
            {
                    "title": "Book3",
                    "quantity": "9"
            }
        ]
    },
    {
        "name": "Harry",
        "books": [
            {
                    "title": "Book4",
                    "quantity": "6"
            }
        ]
    }
]

Anuj Pancholi · Accepted Answer · 2020-02-21 07:23:02Z

1

Try this aggregation:

db.collection.aggregate([{
$group: {
  _id: "$book.author",
  books: {
    $push: {
      title: "$book.title",
      quantity: "$quantity"
    }
  }
}},{
$project: {
  "name": "$_id",
  "books": 1,
  "_id": 0
}}])

I tried it on mongo playground: https://mongoplayground.net/p/m-QbX-uzkkt

answered Feb 21, 2020 at 7:23

Anuj Pancholi

1,22311 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jitendra · Accepted Answer · 2020-02-21 07:14:56Z

Try as below:

db.collection.aggregate([
    {
        $group: {
            _id: "$book.author",
            books: { $push: { "title": "$book.title", "quantity": "$quantity"} }
        },

    }
])

Output will be

/* 1 */
{
    "_id" : "Chris",
    "books" : [
        {
            "title" : "Book5",
            "quantity" : 7
        }
    ]
},

/* 2 */
{
    "_id" : "Harry",
    "books" : [
        {
            "title" : "Book4",
            "quantity" : 6
        }
    ]
},

/* 3 */
{
    "_id" : "Dick",
    "books" : [
        {
            "title" : "Book3",
            "quantity" : 9
        },
        {
            "title" : "Book6",
            "quantity" : 10
        }
    ]
},

/* 4 */
{
    "_id" : "Tom",
    "books" : [
        {
            "title" : "Book1",
            "quantity" : 3
        },
        {
            "title" : "Book2",
            "quantity" : 4
        }
    ]
}

Collectives™ on Stack Overflow

Mongo DB aggregation to convert books to array of authors

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related