mongodb group values by multiple fields

Question

For example  I have these documents        addr    address1      book    book1          addr    address2      book    book1          addr    address1      book    book5          addr    address3      book    book9          addr    address2      book    book5          addr    address2      book    book1          addr    address1      book    book1          addr    address15      book    book1          addr    address9      book    book99          addr    address90      book    book33          addr    address4      book    book3          addr    address5      book    book1          addr    address77      book    book11          addr    address1      book    book1      and so on How can I make a request  which will describe the top N addresses and the top M books per address Example of expected result  address1   book 1  5            book 2  10            book 3  50            total  65                        address2   book 1  10            book 2  10                          book M  10            total  M 10                           addressN   book 1  20            book 2  20                          book M  20            total  M 20

User · Answer

Using aggregate function like below         group    id    book     book  address   addr    total   sum  1       project    book      id book   address      id address   total     total    id   0       it will give you result like following                            total    1               book     book33                address     address90                                     total    1               book     book5                address     address1                                     total    1               book     book99                address     address9                                     total    1               book     book1                address     address5                                     total    1               book     book5                address     address2                                     total    1               book     book3                address     address4                                     total    1               book     book11                address     address77                                     total    1               book     book9                address     address3                                     total    1               book     book1                address     address15                                     total    2               book     book1                address     address2                                     total    3               book     book1                address     address1              I didn t quite get your expected result format  so feel free to modify this to one you need

User · Answer

Below query will provide exactly the same result as given in the desired response   db books aggregate                  group                 id    addresses    addr   books    book                 num     sum  1                                   group                 id     id addresses               bookCounts     push    bookName     id books  count    num                                      project                 id  1              bookCounts 1               totalBookAtAddress                       sum     bookCounts count                                       The response will be looking like below       1            id     address4        bookCounts                             bookName     book3                count    1                       totalBookAtAddress    1        2            id     address90        bookCounts                             bookName     book33                count    1                       totalBookAtAddress    1        3            id     address15        bookCounts                             bookName     book1                count    1                       totalBookAtAddress    1        4            id     address3        bookCounts                             bookName     book9                count    1                       totalBookAtAddress    1        5            id     address5        bookCounts                             bookName     book1                count    1                       totalBookAtAddress    1        6            id     address1        bookCounts                             bookName     book1                count    3                                   bookName     book5                count    1                       totalBookAtAddress    4        7            id     address2        bookCounts                             bookName     book1                count    2                                   bookName     book5                count    1                       totalBookAtAddress    3        8            id     address77        bookCounts                             bookName     book11                count    1                       totalBookAtAddress    1        9            id     address9        bookCounts                             bookName     book99                count    1                       totalBookAtAddress    1

User · Answer

