"Sometimes we need to convert the non-structured MongoDB data into structured data and export it .."

blackduckie RaqForum 28 No.
417 View • 3 Years ago

How to Export MongoDB Data as a CSV File

data rearrangement(1) nested structure(4)

Sometimes we need to convert the non-structured MongoDB data into structured data and export it as a CSV file for being migrated to other applications, such as the database.
Below is collection score containing information of student scores. The collection has nested subdocument subject to record scores tables of all subjects. Now we are trying to export the data to a CSV file.

name	age	province	subject
zhou gao gao	24	guang xi	{"name": "Chinese","score": 80}, {"name": "Math","score": 84}, {"name": "English","score": 84}, {"name": "Chemic","score": 98}
li chao	16	shan dong	{"name": "Chinese","score": 94}, {"name": "Math","score": 88}, {"name": "English","score": 75}, {"name": "Chemic","score": 73}
yang hao hao	26	guang xi	{"name": "Chinese,"score": 85}, {"name": "Math,"score": 83}, {"name": "English ,"score": 64}, {"name": "Chemic,"score": 71}
…	…	…

We can use esProc SPL to first structuralize the data and then export it to a CSV file.

Directions:
1. Write SPL script scores.dfx:

	A	B
1	=mongo_open("mongodb://localhost:27017/student")	/ Connect to MongoDB database
2	=mongo_shell(A1,"score2.find(,{_id:0})").fetch()	/ Get data from score collection
3	=A2.new(name, age, province, (r=subject.(score))(1): Chinese,r(2): English,r(3): Math, r(4): Chemic)	/ Structuralize data by converting scores of each subject into a new column
4	>file("D:\\tmp\\exp_data.csv").export@tc(A3)	/ Export the data as a CSV file
5	=A2.news(subject; name:subject, score, A2.name, age, province)	/ Structuralize data by corresponding each subject to its scores
6	>file("D:\\tmp\\exp_data2.csv").export@tc(A5)	/ Export the data as a CSV file
7	>A1.close()	/ Close database connection

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

A3	name	age	province	Chinese	English	Math	Chemic
	zhou gao gao	24	guang xi	80	84	24	8
	li chao	16	shan dong	94	24	50	32
	yang hao hao	26	guang xi	44	11	26	80
	…	…	…