Shulou(Shulou.com)06/02 Report--

MongoDB belongs to the document database based on distributed file storage in NoSql. It is the most functional non-relational database and most like a relational database. It supports a very loose data structure, which is similar to json's bson format, so it can store more complex data types. The most important feature of Mongo is that the query language it supports is very powerful, its syntax is somewhat similar to the object-oriented query language, and it can almost achieve most of the functions similar to the single table query in relational database, but it is not easy to write. If the aggregator can be combined with SPL language, it is relatively easy to deal with.

Now we discuss and analyze the computing problems of MongoDB, and improve it through the aggregator SPL language to make it convenient for users to use MongoDB. It is explained from the following situation:

1. Statistics of the array structure embedded in a single table. one

two。 Single table embedded document summation. three

3. Segmented grouping structure. five

4. Isomorphic table merging. six

5. Associated nested structure case 1. eight

6. Associated nested structure case 2. ten

7. Associated nested structure case 3. eleven

8. Multi-field grouping statistics. fourteen

9. Two tables are associated with the query. sixteen

10. Multi-table association query. seventeen

11. Specify an array lookup. nineteen

twelve。 An array lookup in an associated table. twenty

1. Statistics of embedded array structure in single table

Statistical processing of data in nested array structures. Inquire about the average score of the examination subjects and the total score of each student.

Test data:

_ idnamesexScroe1TomF [{"lesson": "Physics", "mark": 60}

{"lesson": "Chemical", "mark": 72}] 2JerryM [{"lesson": "Physics", "mark": 92}

{"lesson": "Math", "mark": 81}]

Expected statistical results:

Physics76

Tom132Chemical72

Jerry173Math81

Script:

Db.student.aggregate ([

{$unwind: "$scroe"}

{$group: {

"_ id": {"lesson": "$scroe.lesson"}

"qty": {"$avg": "$scroe.mark"}

}

}

])

Db.student.aggregate ([

{$unwind: "$scroe"}

{$group: {

"_ id": {"name": "$name"}

"qty": {"$sum": "$scroe.mark"}

}

}

])

As the scroe of each subject is based on the array structure of course items and achievement records, it is necessary to disassemble it before statistics, correspond the scores of each subject to the students, and then realize the grouping calculation. This requires familiarity with the combined application of unwind and group.

SPL script:

AB1=mongo_open ("mongodb://127.0.0.1:27017/raqdb")

2=mongo_shell (A1, "student.find ()"). Fetch ()

3=A2.conj (scroe) .groups (lesson:LESSON;avg (mark): AVG)

4=A2.new (name:NAME,scroe.sum (mark): TOTAL)

5 > A1.close ()

Total score by subject

LESSONAVGChemical72.0Math81.0Physics76.0

The total score of each student

NAMETOTALTom132Jerry173

Script description:

A1: connect to the mongo database.

A2: get the data in the student table.

A3: merge the scroe data into a sequence table, then group by course, and calculate the average score.

A4: after counting the scores of each student, return the order table named NAME and TOTAL. The new function means to generate a new ordinal table.

A5: close the database connection.

This more commonly used example of nested structure statistics has been encountered by many people and needs to be dismantled first, mainly because they are familiar with mongodb's handling of nested data structures.

two。 Summation of embedded documents in single table

For the summation of the data in the embedded document, the following is to count the number of income,output in each record.

Test data:

_ idincomeoutput1 {"cpu": 1000, "mem": 1000, "mouse": "100"} {"cpu": 1000, "mem": 600,120 "mouse"} 2 {"cpu": 2000, "mem": 1000

"mouse": "50", "mainboard": 500} {"cpu": 1500, "mem": 1500} expect statistical results

_ idincomeoutput116001720235501800Mongodb 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 income,output information in an array, disassembles it into records with unwind, accumulates and sums up the values, and merges the data according to _ id.

SPL script:

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

Statistical results

IDINCOMEOUTPUT11600.01720.023550.01800.0

Script description:

A1: connect to the database

A2: get data from the computer table

A3: convert the data in income and output fields into sequence summation respectively, and then combine with ID to generate a new order table.

A4: close the database connection.

Getting the field value of the child record and then summing it is much simpler than the mongo script. This embedded document is somewhat similar to the embedded array in organizational structure, and it is easy to be confused carelessly. Note that the script is different from the scroe array structure in the example above.