TLDR Summary In modern MongoDB releases you can brute force this with  slice just off the basic aggregation result  For  quot large quot  results  run parallel queries instead for each grouping   a demonstration listing is at the end of the answer    or wait for SERVER-9377 to resolve  which would allow a  quot limit quot  to the number of items to  push to an array  db books aggregate          quot  group quot              quot  id quot                  quot addr quot    quot  addr quot                quot book quot    quot  book quot                      quot bookCount quot      quot  sum quot   1                  quot  group quot              quot  id quot    quot   id addr quot            quot books quot                   quot  push quot                       quot book quot    quot   id book quot                    quot count quot    quot  bookCount quot                                     quot count quot      quot  sum quot    quot  bookCount quot                   quot  sort quot      quot count quot   -1             quot  limit quot   2           quot  project quot              quot books quot      quot  slice quot      quot  books quot   2               quot count quot   1             MongoDB 3 6 Preview Still not resolving SERVER-9377  but in this release  lookup allows a new  quot non-correlated quot  option which takes an  quot pipeline quot  expression as an argument instead of the  quot localFields quot  and  quot foreignFields quot  options  This then allows a  quot self-join quot  with another pipeline expression  in which we can apply  limit in order to return the  quot top-n quot  results  db books aggregate        quot  group quot          quot  id quot    quot  addr quot        quot count quot      quot  sum quot   1              quot  sort quot      quot count quot   -1           quot  limit quot   2         quot  lookup quot          quot from quot    quot books quot        quot let quot            quot addr quot    quot   id quot              quot pipeline quot              quot  match quot               quot  expr quot      quot  eq quot      quot  addr quot    quot   addr quot                        quot  group quot              quot  id quot    quot  book quot            quot count quot      quot  sum quot   1                      quot  sort quot      quot count quot   -1                quot  limit quot   2               quot as quot    quot books quot           The other addition here is of course the ability to interpolate the variable through  expr using  match to select the matching items in the  quot join quot   but the general premise is a  quot pipeline within a pipeline quot  where the inner content can be filtered by matches from the parent  Since they are both  quot pipelines quot  themselves we can  limit each result separately  This would be the next best option to running parallel queries  and actually would be better if the  match were allowed and able to use an index in the  quot sub-pipeline quot  processing  So which is does not use the  quot limit to  push quot  as the referenced issue asks  it actually delivers something that should work better   Original Content You seem have stumbled upon the top  quot N quot  problem  In a way your problem is fairly easy to solve though not with the exact limiting that you ask for  db books aggregate          quot  group quot              quot  id quot                  quot addr quot    quot  addr quot                quot book quot    quot  book quot                      quot bookCount quot      quot  sum quot   1                  quot  group quot              quot  id quot    quot   id addr quot            quot books quot                   quot  push quot                       quot book quot    quot   id book quot                    quot count quot    quot  bookCount quot                                     quot count quot      quot  sum quot    quot  bookCount quot                   quot  sort quot      quot count quot   -1             quot  limit quot   2       Now that will give you a result like this         quot result quot                                         quot  id quot     quot address1 quot                        quot books quot                                                                         quot book quot     quot book4 quot                                        quot count quot    1                                                                                                   quot book quot     quot book5 quot                                        quot count quot    1                                                                                                   quot book quot     quot book1 quot                                        quot count quot    3                                                                           quot count quot    5                                                   quot  id quot     quot address2 quot                        quot books quot                                                                         quot book quot     quot book5 quot                                        quot count quot    1                                                                                                   quot book quot     quot book1 quot                                        quot count quot    2                                                                           quot count quot    3                           quot ok quot    1    So this differs from what you are asking in that  while we do get the top results for the address values the underlying  quot books quot  selection is not limited to only a required amount of results  This turns out to be very difficult to do  but it can be done though the complexity just increases with the number of items you need to match  To keep it simple we can keep this at 2 matches at most  db books aggregate          quot  group quot              quot  id quot                  quot addr quot    quot  addr quot                quot book quot    quot  book quot                      quot bookCount quot      quot  sum quot   1                  quot  group quot              quot  id quot    quot   id addr quot            quot books quot                   quot  push quot                       quot book quot    quot   id book quot                    quot count quot    quot  bookCount quot                                     quot count quot      quot  sum quot    quot  bookCount quot                   quot  sort quot      quot count quot   -1             quot  limit quot   2           quot  unwind quot    quot  books quot            quot  sort quot      quot count quot   1   quot books count quot   -1             quot  group quot              quot  id quot    quot   id quot            quot books quot      quot  push quot    quot  books quot              quot count quot      quot  first quot    quot  count quot                   quot  project quot              quot  id quot                  quot  id quot    quot   id quot                quot books quot    quot  books quot                quot count quot    quot  count quot                      quot newBooks quot    quot  books quot                 quot  unwind quot    quot  newBooks quot            quot  group quot            quot  id quot    quot   id quot          quot num1 quot      quot  first quot    quot  newBooks quot                   quot  project quot              quot  id quot    quot   id quot            quot newBooks quot    quot   id books quot            quot num1 quot   1                quot  unwind quot    quot  newBooks quot            quot  project quot              quot  id quot    quot   id quot            quot num1 quot   1           quot newBooks quot   1           quot seen quot      quot  eq quot                  quot  num1 quot                quot  newBooks quot                            quot  match quot      quot seen quot   false             quot  group quot             quot  id quot    quot   id  id quot            quot num1 quot      quot  first quot    quot  num1 quot              quot num2 quot      quot  first quot    quot  newBooks quot              quot count quot      quot  first quot    quot   id count quot                   quot  project quot              quot num1 quot   1           quot num2 quot   1           quot count quot   1           quot type quot      quot  cond quot     1   true false  0                    quot  unwind quot    quot  type quot            quot  project quot              quot books quot      quot  cond quot                  quot  type quot                quot  num1 quot                quot  num2 quot                       quot count quot   1                quot  group quot              quot  id quot    quot   id quot            quot count quot      quot  first quot    quot  count quot              quot books quot      quot  push quot    quot  books quot                   quot  sort quot      quot count quot   -1         So that will actually give you the top 2  quot books quot  from the top two  quot address quot  entries  But for my money  stay with the first form and then simply  quot slice quot  the elements of the array that are returned to take the first  quot N quot  elements   Demonstration Code The demonstration code is appropriate for usage with current LTS versions of NodeJS from v8 x and v10 x releases  That s mostly for the async await syntax  but there is nothing really within the general flow that has any such restriction  and adapts with little alteration to plain promises or even back to plain callback implementation  index js const   MongoClient     require  mongodb    const fs   require  mz fs     const uri    mongodb   localhost 27017    const log   data   gt  console log JSON stringify data  undefined  2      async function        try       const client   await MongoClient connect uri        const db   client db  bookDemo        const books   db collection  books         let   version     await db command   buildInfo  1         version   parseFloat version match new RegExp        -        0            Clear and load books     await books deleteMany           await books insertMany         await fs readFile  books json             toString            replace   n    quot  quot            split  quot  n quot            map JSON parse              if   version  gt   3 6             Non-correlated pipeline with limits       let result   await books aggregate              quot  group quot                quot  id quot    quot  addr quot              quot count quot      quot  sum quot   1                          quot  sort quot      quot count quot   -1                 quot  limit quot   2               quot  lookup quot                quot from quot    quot books quot              quot as quot    quot books quot              quot let quot      quot addr quot    quot   id quot                quot pipeline quot                    quot  match quot                    quot  expr quot      quot  eq quot      quot  addr quot    quot   addr quot                                     quot  group quot                    quot  id quot    quot  book quot                  quot count quot      quot  sum quot   1                                   quot  sort quot      quot count quot   -1                     quot  limit quot   2                                   toArray           log   result                   Serial result procesing with parallel fetch         First get top addr items     let topaddr   await books aggregate            quot  group quot              quot  id quot    quot  addr quot            quot count quot      quot  sum quot   1                      quot  sort quot      quot count quot   -1               quot  limit quot   2          toArray            Run parallel top books for each addr     let topbooks   await Promise all        topaddr map     id  addr      gt          books aggregate                quot  match quot     addr                   quot  group quot                  quot  id quot    quot  book quot                quot count quot      quot  sum quot   1                              quot  sort quot      quot count quot   -1                   quot  limit quot   2              toArray                          Merge output     topaddr   topaddr map  d i    gt        d  books  topbooks i           log   topaddr          client close         catch e        console error e      finally       process exit              books json    quot addr quot    quot address1 quot     quot book quot    quot book1 quot        quot addr quot    quot address2 quot     quot book quot    quot book1 quot        quot addr quot    quot address1 quot     quot book quot    quot book5 quot        quot addr quot    quot address3 quot     quot book quot    quot book9 quot        quot addr quot    quot address2 quot     quot book quot    quot book5 quot        quot addr quot    quot address2 quot     quot book quot    quot book1 quot        quot addr quot    quot address1 quot     quot book quot    quot book1 quot        quot addr quot    quot address15 quot    quot book quot    quot book1 quot        quot addr quot    quot address9 quot     quot book quot    quot book99 quot       quot addr quot    quot address90 quot    quot book quot    quot book33 quot       quot addr quot    quot address4 quot     quot book quot    quot book3 quot        quot addr quot    quot address5 quot     quot book quot    quot book1 quot        quot addr quot    quot address77 quot    quot book quot    quot book11 quot       quot addr quot    quot address1 quot     quot book quot    quot book1 quot

[mongodb] mongodb group values by multiple fields

Examples related to mongodb

Examples related to aggregation-framework