RaqForum 28 No.
395 View  • 
     ...

How to Group and Summarize MongoDB Subdocuments

 
subdocumens(1) group(18) summarize(1)

As the most popular non-relational database, MongoDB achieves the one-to-many relationship through the nested subdocument structure. Grouping the nested subdocuments during application development is not uncommon. Below is a MongoDB collection:

{
  "warehouseNsId":"10","brandId":37,
  "financeOwnerId":1231882808817905665,"amount":0,
  "items":[
  　{"goodsNsId":"1353","price":"256.00","count":3},
  　{"goodsNsId":"1799","price":"254.80","count":6},
  　{"goodsNsId":"1353","price":"256.00","count":10}
     ]
    },{
    "warehouseNsId":"15","brandId":35,
  "financeOwnerId":1231882808817905600,"amount":0,
  "items":[
  　{"goodsNsId":"1327","price":"238.00","count":17},
  　{"goodsNsId":"1539","price":"154.80","count":25},
  　{"goodsNsId":"1327","price":"238.00","count":13},
  　{"goodsNsId":"1539","price":"154.80","count":15}
    ]},
    ...

We are trying to group records by items.goodsNsId and sum items.count. Here is the desired result:

{
  "warehouseNsId":"10","brandId":37,
  "financeOwnerId":1231882808817905665,"amount":0,
  "items":[
  　{"goodsNsId":"1353","price":"256.00","count":13},
  　{"goodsNsId":"1799","price":"254.80","count":6}
    ]
    },
  ...

If we use a MongoDB script to do this, we need to group and sum the sub-records under each record and then join the intermediate results with the source collection. It is complicated.

It is convenient to get it done with esProc SPL. We group and sum subdocuments under each record and assign the aggregate result to the subdocuments.
1. Write SPL script warehouse.dfx in esProc:

A

B

1

=mongo_open("mongodb://localhost:27017/cata")

/ Connect to mongodb

2

=mongo_shell(A1,"warehouse.find(,{_id:0})").fetch()

/ Get data from the warehouse collection

3

>A2.run(items=(items.groups(goodsNsId;   price,   sum(count):count)))

/ Group and sum the subdocuments and assign the aggregate results to   subdocuments

4

>A1.close()

/ Close database connection

  2. Start debugging and execute the code. Below is the value of cell A2:

A2

warehouseNsId

brandId

financeOwnerId

amount

items

10

37.0

1.23188280881790566E18

0.0

[[1353, 256.00, 3....]]

15

35.0

1.23188280881790566E18

0.0

[[1327, 238.00, 17...]]

…

…

…

  3. Execute the script and return result as follows:

A2

warehouseNsId

brandId

financeOwnerId

amount

items

10

37.0

1.23188280881790566E18

0.0

[1353, 1799]

15

35.0

1.23188280881790566E18

0.0

[1327,   1539]

…

…

…

Click the items field value in the first row of the above table sequence, and we can get the following table:

goodsNsId

price

count

1353

256.00

13.0

1799

254.80

6.0

A3: run() function loops through each record to group and sum the subdocuments and assign the aggregate result to the sub-records.

Read How to Call an SPL Script in Java to learn about the integration of an SPL script with a Java program.


subdocumens(1) group(18) summarize(1)
 
 •  395 View  •