如果嵌入存在就放松

Question

I have highly nested mongodb set of objects, so my collection is:

{
   "_id":17846384es,
   "company_name":"company1",
   "company_location":"city1",
    "phone":"xxxxx",
   "open_time":"xxx",
   "products":[
       {
         "product_id":"123785",
         "product_name":"product1",
         "user_votes":[
              {
                 "user_id":1,
                  "vote":1
              },
              {
                 "user_id":2,
                  "vote":2
              }
          ]
       },
       {
         "product_id":"98765",
         "product_name":"product2",
         "user_votes":[
              {
                 "user_id":5,
                  "vote":3
              },
              {
                 "user_id":3,
                  "vote":3
              }
          ]
       }
    ]
}  

I used some operation like sort and sum on this sub of documents so i used

db.products.aggregate([
    { $unwind: "$products" },
    { $project: {
        company_name: 1,
        products: {
            product_id: 1,
            product_name: 1,
            user_votes: 1,
            votes: { $sum: "$products.user_votes.vote" }
        } 
    } },
    { $sort: { totalVotes: -1 } },
    {
        $group: {
            _id: "$_id",
            company_name: { $first: "$company_name" },
            products: { $push: "$products" }
        }
    }
])  

to get this result :

{
   "_id":17846384es,
   "company_name":"company1",
   "products":[
       {
         "product_id":"98765",
         "product_name":"product2",
         "user_votes":[
              {
                 "user_id":5,
                  "vote":3
              },
              {
                 "user_id":3,
                  "vote":3
              }
          ]
        "votes":6
       },
       {
         "product_id":"123785",
         "product_name":"product1",
         "user_votes":[
              {
                 "user_id":1,
                  "vote":1
              },
              {
                 "user_id":2,
                  "vote":2
              }
          ],
          "votes":3
       }
    ]
}  

but if product document dosen't exist I have an empty result. I want to get a result with company information even if product document doesn't exist.

Answer 1

Firstly, you can use "preserveNullAndEmptyArrays" to preserve the company which doesn't have product array.

The below query should output the company names even if it doesn't have products array.

I think you are trying to sum the votes and sort the product array to get the highest number of products as first element in the array. The below query should produce the result.

db.product.aggregate([
    { $unwind: { path : "$products", preserveNullAndEmptyArrays : true }},
    { $project: {
        company_name: 1,
        products : { $ifNull : ["$products", "0"]}}            
    },
    { $project: {
        company_name: 1,
        products: {
            product_id: 1,
            product_name: 1,
            user_votes: 1,
            votes: { $sum: "$products.user_votes.vote" }
        } 
    } },
    { $sort: { "products.votes": -1 } },
    {
        $group: {
            _id: "$_id",
            company_name: { $first: "$company_name" },
            products: { $push: "$products" }
        }
    },  

]);  

In case if you want to keep the companies which doesn't have product at the end. You can add this additional sort as the last pipeline in the above query.

{ $sort: { "products.votes": -1 } },

Output of companies which doesn't have product array would like as below:-

{
    "_id" : ObjectId("57f2bd50dd4752c20947fd03"),
    "company_name" : "company2",
    "products" : [ 
        {
            "votes" : 0
        }
    ]
}