RaqForum 19 No.
1 Reply  •  633 View  • 
     ...

Querying ListField in a MongoDB Subdocument

 


Usually a MongoDB ListField wraps multiple fields in strings. This storage mode makes easy management. Users don’t need to worry about the alignment between detailed data and fields when importing and exporting a large volume of data. So that’s fast. On the other hand, however, you need to split the strings and perform data type conversion before real data processing. It’s inconvenient to handle this with mongo shell. Yet it’s easy to get it done in esProc SPL that provides a series of interface functions.

Collection Cbettwen contains multilevel subdocuments. dataList field contains strings of List data type. Each string is made up of a set of numbers. Task: find the string where the first number is greater than 6154 and equal to and less than 6155.

Here’s one of the subdocument in Cbettwen:

{
      "_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 string is "6154.5,37.9,1.529,1.429,1.429","6155,30.4,1.505,1.532,1.543".

SPL script:

A

1

=mongo_open("mongodb:// localhost:27017/local?user=test&password=test")

2

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

3

=A2.conj((t=~.objList.data.dataList.new(~),t.select((s=float(#1.split@c()(1)),s>6154 && s<=6155))))

4

=A3.fetch()

5

=mongo_close(A1)

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

A2: find() function retrieves documents from collection Cbettwen and return result as a cursor. As no filtering condition is specified, it gets all fields except _id.

A3: Find out the eligible string. conj() function concatenates results of processing each document in A2; ~ represents a member in the table sequence of one level higher. new() function creates a new table sequence; #1 represents the first field in the table sequence. split() function splits strings by commas and return them as a sequence; @1 option enables splitting strings into a two-member sequence by the first separator; s enables conversion of the first string into a float type number for comparison.

A4: Batch process data in A3’s cursor to get a result and store it in the memory:

6154.5,37.9,1.529,1.429,1.429

6155,30.4,1.505,1.532,1.543

6154.6,100.9,1.529,1.429,1.429

6154.7,200.9,1.529,1.429,1.429

6155,100.3,1.49,1.436,1.462

6155,200.3,1.49,1.436,1.462

6154.6,300.9,1.529,1.429,1.429

6154.7,400.9,1.529,1.429,1.429

6155,300.3,1.49,1.436,1.462

6155,400.3,1.49,1.436,1.462

6154.6,500.9,1.529,1.429,1.429

6154.7,600.9,1.529,1.429,1.429

6155,500.3,1.49,1.436,1.462

A5: Close MongoDB connection.

It’s concise and easy to query MongoDB ListField in SPL.

 


 
 •  633 View  •