1

My mongo database has two collections (samples copied from MongoDB query result):

Order: 
{
"_id" : ObjectId("5f242e68ceebdd59a456fee5"),
"date": ISODate("2020-07-30T22:00:00.000Z"),
"user" : {
        "$ref" : "user",
        "$id" : "user_0"
},
"item": "banana"
}

User:
{
    "_id" : "user_0",
    "login" : "login_0",
    "password" : "$2a$10$mE.qmcV0mFU5NcKh73TZx.z4ueI/.bDWbj0T1BYyqP481kGGarKLG",
    "first_name" : "John",
    "last_name" : "Kennedy",
    "email" : "jfk@gmail.com",
    "activated" : true
}

Each order belongs to a user. I put inside every Order's object a collection reference of the corresponding user.

With MongoDB's aggregation function, how would you do to get the number of Order that belongs to a user and put it inside an object alongside with his first name and his last name? I want a result to look somehow like this:

TopUser 
{
    "firstName": "John",
    "lastName": "Kennedy",
    "nbOfOrders": 3
}

Thank you very much.

Hải Trung Phạm
  • 89
  • 1
  • 8

1 Answers1

0

You can try like this,

[
  {
    "$lookup": {
      "from": "orders",
      "localField": "_id",
      "foreignField": "user.id",
      "as": "users"
    }
  },
  {
    $project: {
        _id:0,
      first_name: 1,
      last_name: 1,
      nbOfOrders: {
        $size: "$users"
      }
    }
  }
]

Working Mongo playground Note: Remove $ from $ref and $id

varman
  • 8,704
  • 5
  • 19
  • 53
  • Thank you very much, I have a question though. What does the value 1 of the field "first_name" really mean and where is it documented? – Hải Trung Phạm Aug 02 '20 at 18:01
  • 1 - to include the field, 0-to exclue the field. Refer this https://docs.mongodb.com/manual/reference/operator/aggregation/project/ – varman Aug 02 '20 at 18:04
  • I just tested it today, but it didn't work for me, the "$users" array contains actually nothing although it's supposed to have all the orders that the user made (just like in mongodb playground) – Hải Trung Phạm Aug 03 '20 at 07:36
  • The relationship between two tables is userId, no? I made the aggregation pipeline based on how you given the data, and I posted a Demo also, If you feel you can add more details, please edit – varman Aug 03 '20 at 07:42
  • Yes, I just checked it. It's user. Inside the java class Order, I have @DBRef @Field("user") private User user; this creates a one to one association between 2 collections: User and Order – Hải Trung Phạm Aug 03 '20 at 08:15
  • Ok, what's the issue now ur facing? – varman Aug 03 '20 at 08:17
  • Well, I tested your function and it gave me { "first_name" : "John", "last_name" : "Kennedy", "nbOfOrder" : 0 }. Although, I'm pretty sure that this user makes a lot of orders. The same thing happended to all users. – Hải Trung Phạm Aug 03 '20 at 08:21
  • 1
    Haha, it seems funny. You have given your sample data, what @varman did was correct for the question, you have to try your own with given answer, We can't predict what your database has, or you should have posted all the relevant information –  Aug 03 '20 at 08:31
  • hello, how would you guys do it with the pipeline of the lookup function? – Hải Trung Phạm Aug 03 '20 at 08:48
  • The question is regarding mkngodb, right? Not spring? – varman Aug 03 '20 at 08:51
  • yep, it's all about mongodb, I think I found a reason why it doesn't work. That's because I used DBRef inside order. I also think that the answer it's in this thread: https://stackoverflow.com/questions/40622714/mongo-how-to-lookup-with-dbref – Hải Trung Phạm Aug 03 '20 at 11:41
  • I have no idea to continue this question. The answer is provided based on your question, but you ask something else. If the question mentioned spring boot, i could have given a solution to that. Anyhow, keep it up. – varman Aug 03 '20 at 12:48