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

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"}    }   } ] )

  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:

	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"}   }}, ]);

  $filter stores part of the data in income and output as an array; $unwind splits the array into records; calculate the sum of items in each record, sort and group records by _id and join up the fields.

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

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.

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

Emp2:

_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"} } ])

  $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:

	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

childsgroup:

_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"} ])

  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:

	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

txtPost:

_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()

  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:

	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:

{
   _id: '5bc2e44a106342152cd83e97',
   description:
   {
     status: 'Good',
     machine: 'X'
   },
   order: 'A',
   lot: '1'
};

collection2：

{
   _id: '5bc2e44a106342152cd83e80',
   isCompleted: false,
   serialNo: '1',
   batchNo: '2',
   <!-- note the subdocuments here-->   
   product: [ 
     { order: 'A', lot: '1' },
     { order: 'A', lot: '2' }
   ]
}

Desired result:

{
   _id: 5bc2e44a106342152cd83e97,
   description:
   {
      status: 'Good',
      machine: 'X',
   },
   order: 'A',
   lot: '1' ,
   isCompleted: false,
   serialNo: '1',
   batchNo: '2'
}

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()

  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:

	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

collection2:

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()

  $lookup performs an associated query. $redact traverses records to perform conditional handling. $project selects the desired fields.

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

Doc2:

_id	userId	address	mob
2	U001	Gurgaon	9876543200

Doc3:

_id	userId	fbURLs	twitterURLs
3	U001	http://www.facebook.com	http://www.twitter.com

Merged result:

{
     "_id" : ObjectId("5901a4c63541b7d5d3293766"),
     "firstName" : "shubham",
     "lastName" : "verma",

     "address" : {
         "address" : "Gurgaon"
     },
     "social" : {
         "fbURLs" : "http://www.facebook.com",
         "twitterURLs" : "http://www.twitter.com"
     }
}

MongoDB script:

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();

  There can be different scripts to do this and various merged results in MongoDB thanks to the database’s unique data structure.

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()

  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.

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}}} ])

  Find students who select the courses including chemical, biology and math.

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]

workouts:

_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()

  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:

	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.