MongoDB 조회쿼리 정리2
업무에서 간단한 검색어 검색이나 카운트 조회를 할 때는 find를 사용하지만 쿼리 중 grouping, filtering이 들어가게 되면 find로는 처리를 할 수가 없다. 그래서 오늘은 평소에 많이 사용하고 있는 Aggregation Framework Pipeline의 예제들과 정리를 해보려고 한다.
Aggregation
- MongoDB의 Aggregation은 Sharding 기반의 데이터를 효율적으로 처리하고 집계하는 프레임워크이다.
- documents를 grouping, filtering 등 다양한 연산을 적용하여 계산된 결과를 반환한다.
1. $match
SQL의 WHERE 절에 해당한다. 원하는 데이터를 추출하기 위한 Filtering 역할을 한다.
1-1. $exists
SQL의 IS NOT NULL 조회에 해당한다.
db.getCollection('COLLECTION').aggregate([
{
'$match' : {
'iUm' : {'$exists' : true}
}
}
])
1-2. $eq
SQL의 Equal(=) 조회에 해당한다.
db.getCollection('COLLECTION').aggregate([
{
'$match' : {
_id : {'$eq' : '540a4c8705273b05-306f3a1d163f2ba6641-7f3f'}
}
}
])
1-3. $ne
SQL의 Not Equal(!=) 조회에 해당한다.
db.getCollection('COLLECTION').aggregate([
{
'$match' : {
_id : {'$ne' : '540a4c8705273b05-306f3a1d163f2ba6641-7f3f'}
}
}
])
1-4. $in
SQL의 IN절 조회에 해당한다.
db.getCollection('COLLECTION').aggregate([
{
'$match' : {
_id : {'$in' : ['540a4c8705273b05-306f3a1d163f2ba6641-7f3f','b776f5c16d3eaa8266a6f4f31737e25768b-3219']}
}
}
])
1-5. $nin
SQL의 NOT IN절 조회에 해당한다.
db.getCollection('COLLECTION').aggregate([
{
'$match' : {
_id : {'$nin' : ['540a4c8705273b05-306f3a1d163f2ba6641-7f3f','b776f5c16d3eaa8266a6f4f31737e25768b-3219']}
}
}
])
1-6. $limit
SQL의 LIMIT와 동일하다.
db.getCollection('COLLECTION').aggregate([
{
'$match' : {
_id : {'$nin' : ['540a4c8705273b05-306f3a1d163f2ba6641-7f3f','b776f5c16d3eaa8266a6f4f31737e25768b-3219']}
}
},
{
'$limit' : 5
}
])
1-7. $and
$and 조건은 Array type이고 한 개 이상의 JSON 형태의 조건을 입력해야 한다.
db.getCollection('COLLECTION').aggregate([
{
'$match' : {
'$and' : [
{_id : {'$nin' : ['540a4c8705273b05-306f3a1d163f2ba6641-7f3f','b776f5c16d3eaa8266a6f4f31737e25768b-3219']}}
,{'key' : '000108f3c0db54c9-290fb41a165787def261e9d'}
]
}
}
])
// between A and B - 검색 조건 필드의 type이 date인 경우
db.getCollection('COLLECTION').aggregate([
{
'$match' : {
'$and' : [
{'last_update_datetime' : {'$gt' : ISODate('2021-09-01T00:00:00Z')}}
,{'last_update_datetime' : {'$lt' : ISODate('2021-09-06T00:00:00Z')}}
]
}
}
])
// between A and B - 검색 조건 필드의 type이 String인 경우
db.getCollection('COLLECTION').aggregate([
{
'$match' : {
'$and' : [
{'iUm.data.insDate' : {'$gt' : '20210901'}}
,{'iUm.data.insDate' : {'$lt' : '20210906'}}
]
}
}
])
1-8. $regex
SQL의 LIKE 검색에 해당한다.
db.getCollection('COLLECTION').aggregate([
{
$match : {
// '_id' : {$regex : /6641/} // like %6641%
// '_id' : {$regex : /^6641/} // like 6641%
'_id' : {$regex : /6641$/} // like %6641
}
}
])
2. $Project
Collection 필드 중 원하는 필드를 조회할 때 사용한다.
2-1. 전체 Collection 필드 중 _id, iUm 필드만 조회
_id 필드는 명시적으로 false를 선언하지 않으면 출력된다.
db.getCollection('COLLECTION').aggregate([
{
'$project' : {
'_id' : false
,'id' : '$_id'
,'um' : '$iUm'
}
}
])
2-2. $indexOfArray(A, B)를 이용해 A Array중 B값이 몇 번째 인덱스에 해당하는 지 조회
db.getCollection('COLLECTION').aggregate([
{
$project : {
'ageIndex' : {
$indexOfArray : ['$iAge2.data', {$max: '$iAge2.data'}]
}
}
}
])
2-3. $strLenCP를 이용해 _id 필드의 길이 조회
db.getCollection('COLLECTION').aggregate([
{
$project : {
'id_length' : { $strLenCP : '$_id' }
}
}
])
2-4. $substr($substrCP)을 이용한 데이터 조작
db.getCollection('COLLECTION').aggregate([
{
$project : {
'id_substr' : { $substrCP: [ '$_id', 0, 8 ] }
}
}
])
2-5. $cond, if~then 구문을 이용해 SQL의 case when 구현
db.getCollection('COLLECTION').aggregate([
{
$project:{
'PC':{$cond:{'if':{$eq:['$saveCnt.device', 'P']}, 'then':1, 'else':0}},
'MOBILE':{$cond:{'if':{$eq:['$saveCnt.device', 'M']}, 'then':1, 'else':0}},
'UNKNOWN':{$cond:{'if':{$eq:['$saveCnt.device', 'UNKNOWN']}, 'then':1, 'else':0}}
}
}
])
2-6. $setUnion으로 iUm, iHu에서 domain필드만 추출해서 Array로 만들기.
db.getCollection('COLLECTION').aggregate([
{
$match: {
$and : [
{'iUm' : {'$exists' : true}}
,{'iHu' : {'$exists' : true}}
]
}
}
,{
$project:{
'domains' : {
'$setUnion' : ['$iUm.data.domain', '$iHu.data.domain']
}
}
}
])
2-7. $setUnion으로 조회한 Array의 사이즈 조회
db.getCollection('COLLECTION').aggregate([
{
$match: {
$and : [
{'iUm' : {'$exists' : true}}
,{'iHu' : {'$exists' : true}}
]
}
}
,{
$project:{
'CNT' : {
'$size' : {
'$setUnion' : ['$iUm.data.domain', '$iHu.data.domain']
}
}
}
}
])
3. $group
3-1. Collection 전체 카운트
db.getCollection('COLLECTION').aggregate([
{
'$group' : {
'_id' : null
, 'COUNT' : {$sum:1}
}
}
])
4. $sort
sort는 DB나 MongoDB 모두 부하가 많이 가기 때문에 10건으로 제한을 해서 테스트를 진행. sort를 위한 필드의 값이 -1이면 desc, 1이면 asc 정렬이다.
db.getCollection('COLLECTION').aggregate([
{
'$limit' : 10
}
,{ $sort : { 'last_update_datetime' : -1 } }
])
5. $unwind
현재 Document와 iCw.data Array 목록이 카르테시안 곱이된다. iCw.data Array의 사이즈만큼 Row가 생긴다.
5-1. unwind before after 데이터 비교
unwind 전
db.getCollection('COLLECTION').aggregate([
{
'$limit' : 1
}
])
unwind 후
db.getCollection('COLLECTION').aggregate([
{
'$limit' : 1
}
,{
'$unwind' : '$iCw.data'
}
])
unwind는 aggregation의 $match 되는 조건만 조회해서 다시 grouping 결과가 필요할 때 사용한다. $match 조건만으로 조회를 하면 Document 내의 전체 데이터가 모두 조회가 되기 때문에 $unwind로 개별ROW로 변경 후 Filtering 후 Grouping 해서 원하는 DOcument 데이터만을 조회할 수 있다.
5-2. $unwind 활용예시
iCw.data를 unwind하고 $project 파이프라인을 통해서 iCw.data만 조회 후 $match Filtering을 통해 pCode가 0020보다 크고 0030보다 작은 Document만을 추출한 예제이다.
db.getCollection('COLLECTION').aggregate([
{
'$limit' : 1
}
,{
'$unwind' : '$iCw.data'
}
,{
'$project' : {
'_id' : false
,'DATA' : '$iCw.data'
}
}
,{
'$match' : {
'$and' : [
{'DATA.pCode' : {'$gt' : '0020'}}
,{'DATA.pCode' : {'$lt' : '0030'}}
]
}
}
])
6. 불필요한 컬럼 삭제하는 방법
AAA 몽고DB 데이터는 아래와 같은 형태이다.
"AAA" : {
"syncTime" : ISODate("2021-10-01T05:06:59.422Z"),
"data" : [
{
"siteKey" : "1ed30cba0e177c9af84cb6c504cd4480",
"val1" : "008000000389",
"val2" : "008000000381",
"val3" : "008000000382",
}
]
}
6-1. data 하위의 Array 형태의 데이터 구조일 경우 컬럼삭제 테스트
메세지는 정상적으로 출력되지만 데이터의 변화는 없다.
db.getCollection('collectionName').update(
{'AAA.data.val1' : {'$exists':true}},
{'$unset' : {'AAA.data.val1' : true}},
false,
true
)
// 결과
Updated 1349 existing record(s) in 25ms
6-1. data 컬럼삭제 테스트
Array 개별데이터의 컬럼삭제는 안되는 것을 위에서 확인 했고 data를 삭제하면 정상적으로 컬럼이 삭제된다.
db.getCollection('collectionName').update(
{'AAA.data' : {'$exists':true}},
{'$unset' : {'AAA.data' : true}},
false,
true
)
// 결과
Updated 1353 existing record(s) in 98ms
7. MongoDB chunk 관리하기
7-1. balancer scheduling
mongos> use config;
switched to db config
mongos> db.settings.update(
... {_id:"balancer"},
... {$set: {activeWindow: {start : "03:00", stop: "07:00"}}},
... {upsert : true})
WriteResult({ "nMatched" : 0, "nUpserted" : 1, "nModified" : 0, "_id" : "balancer" })
mongos>sh.status()
balancer:
Currently enabled: yes
Currently running: no
Balancer active window is set between 03:00 and 07:00 server local time
참고
mongoDB 공식 - Documentation - aggregation
mongoDB 공식 - Documentation - shard balance
MongoDB chunk 관리하기 - 2