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

MongoDB belongs to the document database based on distributed file storage in NoSql. The document structure of bson format is closer to our description of all aspects of the object attributes. In the process of using MongoDB to store data, sometimes it is inevitable to query the associated tables. Since MongoDB 3.2, it provides $lookup for associated table queries, which makes the query function much better. However, in the implementation of the application scenario, the environment encountered is complex, the problem solving is not easy, and the script is not easy to write. Fortunately, with the help of the aggregator SPL language, it is relatively easy to deal with.

In this paper, we will discuss and analyze the problems of association operation in MongoDB, and improve it through the aggregator SPL language to facilitate users to use MongoDB. The discussion will be divided into the following sections:

1. Associated nested structure case 1... .. one

two。 Associated nested structure case 2. .. three

3. Associated nested structure case 3. .. four

4. Two tables are associated with the query. . six

5. Multi-table association query... . eight

6. Look up the array in the association table. ten

The Java application calls the DFX script... twelve

1. Associated nested structure case 1

Two associated tables, Table An is associated with the embedded document information in Table B, and the returned information is embedded in the document. The table childsgroup field childs is a nested array structure under which the merged information name is required.

Test data:

History:

_ ididHistorychild_id1001today workedch0012002Workingch0043003now workingch009childsgroup:

_ idgidnamechilds1g001group1 {"id": "ch001", "info": {"name": "a", mobile:1111}}, {"id": "ch002", "info": {"name": "b", mobile:2222} 2g002group1 {"id": "ch004", "info": {"name": "c", mobile:3333}}, {"id": "ch009", "info": {"name": "d", mobile:4444}} child_id in table History is associated with childs.id in table childsgroup I hope to get the following results:

{

"_ id": ObjectId ("5bab2ae8ab2f1bdb4f434bc3")

Id: 001

"history": "today worked"

"child_id": "ch001"

"childInfo":

{

"name": "a"

"mobile": 1111

}

.

}

Mongo 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"}

])

This script uses several functions: lookup, pipeline, match, unwind, and replaceRoot. It is not easy for mongodb users to write such a complex script, so let's take a look at how the spl script is implemented:

SPL script (filename: childsgroup.dfx)

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

6 > A1.close ()

Associated query results:

_ ididhistorychild_idinfo1001today workedch001 [a,1111] 2002workingch004 [c,3333] 3003now workingch009 [d,4444]

Script description:

A1: connect to the mongodb database.

A2: get the data in the history table.

A3: get the data in the childsgroup table.

A4: extract the childs data from childsgroup and merge it into a sequence table.

A5: the child_id in the table history is associated with the id in the table childs. Append the info field and return the ordered table.

A6: close the database connection.

Compared with the mongodb script writing method, the difficulty of SPL script is reduced a lot, the train of thought is clearer, and there is no need to be familiar with the usage of mongo function, and how to combine and deal with data, which saves a lot of time.

two。 Associated nested structure case 2

Two associated tables, Table An is associated with the embedded document information in Table B, merging the information into the embedded document. The table txtPost field comment is a nested array structure under which you need to merge comment_content.

TxtComment:

_ IDcomment_nocomment_content1143test test2140math

TxtPost

_ IDpost_noComment148 [{"comment_no": 143, "comment_group": 1}] 247 [{"comment_no": 140, "comment_group": 2}

{"comment_no": 143, "comment_group": 3}]

Expected results:

_ IDpost_noComment148 [{"comment_no": 143," comment_group ": 1," comment_content ":" test test "}] 247 [{" comment_no ": 140," comment_group": 2, "comment_content": "math"}

{"comment_no": 143," comment_group ": 3," comment_content ":" test test "}]

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

Table txtPost is disassembled into records according to comment, and then associated with table txtComment, the results are put into an array, then the array is disassembled into records, the comment_content value is moved to comment, and finally grouped and merged.

SPL script:

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

Associated query results:

PnoComment47 [140,2pd47, …] , [143,47,47, …] ] 48 [[143,1pd48, …]]

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.

Mongo and SPL scripts are implemented in a similar way, which converts the data of the nested structure into the data of the column structure, and then groups and merges. But the implementation of the SPL script is simpler and clearer.

3. 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.

Both Mongo and SPL scripts achieve the desired results. SPL clearly implements filtering from the embedded structure in the data record and merging the qualified data into a new ordered table.

4. 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.

Both Mongo and SPL scripts achieve the desired results. SPL merges the different fields of two associated tables into a new table through join, similar to relational database usage.

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

Both Mongo and SPL scripts achieve the desired results. This SPL 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 very obvious.

6. 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 (users.dfx):

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 sequence table A2, and append the name field. Returns the ordered table of the merge.

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.

Both Mongo and SPL scripts achieve the desired results. From the perspective of script implementation process, SPL is highly integrated and flexible, which simplifies the program a lot.

The 7.Java application calls the DFX script

After associating the MongoDB data with a SPL script, the results can be easily used by java applications. The aggregator provides a JDBC driver, which is accessed by JDBC stored procedures, the same as calling stored procedures. (for the specific configuration of JDBC, please refer to the "basic use of JDBC" section in the aggregator tutorial)

The main procedure of calling Java is as follows:

Public void testUsers () {

Connection con = null

Com.esproc.jdbc.InternalCStatement st

Try {

/ / establish a connection

Class.forName ("com.esproc.jdbc.InternalDriver")

Con= DriverManager.getConnection ("jdbc:esproc:local://")

/ / call the stored procedure, where users is the file name of dfx

St = (com. Esproc.jdbc.InternalCStatement) con.prepareCall ("call users > ()")

/ / execute stored procedures

St.execute ()

/ / get the result set

ResultSet rs = st.getResultSet ()

.

Catch (Exception e) {

System.out.println (e)

}

As you can see, the aggregator is easily embedded in Java applications by following the standard JDBC method when using it. At the same time, the aggregator also supports ODBC drivers, so it is easy to integrate into other languages that support ODBC.

The data structure stored by Mongo is more complex and flexible than the relational database, and the query language it provides is also very strong and adaptable. At the same time, it needs to know a lot of functions, and the combination of functions is infinitely changing, so it is not easy to master and apply it skillfully. The discreteness and ease of use of the aggregator can just make up for the deficiency of Mongo. While reducing the learning cost, complexity and difficulty of mongo, the function of mongo can be fully demonstrated.

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.