MongoDB is a document-oriented NoSQL database operated on a distributed file system. The non-relational database is functionally rich and very similar to a relational database. It can store data in more complicated data types because it stores documents in a loose, JSON-like BSON format. Its most noticeable feature is that it has a powerful query language, whose syntax is similar to that of an object-oriented query language. The language supports most of the operations similar to those performed over a single table in a relational database. But the queries are not simple. esProc SPL language can make those queries simple enough.
In this article, we’ll explain how esProc SPL makes querying MongoDB documents more convenient in the following calculations:
1. A document with array values
2. A document with sub-documents
3. A document grouped by segment
4. Union of same-structure documents
5. Association with embedded structures (1)
6. Association with embedded structures (2)
7. Association with embedded structures (3)
8. Multi-field grouping & summarization
9. Association with selected fields
10. Multi-table association
11. Conditional search
12. Associated query by array values
This involves the handling of data in embedded array values. For instance, find the average of each subject and each student’s total mark.
Test data:
Desired result:
Mongodb script:
The Score values are arrays recording subjects and marks. They need to be split to correspond to each student for further grouping and summarization. This involves the cooperation of $unwind and $group operators.
SPL script:
Average of each subject:
Total mark of each student:
SPL script explanation:
A1: Connect to MongoDB database.
A2: Read in data from student table.
A3: Concatenate each Score value into a table sequence, group the table by subject and calculate the average.
A4: Calculate the total mark of each student and return a table sequence made up of two columns – NAME and TOTAL. The new() function creates a new table sequence.
A5: Close the database connection.
Test data:
Desired result:
Mongodb script:
SPL script:
Result:
SPL script explanation:
A1: Connect to MongoDB database.
A2: Read in data from computer table.
A3：Convert income value and output value respectively into a sequence to calculate the sum, and then join up with ID field to generate a new table sequence.
A4: Close the database connection.
The SPL code retrieves field values from each sub-document and calculates sum. It is much simpler than the MongoDB code. It appears that the embedded sub-documents and the embedded arrays are similar, but be careful not to confuse them. The scripts of handling them are slightly different.
Count the records in each segment. For example, the following data is divided by ranges of sales; find the number of records in each segment.
Grouping conditions: 0-3000; 3000-5000; 5000-7500; 7500-10000; above 10000.
Desired result:
MongoDB script:
It’s complicated to implement conditional grouping by segment in MongoDB because the database doesn’t provide the corresponding API. The above script is one of the possibilities to handle the computation.
SPL script:
SPL script explanation:
A1: Define the ranges of SALES.
A2: Connect to MongoDB database.
A3: Read in data from sales table.
A4: Count the records according to the SALES ranges. The pseg() function returns the segment number containing the current record. The int() function converts data into an integer.
A5: Close the database connection.
The use of pseg() function makes a concise script.
Emp1:
Emp2:
Desired unioned table:
MongoDB script:
$facet stores the two tables in two arrays respectively; $concatArrays concatenates the arrays; $unwind splits the sub-records and presents it at the outermost layer. By contrast, SPL handles it clearly and simply.
SPL script:
SPL script explanation:
A1: Connect to MongoDB database.
A2: Read in data from emp1 table.
A3: Read in data from emp2 table.
A4: Concatenate the two tables.
A5: Close the database connection.
The SPL script is natural and easy to understand. But for a SQL user who begins trying MongDB, they could be puzzled by the MongoDB-style data concatenation.
Test data:
history:
childsgroup:
The child_id field in history table associates with childs.id in childsgroup table. Below is the desired joining result:
{
"_id" : ObjectId("5bab2ae8ab2f1bdb4f434bc3"),
"id" : "001",
"history" : "today worked",
"child_id" : "ch001",
"childInfo" :
{
"name" : "a"
}
………………
}
MongoDB script:
The script uses five operators, including lookup, pipeline, match, unwind and replaceRoot, to handle the computation. It’s complicated and difficult to write.
SPL script:
Joining result:
SPL script explanation:
A1: Connect to MongoDB database.
A2: Read in data from history table.
A3：Read in data from childsgroup table.
A4: Concatenate the array values in childsgroup table’s childs field into a table sequence.
A5: Join history table with A4’s childs table via the associated fields child_id and id and append a name field to generate a new table sequence.
A6：Close the database connection.
The SPL script is simple and efficient.
txtComment：
txtPost:
Desired result:
MongoDB script:
Split txtPost records according to members of Comment arrays into more records, join them with records of txtComment, store the joining result as an array, split the array into records, move comment_content values under Comment field, and group and concatenate the records.
SPL script:
SPL script explanation:
A1：Connect to MongoDB database.
A2：Read data from txtPost table.
A3：Read data from txComment table.
A4：Create a table sequence with A2’s Comment field and post_no field that is renamed pno.
A5：Join A4’s table sequence with A3’s table sequence via comment_no and append comment_content field that is renamed Content.
A6：Group records by pno and return the current table; ~ represents the current record.
A7：Close the database connection.
Test data:
collection1:
$lookup performs an associated query between the two tables. The first $addFields gets the first field from isCompleted array, and the second $addFields adds the other required fields.
SPL script:
SPL script explanation:
A1：Connect to MongoDB database.
A2：Read data from collection1 table.
A3：Read data from collection2 table.
A4：Query A2’s table according to order field and lot field, add A3’s serialNo field and batchNo field, and return the joined tabled table sequence.
A5：Close the database connection.
This instance filters the sub-documents to get the eligible records to form a new table sequence.
Desired result:
MongoDB script:
Count different books by addr and book, then group by addr to find the total number of books in each group, and re-arrange the records by the required order.
SPL script:
Result:
SPL script explanation:
A1: Connect to MongoDB database.
A2: Read data from books table.
A3: Group data by addr and book to count different books.
A4: Group A3’s table sequence by addr and find the total number of books in each group.
A5: Join A3 and A4 via addr and add Total field to the joined table sequence.
A6: Close the database connection.
collection1:
collection2:
Desired result:
MongoDB script:
$lookup performs an associated query. $redact traverses records to perform conditional handling. $project selects the desired fields.
SPL script:
SPL script explanation:
A1: Connect to MongoDB database.
A2: Read data from collection1 table.
A3: Read data from collection2 table.
A4: Join the two tables by user1 and user2, append A3’s output field and return a new table sequence.
A5: Close the database connection.
Combine some of fields in two tables via a join into a new table.
Doc1:
Doc2:
Doc3:
Merged result:
There can be different scripts to do this and various merged results in MongoDB thanks to the database’s unique data structure.
SPL script:
This script is similar to that in the previous example, with only one more joined table. Every join adds a new field to form a merged table.
Obviously, the SPL script is concise and coherent.
Test data:
Desired result:
MongoDB script:
Find students who select the courses including chemical, biology and math.
SPL script:
SPL script explanation:
A1：Define the searching condition, which is an array of subjects.
A2：Connect to MongoDB database.
A3：Read data from student table.
A4：Get records where the Subject value contains at least one of the subjects in the specified array.
A5：Generate a new table sequence consisting of name field and subject field to which a matching subject is added.
A6：Close the database connection.
The SPL script is concise and easy to understand.
Test data:
users:
workouts:
Desired result:
MongoDB script:
Put the result of associated query between users and workouts into an array, split the array and move the embedded document to the top level, and remove the unwanted fields.
SPL script:
SPL script explanation:
A1: Connect to MongoDB database.
A2: Read data from users table.
A3: Read data from workouts table.
A4: Get records from workouts table where _id field is included in workouts field of A2’s table sequence, and add name field to the records.
A5: Close the database connection.
The operation is considered normal only if the matching result isn’t null. So _id field is converted into a sequence to make the comparison convenient in A4.
Compared with relational databases, MongoDB stores data in a more complicated, more flexible way, has powerful and adaptable query syntax, and provides various functions and rich cooperative possibilities between them. It’s not easy to be proficient in MongoDB. esProc, however, features discreteness and ease of use to become the NoSQL database’s right-hand and help it play to its full potential.
In this article, we’ll explain how esProc SPL makes querying MongoDB documents more convenient in the following calculations:
1. A document with array values
2. A document with sub-documents
3. A document grouped by segment
4. Union of same-structure documents
5. Association with embedded structures (1)
6. Association with embedded structures (2)
7. Association with embedded structures (3)
8. Multi-field grouping & summarization
9. Association with selected fields
10. Multi-table association
11. Conditional search
12. Associated query by array values
1. A document with array values
This involves the handling of data in embedded array values. For instance, find the average of each subject and each student’s total mark.Test data:
| _id | name | sex | Score |
|---|---|---|---|
| 1 | Tom | F | [{"subject":"Physics", "mark":60}, {"subject":"Chemical", "mark":72}] |
| 2 | Jerry | M | [{"subject":"Physics", "mark":92}, {"subject":"Math", "mark":81}] |
Desired result:
| Physics | 76 | Tom | F | |
| Chemical | 72 | Jerry | 173 | |
| Math | 81 |
Mongodb script:
| db.student.aggregate( [ {$unwind : "$score"}, {$group: { "_id": {"subject":"$score.subject"} , "qty":{"$avg": "$score.mark"} } } ] ) |
db.student.aggregate( [ {$unwind : "$score"}, {$group: { "_id": {"name" :"$name"} , "qty":{"$sum" : "$score.mark"} } } ] ) |
SPL script:
| A | |
|---|---|
| 1 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") |
| 2 | =mongo_shell(A1,"student.find()").fetch() |
| 3 | =A2.conj(score).groups(subject:SUBJECT;avg(mark):AVG) |
| 4 | =A2.new(name:NAME,score.sum(mark):TOTAL) |
| 5 | >A1.close() |
Average of each subject:
| LESSON | AVG |
|---|---|
| Chemical | 72.0 |
| Math | 81.0 |
| Physics | 76.0 |
Total mark of each student:
| NAME | TOTAL |
|---|---|
| Tom | 132 |
| Jerry | 173 |
SPL script explanation:
A1: Connect to MongoDB database.
A2: Read in data from student table.
A3: Concatenate each Score value into a table sequence, group the table by subject and calculate the average.
A4: Calculate the total mark of each student and return a table sequence made up of two columns – NAME and TOTAL. The new() function creates a new table sequence.
A5: Close the database connection.
2. A document with sub-documents
Calculate sum of data in the sub-documents. For example, calculate the sum of income and output respectively for each record.Test data:
| _id | income | output |
|---|---|---|
| 1 | ｛"cpu":1000, "mem":500, "mouse":"100"｝ | ｛"cpu":1000, "mem":600 ,"mouse":"120"｝ |
| 2 | ｛"cpu":2000, "mem":1000, "mouse":"50","mainboard":500 ｝ | ｛"cpu":1500, "mem":300 ｝ |
Desired result:
| _id | income | output |
|---|---|---|
| 1 | 1600 | 1720 |
| 2 | 3550 | 1800 |
Mongodb script:
| var fields = ["income", "output"]; db.computer.aggregate([ { $project:{ "values":{ $filter:{ input:{ "$objectToArray":"$$ROOT" }, cond:{ $in:[ "$$this.k", fields ] } } } } }, { $unwind:"$values" }, { $project:{ key:"$values.k", values:{ "$sum":{ "$let":{ "vars":{ "item":{ "$objectToArray":"$values.v" } }, "in":"$$item.v" } } } } }, {$sort: {"_id":-1}}, { "$group": { "_id": "$_id", 'income':{"$first": "$values"}, "output":{"$last": "$values"} }}, ]); |
SPL script:
| A | |
|---|---|
| 1 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") |
| 2 | =mongo_shell(A1,"computer.find()").fetch() |
| 3 | =A2.new(_id:ID,income.array().sum():INCOME,output.array().sum():OUTPUT) |
| 4 | >A1.close() |
Result:
| ID | INCOME | OUTPUT |
|---|---|---|
| 1 | 1600.0 | 1720.0 |
| 2 | 3550.0 | 1800.0 |
A1: Connect to MongoDB database.
A2: Read in data from computer table.
A3：Convert income value and output value respectively into a sequence to calculate the sum, and then join up with ID field to generate a new table sequence.
A4: Close the database connection.
The SPL code retrieves field values from each sub-document and calculates sum. It is much simpler than the MongoDB code. It appears that the embedded sub-documents and the embedded arrays are similar, but be careful not to confuse them. The scripts of handling them are slightly different.
3. A document grouped by segment
Count the records in each segment. For example, the following data is divided by ranges of sales; find the number of records in each segment.| _id | NAME | STATE | SALES |
|---|---|---|---|
| 1 | Ashley | New York | 11000 |
| 2 | Rachel | Montana | 9000 |
| 3 | Emily | New York | 8800 |
| 4 | Matthew | Texas | 8000 |
| 5 | Alexis | Illinois | 14000 |
Grouping conditions: 0-3000; 3000-5000; 5000-7500; 7500-10000; above 10000.
Desired result:
| Segment | number |
|---|---|
| 3 | 3 |
| 4 | 2 |
MongoDB script:
| var a_count=0; var b_count=0; var c_count=0; var d_count=0; var e_count=0; db.sales.find({ }).forEach( function(myDoc) { if (myDoc.SALES <3000) { a_count += 1; } else if (myDoc.SALES <5000) { b_count += 1; } else if (myDoc.SALES <7500) { c_count += 1; } else if (myDoc.SALES <10000) { d_count += 1; } else { e_count += 1; } } ); print("a_count="+a_count) print("b_count="+b_count) print("c_count="+c_count) print("d_count="+d_count) print("e_count="+e_count) |
It’s complicated to implement conditional grouping by segment in MongoDB because the database doesn’t provide the corresponding API. The above script is one of the possibilities to handle the computation.
SPL script:
| A | |
|---|---|
| 1 | [3000,5000,7500,10000,15000] |
| 2 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") |
| 3 | =mongo_shell(A2,"sales.find()").fetch() |
| 4 | =A3.groups(A1.pseg(int(~.SALES)):Segment;count(1): number) |
| 5 | >A2.close() |
SPL script explanation:
A1: Define the ranges of SALES.
A2: Connect to MongoDB database.
A3: Read in data from sales table.
A4: Count the records according to the SALES ranges. The pseg() function returns the segment number containing the current record. The int() function converts data into an integer.
A5: Close the database connection.
The use of pseg() function makes a concise script.
4. Union of same-structure documents
To union the following employee tables:Emp1:
| _id | NAME | STATE | HIREDATE | DEPT | SALARY |
|---|---|---|---|---|---|
| 1 | Ashley | New York | 2008-03-16 | Finance | 11000 |
| 2 | Rachel | Michigan | 2001-04-16 | Sales | 9000 |
| 3 | Emily | New York | 2011-07-11 | HR | 8800 |
| 4 | Matthew | Texas | 2003-03-06 | R&D | 8000 |
| 5 | Alexis | Illinois | 2008-03-10 | Sale | 14000 |
| _id | NAME | STATE | HIREDATE | DEPT | SALARY |
|---|---|---|---|---|---|
| 10 | Jacob | New York | 2009-03-14 | Sales | 13000 |
| 12 | Jessica | Florida | 2011-04-19 | Sales | 9500 |
| 13 | Daniel | New York | 2001-02-11 | HR | 7800 |
| 14 | Alyssa | Montana | 2013-09-06 | R&D | 8000 |
| 15 | Hannah | Florida | 2015-06-10 | Sales | 12500 |
Desired unioned table:
| _id | NAME | STATE | HIREDATE | DEPT | SALARY |
|---|---|---|---|---|---|
| 1 | Ashley | New York | 2008-03-16 | Finance | 11000 |
| 2 | Rachel | Michigan | 2001-04-16 | Sales | 9000 |
| 3 | Emily | New York | 2011-07-11 | HR | 8800 |
| 4 | Matthew | Texas | 2003-03-06 | R&D | 8000 |
| 5 | Alexis | Illinois | 2008-03-10 | Sale | 14000 |
| 10 | Jacob | New York | 2009-03-14 | Sales | 13000 |
| 12 | Jessica | Florida | 2011-04-19 | Sales | 9500 |
| 13 | Daniel | New York | 2001-02-11 | HR | 7800 |
| 14 | Alyssa | Montana | 2013-09-06 | R&D | 8000 |
| 15 | Hannah | Florida | 2015-06-10 | Sales | 12500 |
MongoDB script:
| db.emp1.aggregate([ {"$limit": 1}, { "$facet": { "collection1": [ {"$limit": 1}, { "$lookup": { "from": "emp1", "pipeline": [{"$match": {} }], "as": "collection1" }} ], "collection2": [ {"$limit": 1}, { "$lookup": { "from": "emp2", "pipeline": [{"$match": {} }], "as": "collection2" }} ] }}, { "$project": { "data": { "$concatArrays": [ {"$arrayElemAt": ["$collection1.collection1", 0] }, {"$arrayElemAt": ["$collection2.collection2", 0] }, ] } }}, {"$unwind": "$data"}, {"$replaceRoot": { "newRoot": "$data"} } ]) |
SPL script:
| A | |
|---|---|
| 1 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") |
| 2 | =mongo_shell(A1,"emp1.find()").fetch() |
| 3 | =mongo_shell(A1,"emp2.find()").fetch() |
| 4 | =A2|A3 |
| 5 | >A1.close() |
SPL script explanation:
A1: Connect to MongoDB database.
A2: Read in data from emp1 table.
A3: Read in data from emp2 table.
A4: Concatenate the two tables.
A5: Close the database connection.
The SPL script is natural and easy to understand. But for a SQL user who begins trying MongDB, they could be puzzled by the MongoDB-style data concatenation.
5. Association with embedded structures (1)
This involves two associated tables. One of table A’s field is associated with the sub-documents in table B, and the to-be-analyzed data is contained in the sub-documents. For example, the childs field of childsgroup table contains embedded arrays, in which the name item needs to be summarized.Test data:
history:
| _id | id | History | child_id |
|---|---|---|---|
| 1 | 001 | today worked | ch001 |
| 2 | 002 | Working | ch004 |
| 3 | 003 | now working | ch009 |
| _id | groupid | name | childs |
|---|---|---|---|
| 1 | g001 | group1 | {"id":"ch001","info":{"name":"a"}}, {"id":"ch002","info":{"name":"b"}} |
| 2 | g002 | group1 | {"id":"ch004","info":{"name":"c"}}, {"id":"ch009","info":{"name":"d"}} |
The child_id field in history table associates with childs.id in childsgroup table. Below is the desired joining result:
{
"_id" : ObjectId("5bab2ae8ab2f1bdb4f434bc3"),
"id" : "001",
"history" : "today worked",
"child_id" : "ch001",
"childInfo" :
{
"name" : "a"
}
………………
}
MongoDB script:
| db.history.aggregate([ {$lookup: { from: "childsgroup", let: {child_id: "$child_id"}, pipeline: [ {$match: { $expr: { $in: [ "$$child_id", "$childs.id"] } } }, {$unwind: "$childs"}, {$match: { $expr: { $eq: [ "$childs.id", "$$child_id"] } } }, {$replaceRoot: { newRoot: "$childs.info"} } ], as: "childInfo" }}, {"$unwind": "$childInfo"} ]) |
SPL script:
| A | |
|---|---|
| 1 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") |
| 2 | =mongo_shell(A1,"history.find()").fetch() |
| 3 | =mongo_shell(A1,"childsgroup.find()").fetch() |
| 4 | =A3.conj(childs) |
| 5 | =A2.join(child_id,A4:id,info.name:name) |
| 6 | >A1.close() |
Joining result:
| _id | id | history | child_id | name |
|---|---|---|---|---|
| 1 | 001 | today worked | ch001 | a |
| 2 | 002 | working | ch004 | c |
| 3 | 003 | now working | ch009 | d |
SPL script explanation:
A1: Connect to MongoDB database.
A2: Read in data from history table.
A3：Read in data from childsgroup table.
A4: Concatenate the array values in childsgroup table’s childs field into a table sequence.
A5: Join history table with A4’s childs table via the associated fields child_id and id and append a name field to generate a new table sequence.
A6：Close the database connection.
The SPL script is simple and efficient.
6. Association with embedded structures (2)
There are two associated tables. Table A is associated with table B’s embedded documents and combine table A’s data under the embedded documents. For example, txtPost table’s Comment field contains array values; combine comment_content field of txtComment table under the arrays.txtComment：
| _ID | comment_no | comment_content |
|---|---|---|
| 1 | 143 | test test |
| 2 | 140 | math |
| _ID | post_no | Comment |
|---|---|---|
| 1 | 48 | [{"comment_no" : 143, "comment_group" : 1} ] |
| 2 | 47 | [{"comment_no" : 140, "comment_group" : 2}， {"comment_no" : 143, "comment_group" : 3} ] |
Desired result:
| _ID | post_no | Comment |
|---|---|---|
| 1 | 48 | [{"comment_no" : 143, "comment_group" : 1，"comment_content" : "test test"} ] |
| 2 | 47 | [{"comment_no" : 140, "comment_group" : 2，"comment_content" : "math"}， {"comment_no" : 143, "comment_group" : 3，"comment_content" : "test test"} ] |
MongoDB script:
| db.getCollection("txtPost").aggregate([ {"$unwind": "$comment"}, { "$lookup": { "from": "txtComment", "localField": "comment.comment_no", "foreignField": "comment_no", "as": "comment.comment_content" }}, {"$unwind": "$comment.comment_content"}, {"$addFields": { "comment.comment_content":"$comment.comment_content.comment_content"}}, { "$group": { "_id": "$_id", 'post_no':{"$first": "$post_no"}, "comment": {"$push": "$comment"} }}, ]).pretty() |
SPL script:
| A | |
|---|---|
| 1 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") |
| 2 | =mongo_shell(A1,"txtPost.find()").fetch() |
| 3 | =mongo_shell(A1,"txtComment.find()").fetch() |
| 4 | =A2.conj(comment.derive(A2.post_no:pno)) |
| 5 | =A4.join(comment_no,A3:comment_no,comment_content:Content) |
| 6 | =A5.group(pno;~:comment) |
| 7 | >A1.close() |
SPL script explanation:
A1：Connect to MongoDB database.
A2：Read data from txtPost table.
A3：Read data from txComment table.
A4：Create a table sequence with A2’s Comment field and post_no field that is renamed pno.
A5：Join A4’s table sequence with A3’s table sequence via comment_no and append comment_content field that is renamed Content.
A6：Group records by pno and return the current table; ~ represents the current record.
A7：Close the database connection.
7. Association with embedded structures (3)
This involves two associated tables. Table A is associated with the sub-documents in table B, and the to-be-returned information is contained in the joined record. For example, product field in collection2 contains array values and the joined result needs to include fields in isCompleted array in collection2.Test data:
collection1:
MongoDB script:
| db.collection1.aggregate([{ $lookup: { from: "collection2", let: {order: "$order", lot: "$lot"}, pipeline: [{ $match: { $expr:{$in: [ { order: "$$order", lot: "$$lot"}, "$product"] } } }], as: "isCompleted" } }, { $addFields: { "isCompleted": {$arrayElemAt: [ "$isCompleted", 0] } } }, { $addFields: { // add the required fields to the top level structure "isCompleted": "$isCompleted.isCompleted", "serialNo": "$isCompleted.serialNo", "batchNo": "$isCompleted.batchNo" } }]) |
$lookup performs an associated query between the two tables. The first $addFields gets the first field from isCompleted array, and the second $addFields adds the other required fields.
SPL script:
| A | |
|---|---|
| 1 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") |
| 2 | =mongo_shell(A1,"collection1.find()").fetch() |
| 3 | =mongo_shell(A1,"collection2.find()").fetch() |
| 4 | =A3.conj(A2.select(order:A3.product.order,lot:A3.product.lot).derive(A3.serialNo:sno,A3.batchNo:bno)) |
| 5 | >A1.close() |
SPL script explanation:
A1：Connect to MongoDB database.
A2：Read data from collection1 table.
A3：Read data from collection2 table.
A4：Query A2’s table according to order field and lot field, add A3’s serialNo field and batchNo field, and return the joined tabled table sequence.
A5：Close the database connection.
This instance filters the sub-documents to get the eligible records to form a new table sequence.
8. Multi-field grouping & summarization
Group data by the grouping field and calculate the sum in each group and sub-group. For example, group the following data by addr and book to find the number under different books and the total number of books in each group.| addr | book |
|---|---|
| address1 | book1 |
| address2 | book1 |
| address1 | book5 |
| address3 | book9 |
| address2 | book5 |
| address2 | book1 |
| address1 | book1 |
| address15 | book1 |
| address4 | book3 |
| address5 | book1 |
| address7 | book11 |
| address1 | book1 |
Desired result:
| _id | Total | books | Count |
|---|---|---|---|
| address1 | 4 | book1 | 3 |
| book5 | 1 | ||
| address15 | 1 | book1 | 1 |
| address2 | 3 | book1 | 2 |
| book5 | 1 | ||
| address3 | 1 | book9 | 1 |
| address4 | 1 | book3 | 1 |
| address5 | 1 | book1 | 1 |
| address7 | 1 | book11 | 1 |
MongoDB script:
| db.books.aggregate([ { "$group": { "_id": { "addr": "$addr", "book": "$book" }, "bookCount": {"$sum": 1} }}, { "$group": { "_id": "$_id.addr", "books": { "$push": { "book": "$_id.book", "count": "$bookCount" }, }, "count": {"$sum": "$bookCount"} }}, {"$sort": { "count": -1} }, { "$project": { "books": {"$slice": [ "$books", 2] }, "count": 1 }} ]).pretty() |
SPL script:
| A | |
|---|---|
| 1 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") |
| 2 | =mongo_shell(A1,"books.find()") |
| 3 | =A2.groups(addr,book;count(book): Count) |
| 4 | =A3.groups(addr;sum(Count):Total) |
| 5 | =A3.join(addr,A4:addr,Total) |
| 6 | >A1.close() |
Result:
| Address | book | Count | Total |
|---|---|---|---|
| address1 | book1 | 3 | 4 |
| address1 | book5 | 1 | 4 |
| address15 | book1 | 1 | 1 |
| address2 | book1 | 2 | 3 |
| address2 | book5 | 1 | 3 |
| address3 | book9 | 1 | 1 |
| address4 | book3 | 1 | 1 |
| address5 | book1 | 1 | 1 |
| address7 | book11 | 1 | 1 |
SPL script explanation:
A1: Connect to MongoDB database.
A2: Read data from books table.
A3: Group data by addr and book to count different books.
A4: Group A3’s table sequence by addr and find the total number of books in each group.
A5: Join A3 and A4 via addr and add Total field to the joined table sequence.
A6: Close the database connection.
9. Association with selected fields
Create a new table through association and with selected fields.collection1:
| user1 | user2 | income |
|---|---|---|
| 1 | 2 | 0.56 |
| 1 | 3 | 0.26 |
| user1 | user2 | output |
|---|---|---|
| 1 | 2 | 0.3 |
| 1 | 3 | 0.4 |
| 2 | 3 | 0.5 |
Desired result:
| user1 | user2 | income | output |
|---|---|---|---|
| 1 | 2 | 0.56 | 0.3 |
| 1 | 3 | 0.26 | 0.4 |
MongoDB script:
| db.c1.aggregate([ { "$lookup": { "from": "c2", "localField": "user1", "foreignField": "user1", "as": "collection2_doc" }}, {"$unwind": "$collection2_doc"}, { "$redact": { "$cond": [ {"$eq": [ "$user2", "$collection2_doc.user2"] }, "$$KEEP", "$$PRUNE" ] }}, { "$project": { "user1": 1, "user2": 1, "income": "$income", "output": "$collection2_doc. output" }} ]).pretty() |
SPL script:
| A | |
|---|---|
| 1 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") |
| 2 | =mongo_shell(A1,"c1.find()").fetch() |
| 3 | =mongo_shell(A1,"c2.find()").fetch() |
| 4 | =A2.join(user1:user2,A3:user1:user2,output) |
| 5 | >A1.close() |
SPL script explanation:
A1: Connect to MongoDB database.
A2: Read data from collection1 table.
A3: Read data from collection2 table.
A4: Join the two tables by user1 and user2, append A3’s output field and return a new table sequence.
A5: Close the database connection.
Combine some of fields in two tables via a join into a new table.
10. Multi-table association
This is the association between more than two tables to get a merged table.Doc1:
| _id | firstName | lastName |
|---|---|---|
| U001 | shubham | verma |
| _id | userId | address | mob |
|---|---|---|---|
| 2 | U001 | Gurgaon | 9876543200 |
| _id | userId | fbURLs | twitterURLs |
|---|---|---|---|
| 3 | U001 | http://www.facebook.com | http://www.twitter.com |
Merged result:
| db.doc1.aggregate([ {$match: { _id: ObjectId("5901a4c63541b7d5d3293766") } }, { $lookup: { from: "doc2", localField: "_id", foreignField: "userId", as: "address" } }, { $unwind: "$address" }, { $project: { "address._id": 0, "address.userId": 0, "address.mob": 0 } }, { $lookup: { from: "doc3", localField: "_id", foreignField: "userId", as: "social" } }, { $unwind: "$social" }, { $project: { "social._id": 0, "social.userId": 0 } } ]).pretty(); |
SPL script:
| A | |
|---|---|
| 1 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") |
| 2 | =mongo_shell(A1,"doc1.find()").fetch() |
| 3 | =mongo_shell(A1,"doc2.find()").fetch() |
| 4 | =mongo_shell(A1,"doc3.find()").fetch() |
| 5 | =A2.join(_id,A3:userId,address,mob) |
| 6 | =A5.join(_id,A4:userId,fbURLs,twitterURLs) |
| 7 | >A1.close() |
Obviously, the SPL script is concise and coherent.
11. Conditional search
Find the eligible records according to a specified array. In this example, the specified array is [“Chemical”, “Biology”, “Math”].Test data:
| _id | Name | Subject |
|---|---|---|
| 1 | jacker | [English, Chemical，Math, Physics] |
| 2 | tom | [Chinese, Chemical，Math, Biology] |
| 3 | Mint | [Chinese, History] |
Desired result:
| _id | Name | Subject |
|---|---|---|
| 1 | Jacker | [Chemical,Math] |
| 2 | Tom | [Chemical,Math,Biology] |
MongoDB script:
| var field = ["Chemical", "Biology", "Math"] db.student.aggregate([ { "$project": { "name":1, "subjects": { "$filter": { "input": "$lesson", "cond": { "$in": [ "$$this", field ] } } }, }}, {"$project": {"name":1,"subjects":1,"sizeOfsubject": {"$size": "$subjects"} }}, {$match: { "sizeOfsubject":{ $gt: 0}}} ]) |
SPL script:
| A | |
|---|---|
| 1 | [Chemical, Biology, Math] |
| 2 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") |
| 3 | =mongo_shell(A2,"student.find()").fetch() |
| 4 | =A3.select(subject^A1!=[]) |
| 5 | =A4.new(name, ~.subject^A1) |
| 6 | >A2.close() |
SPL script explanation:
A1：Define the searching condition, which is an array of subjects.
A2：Connect to MongoDB database.
A3：Read data from student table.
A4：Get records where the Subject value contains at least one of the subjects in the specified array.
A5：Generate a new table sequence consisting of name field and subject field to which a matching subject is added.
A6：Close the database connection.
The SPL script is concise and easy to understand.
12. Associated query by array values
Get records from a table according to the array field in its associated table and generate a new table with certain fields.Test data:
users:
| _id | Name | workouts |
|---|---|---|
| 1000 | xxx | [2,4,6] |
| 1002 | yyy | [1,3,5] |
| _id | Date | Book |
|---|---|---|
| 1 | 1/1/2001 | Othello |
| 2 | 2/2/2001 | A Midsummer Night's Dream |
| 3 | 3/3/2001 | The Old Man and the Sea |
| 4 | 4/4/2001 | GULLIVER’S TRAVELS |
| 5 | 5/5/2001 | Pickwick Papers |
| 6 | 6/6/2001 | The Red and the Black |
Desired result:
| Name | _id | Date | Book |
|---|---|---|---|
| xxx | 2 | 2/2/2001 | A Midsummer Night's Dream |
| xxx | 4 | 4/4/2001 | GULLIVER’S TRAVELS |
| xxx | 6 | 6/6/2001 | The Red and the Black |
| yyy | 1 | 1/1/2001 | Othello |
| yyy | 3 | 3/3/2001 | The Old Man and the Sea |
| yyy | 5 | 5/5/2001 | Pickwick Papers |
MongoDB script:
| db.users.aggregate([ { "$lookup": { "from" : "workouts", "localField" : "workouts", "foreignField" : "_id", "as" : "workoutDocumentsArray" }}, {$project: { _id:0,workouts:0} } , {"$unwind": "$workoutDocumentsArray"}, {"$replaceRoot": { "newRoot": { $mergeObjects: [ "$$ROOT", "$workoutDocumentsArray"] } } }, {$project: { workoutDocumentsArray: 0} } ]).pretty() |
SPL script:
| A | |
|---|---|
| 1 | =mongo_open("mongodb://127.0.0.1:27017/raqdb") |
| 2 | =mongo_shell(A1,"users.find()").fetch() |
| 3 | =mongo_shell(A1,"workouts.find()").fetch() |
| 4 | =A2.conj(A3.select(A2.workouts^~.array(_id)!=[]).derive(A2.name)) |
| 5 | >A1.close() |
SPL script explanation:
A1: Connect to MongoDB database.
A2: Read data from users table.
A3: Read data from workouts table.
A4: Get records from workouts table where _id field is included in workouts field of A2’s table sequence, and add name field to the records.
A5: Close the database connection.
The operation is considered normal only if the matching result isn’t null. So _id field is converted into a sequence to make the comparison convenient in A4.
Compared with relational databases, MongoDB stores data in a more complicated, more flexible way, has powerful and adaptable query syntax, and provides various functions and rich cooperative possibilities between them. It’s not easy to be proficient in MongoDB. esProc, however, features discreteness and ease of use to become the NoSQL database’s right-hand and help it play to its full potential.
No comments:
Post a Comment