MongoDB 조회쿼리 정리

Published: January 11, 2021 by

• Categories:
• mongodb 3

• Tags:
• mongodb 3

• 범위조회

SELECT 
  AColumn,BColumn 
FROM collection 
WHERE last_update_datetime BETWEEN '2020-01-01T00:00:00.000Z' AND '2020-12-30T00:00:00.000Z'

db.collection.find({$and:
  [
    {last_update_datetime:{$gte:ISODate("2020-01-01T00:00:00.000Z")}},
     {last_update_datetime:{$lte:ISODate("2020-12-30T00:00:00.000Z")}}
   ]
 },{AColumn:1, BColumn:1})

• Mongo aggregate Multiple Group by

SELECT 
  collDate, targetType, PC_COUNT, MOBILE_COUNT, UNKNOWN_COUNT, TOTAL_COUNT
FROM collection 
WHERE collDate = '20191119'
limit 3
GROUP BY collDate,targetType

db.collection.aggregate([
    {
        '$match' : {'collDate' : {'$eq' : '20191119'}}
    },
    {
        '$limit' : 3
    },
    {
        '$group' : {
            '_id' : {
                'collDate' : '$collDate',
                'targetType' : '$targetType'
            },
            'PC_COUNT' : {'$sum':'$PC'},
            'MOBILE_COUNT' : {'$sum':'$MOBILE'},
            'TOTAL_COUNT' : {'$sum':'$TOTAL'}
        }

    }
])

• Mongo field 목록조회
  데이터가 많을 경우 limit를 걸어서 조회

var allKeys = {}; 
db.collection.find().limit(30000).forEach(
	function(doc){
		Object.keys(doc).forEach(
			function(key){
				allKeys[key]=1
			}
		)
	}
); 
allKeys;

• Query on Embedded/Nested Documents

db.collection.find(
    {
        'mABTest.data.testType' : 'batk_2101'
    }
).count()

• Mongo aggregate group함수

db.collection.aggregate([
    {
        '$match' : {
            'collDate' : {'$in' : ['20210112','20210113']},
            'targetType' : 'iTc',
            'targetData' : '00f09880428ab68692bf9f6b16b207f4',
            'PC_NEW' : {'$exists' : true}
        }
    }
    ,{
        '$group' : {
            '_id' : {}
            ,'PC_NEW_SUM' : {'$sum' : '$PC_NEW'}
            ,'PC_NEW_MAX' : {'$max' : '$PC_NEW'}
            ,'PC_NEW_MIN' : {'$min' : '$PC_NEW'}
            ,'PC_NEW_AVG' : {'$avg' : '$PC_NEW'}
            ,'PC_NEW_FIRST' : {'$first' : '$PC_NEW'}
            ,'PC_NEW_LAST' : {'$last' : '$PC_NEW'}
            ,'PC_NEW_COUNT' : {'$sum' : 1}
            ,'PC_NEW_addToSet' : {'$addToSet' : '$PC_NEW'}
            ,'PC_NEW_push' : {'$push' : '$PC_NEW'}
        }
    }
])

너무 두서 없이 정리를 한 것 같아 시간날 때 다시 두서 있게 정리를 해야할 것 같다.