[mongoDB] MongoDB Json Export / Import

Use_Silver·2022년 1월 18일

mongodb

mongoDB

목록 보기

4/6

MongoDB Json으로 export

1. export tools download

아래 사이트에서 mongoexport 다운로드 받기
https://docs.mongodb.com/database-tools/mongoexport/
Installation을 찾아 -> mongoexport를 누르고 -> 환경에 맞게 다운로드

2. json 파일로 export

host, collection, use database, out 지정해서 export

- mongoexport --host="localhost" --port=27017 --collection=scores --db=testDB --out=scores_01.json
2-1. host, port번호 빼고 export
mongoexport --collection=scores --db=testDB --out=scores_02.json

3. 조건을 줘서 export(--query)

mongoexport --collection=scores --db=testDB --query="{\"point\":{\"$gte\":80}}" --out=scores_04.json

MongoDB Json Import

1. Copy해서 데이터 가져오기

Query문을 실행했을 때 나오는 결과값을 copy해 insert

> var sArr = [{grade:'1',losal:'700',hisal:'1200'},{grade:'2',losal:'1201',hisal:'1400'},{grade:'3',losal:'1401',hisal:'2000'},{grade:'4',losal:'2001',hisal:'3000'},{grade:'5',losal:'3001',hisal:'9999'}]
> sArr[ {
                "grade" : "1",
                "losal" : "700",
                "hisal" : "1200"
        },
        {
                "grade" : "2",
                "losal" : "1201",
                "hisal" : "1400"
        },
        {
                "grade" : "3",
                "losal" : "1401",
                "hisal" : "2000"
        },
        {
                "grade" : "4",
                "losal" : "2001",
                "hisal" : "3000"
        },
        {
                "grade" : "5",
                "losal" : "3001",
                "hisal" : "9999"
        }
]
>
> db.salgrade.insertMany(sArr);
{
        "acknowledged" : true,
        "insertedIds" : [
                ObjectId("61e62e52e0896cce1efd0f02"),
                ObjectId("61e62e52e0896cce1efd0f03"),
                ObjectId("61e62e52e0896cce1efd0f04"),
                ObjectId("61e62e52e0896cce1efd0f05"),
                ObjectId("61e62e52e0896cce1efd0f06")
        ]
}
> db.salgrade.find()
{ "_id" : ObjectId("61e62e52e0896cce1efd0f02"), "grade" : "1", "losal" : "700", "hisal" : "1200" }
{ "_id" : ObjectId("61e62e52e0896cce1efd0f03"), "grade" : "2", "losal" : "1201", "hisal" : "1400" }
{ "_id" : ObjectId("61e62e52e0896cce1efd0f04"), "grade" : "3", "losal" : "1401", "hisal" : "2000" }
{ "_id" : ObjectId("61e62e52e0896cce1efd0f05"), "grade" : "4", "losal" : "2001", "hisal" : "3000" }
{ "_id" : ObjectId("61e62e52e0896cce1efd0f06"), "grade" : "5", "losal" : "3001", "hisal" : "9999" }
>

> var dArr = [{empno:'7499',ename:'ALLEN',job:'SALESMAN',mgr:'7698',hiredate:'1981-02-20',sal:'1600.00',comm:'300.00',deptno:'30'},{empno:'7521',ename:'WARD',job:'SALESMAN',mgr:'7698',hiredate:'1981-02-22',sal:'1250.00',comm:'500.00',deptno:'30'},{empno:'7654',ename:'MARTIN',job:'SALESMAN',mgr:'7698',hiredate:'1981-09-28',sal:'1250.00',comm:'1400.00',deptno:'30'},{empno:'7844',ename:'TURNER',job:'SALESMAN',mgr:'7698',hiredate:'1981-09-08',sal:'1500.00',comm:'0.00',deptno:'30'}]
>
>
>
>
> dArr
[
        {
                "empno" : "7499",
                "ename" : "ALLEN",
                "job" : "SALESMAN",
                "mgr" : "7698",
                "hiredate" : "1981-02-20",
                "sal" : "1600.00",
                "comm" : "300.00",
                "deptno" : "30"
        },
        {
                "empno" : "7521",
                "ename" : "WARD",
                "job" : "SALESMAN",
                "mgr" : "7698",
                "hiredate" : "1981-02-22",
                "sal" : "1250.00",
                "comm" : "500.00",
                "deptno" : "30"
        },
        {
                "empno" : "7654",
                "ename" : "MARTIN",
                "job" : "SALESMAN",
                "mgr" : "7698",
                "hiredate" : "1981-09-28",
                "sal" : "1250.00",
                "comm" : "1400.00",
                "deptno" : "30"
        },
        {
                "empno" : "7844",
                "ename" : "TURNER",
                "job" : "SALESMAN",
                "mgr" : "7698",
                "hiredate" : "1981-09-08",
                "sal" : "1500.00",
                "comm" : "0.00",
                "deptno" : "30"
        }
]
> db.emp.insertMany(dArr);
{
        "acknowledged" : true,
        "insertedIds" : [
                ObjectId("61e62f8ae0896cce1efd0f07"),
                ObjectId("61e62f8ae0896cce1efd0f08"),
                ObjectId("61e62f8ae0896cce1efd0f09"),
                ObjectId("61e62f8ae0896cce1efd0f0a")
        ]
}
> db.emp.find()
{ "_id" : ObjectId("61e62f8ae0896cce1efd0f07"), "empno" : "7499", "ename" : "ALLEN", "job" : "SALESMAN", "mgr" : "7698", "hiredate" : "1981-02-20", "sal" : "1600.00", "comm" : "300.00", "deptno" : "30" }
{ "_id" : ObjectId("61e62f8ae0896cce1efd0f08"), "empno" : "7521", "ename" : "WARD", "job" : "SALESMAN", "mgr" : "7698", "hiredate" : "1981-02-22", "sal" : "1250.00", "comm" : "500.00", "deptno" : "30" }
{ "_id" : ObjectId("61e62f8ae0896cce1efd0f09"), "empno" : "7654", "ename" : "MARTIN", "job" : "SALESMAN", "mgr" : "7698", "hiredate" : "1981-09-28", "sal" : "1250.00", "comm" : "1400.00", "deptno" : "30" }
{ "_id" : ObjectId("61e62f8ae0896cce1efd0f0a"), "empno" : "7844", "ename" : "TURNER", "job" : "SALESMAN", "mgr" : "7698", "hiredate" : "1981-09-08", "sal" : "1500.00", "comm" : "0.00", "deptno" : "30" }
>