3. Segmented grouping structure

Count the number of records in each paragraph. The following is broken down by sales volume, and the amount of data in each segment is counted as follows:

_ idNAMESTATESALES1AshleyNew York110002RachelMontana90003EmilyNew York88004MatthewTexas80005AlexisIllinois14000

Segmentation method: 0-3000-5000-5000-7500-10000 or more.

Expected results:

Segmentnumber3342

Mongo 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 A1.close ()

Script description:

A1: connect to the mongodb database.

A2: get the data in the txtPost table.

A3: get the data in the txtComment table.

A4: combine comment and post_no under sequence table A2 into a sequence table, where post_no is renamed to pno.

A5: sequence table A4 is associated with sequence table A3 through comment_no, appends the field comment_content, and renames it to Content.

A6: returns the sequence table according to pno grouping, and ~ represents the current record.

A7: close the database connection.

7. Associated nested structure case 3

Two associated tables, Table An is associated with the embedded document information in Table B, and the returned information is on the record. The table collection2 field product is a nested array structure, and the information returned is fields such as isCompleted.

Test data:

Collection1:

{

_ id: '5bc2e44a106342152cd83e97'

Description:

{

Status: 'Good'

Machine:'X'

}

Order:'A'

Lot:'1'

}

Collection2:

{

_ id: '5bc2e44a106342152cd83e80'

IsCompleted: false

SerialNo:'1'

BatchNo:'2'

Product: [/ / note the subdocuments here

{order: 'Aids, lot:' 1'}

{order: 'Aids, lot:' 2'}

]

}

Expect the result

{

_ id: 5bc2e44a106342152cd83e97

Description:

{

Status: 'Good'

Machine:'X'

}

Order:'A'

Lot:'1'

IsCompleted: false

SerialNo:'1'

BatchNo:'2'

}

Mongo 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 two tables associative query. The first addFields gets the first record of the isCompleted array, and the latter addFields is converted into several required field information.

SPL script:

AB1=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.serialNopursnoMagnesia A3.batchNorangbno))

5 > A1.close ()

Script description:

A1: connect to the mongodb database.

A2: get the data in the collection1 table.

A3: get the data in the collection2 table.

A4: according to the condition order, lot queries the records from the sequence table A2, and then appends the fields serialNo and batchNo in the order table A3 to return the merged order table.

A5: close the database connection.

It is implemented to filter from the embedded structure in the data record and merge the qualified data into a new ordered table.

8. Multi-field grouping statistics

Statistics of the total number and the number of sub-items under the classification. The following statistics classify the number of book by addr and the number of different book under it.

Addrbookaddress1book1address2book1address1book5address3book9address2book5address2book1address1book1address15book1address4book3address5book1address7book11address1book1

Expected results:

_ idTotalbooksCountaddress14book13

Book51address151book11address23book12

Book51address31book91address41book31address51book11address71book111

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

First count the book number according to addr,book grouping, then count the book number according to addr grouping, and adjust the display order.

SPL script:

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

Calculation results:

AddressbookCountTotaladdress1book134address1book514address15book111address2book123address2book513address3book911address4book311address5book111address7book1111

Script description:

A1: connect to the mongodb database.

A2: get the data in the books table.

A3: count book by addr,book grouping

A4: then count the number of book according to addr.

A5: associate the Total in A4 by addr and merge them into the sequence table.

A6: close the database connection.

9. Query associated with two tables

Select the required fields from the associated table to combine the new table.

Collection1:

User1user2income120.56130.26

Collection2:

User1user2output120.3130.4230.5

Expected results:

User1user2incomeoutput120.560.3130.260.4

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

The two tables of lookup are associated with the query, redact traverses the records according to the conditions, and project selects the fields to display.

SPL script:

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

Script description:

A1: connect to the mongodb database.

A2: gets the data in the C1 table.

A3: get the data in the c2 table.

A4: the two tables are associated by the field user1,user2, and the output field in the order table A3 is appended to return the order table.

A5: close the database connection.

Merge the different fields of two associated tables into a new table through join.

10. Multi-table association query

Associative queries with more than two tables are combined into one large table.

Doc1:

