SPL-driven MongoDB Hackers

 

  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.