RaqForum 28 No.
289 View  • 
     ...

How to Sort a MongoDB Collection by a Specific Field in the Nested Document

 
nested-structure(1) subquery(3) sort(10) filtering(7)

The following collection contains a list table of nested structure. We are trying to sort the collection according to a specified field in the subdocument meeting a specific condition in the nested documents. Below is collection demo:

{
  "_id" : ObjectId("59fb2e93670c90000ab85efb"),
      "relateId" : NumberLong(124),
      "list" : [ 
          {
              "relateId" : NumberLong(206),
              "position" : 10
          }, 
          {
              "relateId" : NumberLong(208),
              "position" : 4
          }
      ],
      "createAt" : NumberLong(1489258188083),
      "updateAt" : NumberLong(1505796172787)
    }

 {
  "_id" : ObjectId("59fb2e93670c90000ab86efb"),
      "relateId" : NumberLong(125),
      "list" : [ 
          {
              "relateId" : NumberLong(208),
              "position" : 5
          }
      ],
      "createAt" : NumberLong(1489258188083),
      "updateAt" : NumberLong(1505796172787)
  }
   
   
  ......

Suppose we want to do the sorting according to position field under list object meeting the condition list.relateId＝208.
It is easy to do the task with esProc SPL. We select eligible records according to the specified condition and do the sorting by position field.

1.     Write SPL script demo.dfx in esProc:

A

B

1

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

/ Connect to MongoDB database

2

=mongo_shell(A1,"demo.find({\"list.relateId\":{$in:[208]}},     {_id:0})").fetch()

/ Get data from demo collection

3

=A2.sort(-~.list.select@1(relateId==208).position )

/ Sort A2’s collection by position field in the selected records

4

>A1.close()

/ Close database connection

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

A2

relateId

list

createAt

updateAt

124

[[206,10.0],[208,4.0]]

1489258188083

1505796172787

125

[[208,5.0]]

1489258188083

1505796172787

100

[[203,20.0],[208,6.0]]

1489258188000

1505796172777

123

[[208,9.0]]

1489258188883

1505796172888

3.     Execute the script and below is the value of A3:

A3

relateId

list

createAt

updateAt

123

[[208,9.0]]

1489258188883

1505796172888

100

[[203,20.0],[208,6.0]]

1489258188000

1505796172777

125

[[208,5.0]]

1489258188083

1505796172787

124

[[206,10.0],[208,4.0]]

1489258188083

1505796172787

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


nested-structure(1) subquery(3) sort(10) filtering(7)
 
 •  289 View  •