_ idfirstNamelastNameU001shubhamverma

Doc2:

_ iduserIdaddressmob2U001Gurgaon9876543200

Doc3:

_ iduserIdfbURLstwitterURLs3U001 http://www.facebook.comhttp://www.twitter.com

The result of the merger:

{

"_ id": ObjectId ("5901a4c63541b7d5d3293766")

"firstName": "shubham"

"lastName": "verma"

"address": {

"address": "Gurgaon"

}

"social": {

"fbURLs": "http://www.facebook.com","

"twitterURLs": "http://www.twitter.com""

}

}

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

Due to the Mongodb data structure, the writing method is also diversified, and the presentation is also different.

SPL script:

AB1=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 the above example, except that there is one more associated table, and each join adds new fields, and finally overlays to form a large table. .

The simplicity and unity of SPL scripts are obvious.

11. Specify array lookup

Finds qualified records from the specified array. The array given is: ["Chemical", "Biology", "Math"].

Test data:

_ idNameLesson1jacker [English, Chemical,Math, Physics] 2tom [Chinese, Chemical,Math, Biology] 3Mint [Chinese, History]

Expected results:

_ idNameLesson1Jacker [Chemical,Math] 2Tom [Chemical,Math,Biology]

Mongodb script

Var field = ["Chemical", "Biology", "Math"]

Db.student.aggregate ([

{"$project": {

"name": 1

"lessons": {

"$filter": {

"input": "$lesson"

"cond": {

"$in": [

"$$this"

Field

]

}

}

}

}}

{"$project": {"name": 1, "lessons": 1, "sizeOflesson": {"$size": "$lessons"}

{$match: {"sizeOflesson": {$gt: 0}

])

Query the students whose elective courses include ["Chemical", "Biology", "Math".

SPL script:

AB1 [Chemical, Biology, Math]

2=mongo_open ("mongodb://127.0.0.1:27017/raqdb")

3=mongo_shell (A2, "student.find ()"). Fetch ()

4=A3.select (lesson ^ A1! = [])

5=A4.new (name, ~ .lesson ^ A1)

6 > A2.close ()

Script description:

A1: defines an array of query criteria accounts.

A2: connect to the mongodb database.

A3: get the data in the student table.

A4: query the account records that exist in the array.

A5: generate a new ordinal table with fields of name and lesson, where eligible values are stored in field lesson

A6: close the database connection.

The implementation of the aggregator for query records in a given array is more concise and easy to understand.

twelve。 Array lookup in the associated table looks for eligible records from the associated table record data set and combines the given fields into a new table.

Test data:

Users:

_ idNameworkouts1000xxx [2,4,6] 1002yyy [1,3,5]

Workouts:

_ idDateBook11/1/2001Othello22/2/2001A Midsummer Night's Dream33/3/2001The Old Man and the Sea44/4/2001GULLIVER'S TRAVELS55/5/2001Pickwick Papers66/6/2001The Red and the Black

Expected results:

Name_idDateBookxxx22/2/2001A Midsummer Night's Dreamxxx44/4/2001GULLIVER'S TRAVELSxxx66/6/2001The Red and the Blackyyy11/1/2001Othelloyyy33/3/2001The Old Man and the Seayyy55/5/2001Pickwick Papers

Mongo 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 users,workouts query results of the associated table into an array, then disassemble the array, improve the location of the child records, and remove the unwanted fields.

SPL script:

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

Script description:

A1: connect to the mongodb database.

A2: get the data in the users table.

A3: get the data in the workouts table.

A4: the _ id value of query order table A3 exists in the record of workouts array in order table A2, and appends the name field to return the merged order table.

A5: close the database connection.

Since it is necessary to obtain that the intersection of the sequence is not empty, the _ id is converted into a sequence.

The data structure stored by Mongo is more complex and flexible than the associated database, and the query language it provides is also very strong and can adapt to different situations. It needs to know a lot of functions, and the combination of functions is infinitely changing, so it is not easy to master and be familiar with the application of it. The discreteness and ease of use of the aggregator can just make up for the deficiency of Mongo. It reduces the learning cost of mongo and the complexity and difficulty of using mongo, and makes the function of mongo more fully displayed. At the same time, it also hopes that mongo will be favored by the majority of enthusiasts.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.