How to use mongodb aggregation to transform document in nested array

Question

My document looks like as shown below and I want to transform it using aggregation. Inside favourite_products I have product array and it has product_id corresponding to each shops. Now I just want product ids of all shops but sorted based on time. :

"favourite_products": [
                {
                    "shop": {
                        "shop_id": "59465888f7babb000485574b",
                        "time": "2017-07-12T06:11:19.817Z"
                    },
                    "product": [
                        {
                            "product_id": "594c2d56f7afcf00043b1195",
                            "time": "2017-07-12T06:10:36.775Z"
                        },
                        {
                            "product_id": "594ac36c76de720004e819f6",
                            "time": "2017-07-12T06:11:19.817Z"
                        }
                    ]
                },
                {
                    "shop": {
                        "shop_id": "593acc24a2902d0004211f1f",
                        "time": "2017-07-12T06:12:59.372Z"
                    },
                    "product": [
                        {
                            "product_id": "594ac36c76de720004e819f6",
                            "time": "2017-07-12T06:12:59.372Z"
                        }
                    ]
                }
            ]

I want to transform it into this:

"favourite_products"

 ["59465888f7babb000485574b",594c2d56f7afcf00043b1195","594ac36c76de720004e819f6","593acc24a2902d0004211f1f","594ac36c76de720004e819f6"]

Why would you want both shop and product id's in the same output array? Also note that your output example is not actually sorted by time. — Neil Lunn
– Neil Lunn, Commented Jul 12, 2017 at 9:02
What you did to achieve this. Show your code and ask your problem. this is not a good way to ask a question. — Shubham Verma
– Shubham Verma, Commented Jul 12, 2017 at 9:30
@NeilLunn Yes I want output ids to be time sorted. And above I just showed how my final document will look like, Pls consider it as if it time sorted. And I have put shop and product id's in the same array bcs shop id correspond to one shop and product ids correspond to products related to that shop which are favourite. — Bhupendra Singh Chauhan
– Bhupendra Singh Chauhan, Commented Jul 12, 2017 at 9:51
I really don't think you should use the aggregation framework for this at all. You simply are not "aggregating" anything, and there is no way to "sort" an array without using $unwind, and that is something you need to avoid if this really is just output "per document". It's a task better suited to client side code instead. And by "client" that means the application client to the database, as opposed to the "browser" or other eventual client. It's very easy to transform data per document without resorting to the aggregation framework. — Neil Lunn
– Neil Lunn, Commented Jul 12, 2017 at 10:01
@NeilLunn I actually want to extract product ids from one document and then fetch products corresponding to those ids as json response. So can you tell me how to do it ? — Bhupendra Singh Chauhan
– Bhupendra Singh Chauhan, Commented Jul 12, 2017 at 10:51

Pav K. · Accepted Answer · 2017-07-12 09:55:38Z

2

Below returns time ordered documents of favourite_products.product.product_id

use project if you want the result as different documents. or use group if you want the result as the array in one document.

db['testing-aggregate'].aggregate([
    {$unwind:'$favourite_products'},
    {$unwind:'$favourite_products.product'},
    {$sort:{'favourite_products.product.time':1}}, // order by time. 1=ascending | -1=descending 
    //  {$project:{
    //      '_id':0, // exclude _id from output
    //      'favourite_products':'$favourite_products.product.product_id' // return only product_id
    //  }},
    {$group:{
        _id:null,
        product_id:{$push:'$favourite_products.product.product_id'}
    }}  
])

edited Jul 12, 2017 at 9:55

answered Jul 12, 2017 at 9:40

Pav K.

2,8882 gold badges22 silver badges36 bronze badges

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

4 Comments

Bhupendra Singh Chauhan Over a year ago

@Pav.k Actually favourite_products is a part of document , so I need to match or find that document corresponding to one userid. And then extract products ids and sort by time from all shops in favourite_products array. And finally use product ids to fetch all products corresponding to those ids.

Pav K. Over a year ago

use $match{} to find the one you need. if you searching on inner array, use: {$match{'innerArray._id'='id'}}, {$unwind:'innerArray'} {$match{'innerArray._id'='id'}}

Bhupendra Singh Chauhan Over a year ago

your answer is exactly what i wanted, it worked. But can you tell why we have _id=null

Pav K. Over a year ago

When using {$group}, you have to specify the key to group by (_id:'key'). From the mongo docs on $group(aggregation): The _id field is mandatory; however, you can specify an _id value of null to calculate accumulated values for all the input documents as a whole.

Collectives™ on Stack Overflow

How to use mongodb aggregation to transform document in nested array

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related