How to Import Data from Database to MongoDB

Sometimes we need to import data from a database table to the MongoDB database. Data needs to be imported and stored in the MongoDB nested structure of subdocuments relationship. MongoDB has two basic types of nested structure. They are Map object and Array object. All multilevel nested structures are generated based on the two basic structures.
Let’s take MySQL as an example to illustrate the issue. scores table stores student scores. We are trying to convert SUBJECT field and SCORE field, which contain each student’s scores, into the structure of subdocument relationship. Below is the source table:

CLASS

STUDENTID

SUBJECT

SCORE

Class   one

1

English

84

Class   one

1

Math

77

Class   one

1

PE

69

Class   one

2

English

81

Class   one

2

Math

80

To convert it into the Map structure:

{
       "CLASS" : "Class one",
        "STUDENTID" : 1,
        "SUBJECT" : {
                "English" : 84,
              "Math"   : 77,
                "PE" : 69
            }
  },
    ...

And to covert it into the Array structure:

{
        "CLASS" : "Class one",
        "STUDENTID" : 1,
        "SUBJECT" : [
          {  English" : 84 },
          {  "Math" :   77 },
          {  "PE" : 69 }
      ]
  },
    ...

It is convenient to use esProc SPL to convert the table data into a nested structure and import it into MongoDB.

1.     Write SPL script scores.dfx in esProc:

A

B

1

=connect("mysql")

/ Connect to mysql database

2

=A1.query@x("select * from   scores")

/ Get data from the scores table

3

=A2.pivot(CLASS, STUDENTID; SUBJECT, SCORE)

/ Convert rows under SUBJECT into new columns

4

=A3.new(CLASS, STUDENTID, create(English, Math, PE).    record(~.array().to(3,))(1):SUBJECT )

/ Convert the subjects into a Map structure

5

=A2.group(CLASS, STUDENTID; ~.eval("create("
  +SUBJECT+")").insert(0, SCORE)(1) ):SUBJECT)

/ Convert scores into the Array structure

6

=mongo_open("mongodb://localhost:27017/raqdb")

/ Connect to MongoDB database

7

>mongo_insert(A6, "courseMap", json(A4))

/ Import courseMap set into MongoDB

8

>mongo_insert(A6, "courseArray", json(A5))

/ Import courseArray set into MongoDB

9

>A5.close()

/ Close database connection

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

A3

CLASS  

STUDENTID

English

Math

PE

Class one

1

84

77

69

Class one

2

81

80

97

Class one

3

75

86

67

 Value of cell A4:

A4

CLASS  

STUDENTID

SUBJECT

Class one

1

[84, 77, 69]

Class one

2

[81, 80, 97]

Class one

3

[75, 86, 67]

 Value of cell A5:

A5

CLASS  

STUDENTID

SUBJECT

Class one

1

[[84], [77], [69]]

Class one

2

[[81], [80], [97]]

Class one

3

[[75], [86], [67]]

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

courseMap

{
        "_id" :   ObjectId("5feda0899f919c0b2c097b86"),
        "CLASS" : "Class one",
        "STUDENTID" : 1,
        "SUBJECT" : {
                  "English" : 84,
              "Math"   : 77,
                  "PE" : 69
          }
  }
  ......

courseArray

{
      "_id" :     ObjectId("5feda0899f919c0b2c097ba2"),
      "CLASS" : "Class   one",
      "STUDENTID" : 1,
      "SUBJECT" : [
          {
                "English" : 84
          },
          {
              "Math"   : 77
          },
          {
                "PE" : 69
          }
      ]
  }
    ......

A5: SUBJECT field stores records rather than table sequences, so the first record in the generated table sequence needs to be retrieved. Otherwise, instead of the desired structure, a structure of nested Map within Array will be generated in MongoDB.
If the database table is of the nested structure, just convert it into the JSON format and import the JSON data into MongoDB.

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