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

  3. Start debugging and execute the code. Below is the value of cell A3:

A3

friend

tel

name

tom

186123500500

jim

jack

181123500511

jim

luke

138123500522

jim

4. After the script is executed, we can view the final result set in mysql database as follows:

undefined

Read How to Call an SPL Script in Java to learn about the integration of an SPL script with a Java program.