Query List Fields in MongoDB Subdocuments in esProc

Problem source：

https://groups.google.com/forum/#!msg/mongodb-user/HqzXSh5DZek/ffZG0TQ1w8cJ . 

Collection Cbetween contains cascaded subdocuments, in which the List-type dataList field includes a series of strings, each of which has multiple numbers. You need to find strings according to the criterion that the first number is greater than 6154 and less than or equal to 6155.

Below is one of Cbetween’s subdocuments:

{         "_id" : ObjectId("54f6a766bf4436333edcd6a2"),         "_class" : "com.abc.core.bo.obj.Objs",         "objList" : [                 {                         "name" : "ABB-09",                         "uid" : "ABB-09",                         "data" : {                                 "dataId" : NumberLong(0),                                 "dataList" : [             "6150,32.9,1.475,,1.434",             "6150.5,43,,1.529,1.402",             "6151,31.8,1.506,1.447,1.453",             "6151.5,33.6,1.481,1.456,1.521",             "6152,30.9,1.465,1.472,1.547",             "6152.5,39.5,1.404,1.425,1.485",             "6153,43.2,1.406,1.446,1.481",             "6153.5,39.5,1.433,1.468,1.488",             "6154,32.7,1.459,1.477,1.427",             "6154.5,37.9,1.529,1.429,1.429",             "6155,30.4,1.505,1.532,1.543",             "6155.5,37.3,1.49,1.436,1.462",             "6156,35.3,1.538,1.45,1.488",             "6156.5,37.3,1.517,1.535,1.473",             "6157,32.7,1.401,1.405,1.497",             "6157.5,38.9,1.488,1.468,1.499",             "6158,35.4,1.526,1.422,1.452",             "6158.5,43.3,1.516,1.433,1.491",             "6159,34.6,1.519,1.442,1.478",             "6159.5,42.7,1.426,1.514,1.428",       "6160,32.7,1.451,1.5,1.516"             ]            }           }          ]  }

The eligible strings include "6154.5,37.9,1.529,1.429,1.429","6155,30.4,1.505,1.532,1.543".

esProc code：

A1: Connect to MongoDB. The connection string format is mongo://ip:port/db?arg=value&…

A2: Retrieve data from MongoDB using find function and create a cursor. The name of the collection is Cbettwen. There is no filtering criterion. Retrieve all fields except _id field. Syntax of filtering criterion in esProc find function, which is similar to its MongoDB counterpart, follows MongoDB rules. 

A3: Find the eligible strings. conj function concatenates results of filtering each subdocument in A2; ~ represents each member of an upper level of table sequence. new function is used to create a new table sequence and #1 represents the first field of the table sequence. array function can split a string into a sequence with comma being default delimiter; @1 means splitting the string into two members with the first delimiter being the boundary.

A4: Fetch cursor data in batches to get data from the memory. The result is as follows:

A5：Disconnect from MongoDB.