"A MongoDB nested subdocument can flexibly record attributes with multiple fields in a one-to-man .."

blackduckie RaqForum 28 No.
350 View • 3 Years ago

Load MongoDB Nested Subdocuments to the Database

A MongoDB nested subdocument can flexibly record attributes with multiple fields in a one-to-many relationship. We have a collection users, whose subdocument friends has fields name and tel. We are trying to structuralize the collection and export its data to mysql database. Below is the source data:

{"_id" : ObjectId("5f57421c1796b4875dc90187"),
"name" : "jim",
"friends" : [
   {"name":"tom", "tel":"186123500500"},
   {"name":"jack", "tel":"181123500511"},
   {"name":"luke", "tel":"138123500522"},
   {"name":"rose","tel":"189123500533"}
]},

{"_id" : ObjectId("5f57421c1796b4875dc90188"),
"name" : "jack",
"friends" : [
   {"name":"james", "tel":"186123500544"},
   {"name":"sam", "tel":"186123500555"},
   {"name":"peter", "tel":"186123500566"}
]}
…

esProc SPL can split each friends value in the user collection into multiple rows and export them to the database.

Directions:
1. Make sure that there is a corresponding user table in mysql database that has fields name, friend, and tel.

2. Write the following SPL script user.dfx in esProc:

	A	B
1	=mongo_open("mongodb://localhost:27017/local")	/ Connect to MongoDB database
2	=mongo_shell(A1,"user.find(,{_id:0})")	/ Get data from the user collection
3	=A2.news(friends; name:friend, tel, A2.name)	/ Structuralize A2’s data
4	=connect("mysql")	/ Connecto to mysql database
5	>A4.update@i(A3, user)	/ Upadata A3’s data to user table in mysql database
6	>(A1.close(), A4.close())	/ Close database connection