> var cArr = [{deptno:'10',dname:'ACCOUNTING',loc:'NEW YORK'},{deptno:'20',dname:'RESEARCH',loc:'DALLAS'},{deptno:'30',dname:'SALES',loc:'CHICAGO'},{deptno:'40',dname:'OPERATIONS',loc:'BOSTON'},{deptno:'50',dname:'',loc:'50'}]
>
>
> cArr
[
        {
                "deptno" : "10",
                "dname" : "ACCOUNTING",
                "loc" : "NEW YORK"
        },
        {
                "deptno" : "20",
                "dname" : "RESEARCH",
                "loc" : "DALLAS"
        },
        {
                "deptno" : "30",
                "dname" : "SALES",
                "loc" : "CHICAGO"
        },
        {
                "deptno" : "40",
                "dname" : "OPERATIONS",
                "loc" : "BOSTON"
        },
        {
                "deptno" : "50",
                "dname" : "",
                "loc" : "50"
        }
]
> db.dept.insertMany(cArr);
{
        "acknowledged" : true,
        "insertedIds" : [
                ObjectId("61e6301ce0896cce1efd0f0b"),
                ObjectId("61e6301ce0896cce1efd0f0c"),
                ObjectId("61e6301ce0896cce1efd0f0d"),
                ObjectId("61e6301ce0896cce1efd0f0e"),
                ObjectId("61e6301ce0896cce1efd0f0f")
        ]
}
> db.dept.find()
{ "_id" : ObjectId("61dfe7f394bf732544229914"), "deptno" : 20, "dname" : "RESEARCH", "loc" : "DALLAS" }
{ "_id" : ObjectId("61dfe7f394bf732544229915"), "deptno" : 30, "dname" : "SALES", "loc" : "CHICAGO" }
{ "_id" : ObjectId("61dfe7f394bf732544229916"), "deptno" : 40, "dname" : "OPERATIONS", "loc" : "BOSTON" }
{ "_id" : ObjectId("61dfe7f394bf732544229917"), "deptno" : 50, "dname" : "IT", "loc" : "Seoul" }
{ "_id" : ObjectId("61e6301ce0896cce1efd0f0b"), "deptno" : "10", "dname" : "ACCOUNTING", "loc" : "NEW YORK" }
{ "_id" : ObjectId("61e6301ce0896cce1efd0f0c"), "deptno" : "20", "dname" : "RESEARCH", "loc" : "DALLAS" }
{ "_id" : ObjectId("61e6301ce0896cce1efd0f0d"), "deptno" : "30", "dname" : "SALES", "loc" : "CHICAGO" }
{ "_id" : ObjectId("61e6301ce0896cce1efd0f0e"), "deptno" : "40", "dname" : "OPERATIONS", "loc" : "BOSTON" }
{ "_id" : ObjectId("61e6301ce0896cce1efd0f0f"), "deptno" : "50", "dname" : "", "loc" : "50" }
>

2. json 파일로 export해서 import 하기

select * from table명 # 모든 테이블 데이터 조회
select 결과에 마우스 오른쪽 키 -> 데이터 추출
원하는 데이터 형태, 파일 경로 등 지정해서 저장
export 파일
mongo import

import
mongoimport -c=dept -d=empdb2 --file=dept_202201181217.json
생성 확인

