"Sometimes we need to import data from a database table to the MongoDB database. Data needs to be .."

blackduckie RaqForum 28 No.
499 View • 3 Years ago

How to Import Data from Database to MongoDB

row-to-column transposition(4) array structure(1) database import(1) dynamic parse(1)

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.

SPL Official Website 👉 https://www.scudata.com

SPL Feedback and Help 👉 https://www.reddit.com/r/esProcSPL

SPL Learning Material 👉 https://c.scudata.com

SPL Source Code and Package 👉 https://github.com/SPLWare/esProc

Discord 👉 https://discord.gg/cFTcUNs7

Youtube 👉 https://www.youtube.com/@esProc_SPL

row-to-column transposition(4) array structure(1) database import(1) dynamic parse(1)

Application