null값
select 한 결과를 json으로 export -> import 했을 때, null값이 사라진다
이유는 json으로 export 할 때 select에서 null값이 사라지기 때문!

저장된 json 파일을 열어보면 아래와 같은 형식으로 저장되어 있을것이다.

이런식으로 바꿔주면 데이터가 원래 상태로 들어간다.

Aggregate pipeline

https://docs.mongodb.com/manual/reference/operator/aggregation-pipeline/
dept collection에서 전체 다 가져와라
pipeline들이 array화해서 들어감

db.aggrgate([
    	$project : [select] 내가 원하는 컬럼만 가져오겠다. 
        $limit 	 : [n개 top(n)]
        $match   : [where]
        $group 	 : [group by]
        $sort 	 : [order by]
        $lookup  : Performs a left outer join
        $out 	 : collection 생성 
        ])

project : 원하는 컬럼만 선택

> db.dept.aggregate([ { '$project': {'dname':1 } }]);

{ "_id" : ObjectId("61e6354bac1bdb3f3eff3476"), "dname" : "SALES" }
{ "_id" : ObjectId("61e6354bac1bdb3f3eff3477"), "dname" : "RESEARCH" }
{ "_id" : ObjectId("61e6354bac1bdb3f3eff3478"), "dname" : "OPERATIONS" }
{ "_id" : ObjectId("61e6354bac1bdb3f3eff3479"), "dname" : "IT" }
{ "_id" : ObjectId("61e6354bac1bdb3f3eff347a"), "dname" : "ACCOUNTING" }
>

> db.dept.aggregate([ { '$project':{'deptno':1,'dname':1,'_id':0 } } ] );
{ "deptno" : 30, "dname" : "SALES" }
{ "deptno" : 20, "dname" : "RESEARCH" }
{ "deptno" : 40, "dname" : "OPERATIONS" }
{ "deptno" : 50, "dname" : "IT" }
{ "deptno" : 10, "dname" : "ACCOUNTING" }
>

limit : 원하는 개수만 확인

> db.dept.aggregate([ { '$project':{'dname':1,'_id':0 } },{'$limit':3} ] );
{ "dname" : "SALES" }
{ "dname" : "RESEARCH" }
{ "dname" : "OPERATIONS" }
>

> db.dept.aggregate([ { '$project':{'deptno':1,'dname':1,'_id':0 }},{'$limit':3}]);
{ "deptno" : 30, "dname" : "SALES" }
{ "deptno" : 20, "dname" : "RESEARCH" }
{ "deptno" : 40, "dname" : "OPERATIONS" }
>

count : grouping이 필요

> db.dept.aggregate([ { '$group' : {'_id':0, 'count':{$sum:1} }}] );
{ "_id" : 0, "count" : 5 }

> db.emp.aggregate([ { '$group' : {'_id':0, 'count':{$sum:1}  } } ] );
{ "_id" : 0, "count" : 14 }
>

count별 직원이 몇 명인지 구함

> db.emp.aggregate([ { '$group' : {'_id':'$empno', 'count':{$sum:1}  } } ] );
{ "_id" : 7566, "count" : 1 }
{ "_id" : 7839, "count" : 1 }
{ "_id" : 7782, "count" : 1 }
{ "_id" : 7844, "count" : 1 }
{ "_id" : 7788, "count" : 1 }
{ "_id" : 7902, "count" : 1 }
{ "_id" : 7900, "count" : 1 }
{ "_id" : 7654, "count" : 1 }
{ "_id" : 7876, "count" : 1 }
{ "_id" : 7698, "count" : 1 }
{ "_id" : 7934, "count" : 1 }
{ "_id" : 7521, "count" : 1 }
{ "_id" : 7499, "count" : 1 }
{ "_id" : 7369, "count" : 1 }
>

Q1. job별 인원수

> db.emp.aggregate([ { '$group' : {'_id':'$job', 'count':{$sum:1}  } } ] );
{ "_id" : "PRESIDENT", "count" : 1 }
{ "_id" : "SALESMAN", "count" : 4 }
{ "_id" : "MANAGER", "count" : 3 }
{ "_id" : "CLERK", "count" : 4 }
{ "_id" : "ANALYST", "count" : 2 }

Q2. deptno별 급여합계

> db.emp.aggregate([ { '$group' : {'_id':'$deptno', 'count':{'$sum':"$sal"}  } } ] );
{ "_id" : 20, "count" : 10875 }
{ "_id" : 30, "count" : 9400 }
{ "_id" : 10, "count" : 8750 }
>

Q3. job별 급여 평균

> db.emp.aggregate([ { '$group' : {'_id':'$job', 'count':{'$avg':"$sal"}  } } ] );
{ "_id" : "SALESMAN", "count" : 1400 }
{ "_id" : "ANALYST", "count" : 3000 }
{ "_id" : "CLERK", "count" : 1037.5 }
{ "_id" : "MANAGER", "count" : 2758.3333333333335 }
{ "_id" : "PRESIDENT", "count" : 5000 }
>