잔재미코딩

데이터베이스 NoSQL 심화 (mongoDB) - project에 표현식 활용하기

7. project에 표현식 활용하기

* 산술표현식
 - $add       [a1 [, a2, a3 ... an]]
 - $substract [a1, a2]
 - $multiply  [a1 [, a2, a3 ... an]]
 - $divide    [a1, a2]
 - $mod       [a1, a2]

* 날짜 표현식
 - $year, $month, $week, $dayOfMonth, $dayOfWeek, $dayOfYear, $hour, $minute, $second

* 문자열 표현식
 - $substr
 - $concat

7.1. 산술표현식 예제 (add)

In [39]:
result = db.zip.aggregate([
    {'$group' : 
        {
            '_id' : '$state', 
            'max' : {'$max' : '$pop'}, 
            'min' : {'$min' : '$pop'} 
        } 
    },
    {'$project' : 
        {
            'maxmin' : {'$add' : ['$max', '$min']} 
        } 
    }
])
for record in result:
    print(record)

{'_id': 'CA', 'maxmin': 99568}
{'_id': 'MT', 'maxmin': 40128}
{'_id': 'MS', 'maxmin': 46968}
{'_id': 'FL', 'maxmin': 73194}
{'_id': 'AR', 'maxmin': 53532}
{'_id': 'GA', 'maxmin': 58646}
{'_id': 'WA', 'maxmin': 50517}
{'_id': 'SC', 'maxmin': 66990}
{'_id': 'MN', 'maxmin': 51421}
{'_id': 'NE', 'maxmin': 35330}
{'_id': 'MD', 'maxmin': 76003}
{'_id': 'TN', 'maxmin': 60510}
{'_id': 'DE', 'maxmin': 50681}
{'_id': 'DC', 'maxmin': 62935}
{'_id': 'AZ', 'maxmin': 57133}
{'_id': 'ME', 'maxmin': 40434}
{'_id': 'OR', 'maxmin': 48007}
{'_id': 'AL', 'maxmin': 44165}
{'_id': 'PA', 'maxmin': 80454}
{'_id': 'RI', 'maxmin': 53778}
{'_id': 'MA', 'maxmin': 65046}
{'_id': 'NJ', 'maxmin': 69663}
{'_id': 'NY', 'maxmin': 111396}
{'_id': 'ND', 'maxmin': 42207}
{'_id': 'VT', 'maxmin': 39127}
{'_id': 'NM', 'maxmin': 57502}
{'_id': 'CT', 'maxmin': 60695}
{'_id': 'KY', 'maxmin': 46563}
{'_id': 'OH', 'maxmin': 66712}
{'_id': 'WY', 'maxmin': 33113}
{'_id': 'UT', 'maxmin': 56008}
{'_id': 'HI', 'maxmin': 62915}
{'_id': 'MI', 'maxmin': 84712}
{'_id': 'AK', 'maxmin': 32383}
{'_id': 'WV', 'maxmin': 70185}
{'_id': 'IA', 'maxmin': 52117}
{'_id': 'WI', 'maxmin': 57189}
{'_id': 'VA', 'maxmin': 68525}
{'_id': 'IN', 'maxmin': 56618}
{'_id': 'SD', 'maxmin': 45336}
{'_id': 'IL', 'maxmin': 112047}
{'_id': 'MO', 'maxmin': 54994}
{'_id': 'KS', 'maxmin': 50178}
{'_id': 'LA', 'maxmin': 58905}
{'_id': 'NH', 'maxmin': 41465}
{'_id': 'OK', 'maxmin': 45550}
{'_id': 'NC', 'maxmin': 69179}
{'_id': 'TX', 'maxmin': 79463}
{'_id': 'CO', 'maxmin': 59418}
{'_id': 'ID', 'maxmin': 40912}
{'_id': 'NV', 'maxmin': 51533}

7.2. 날짜 표현식 예제 (year, month, day, hour, minutes, second, millisecond, dayOfYear, dayOfWeek, week)

In [43]:
#ISODate 라는 날짜 표현식으로 데이터 넣기
import datetime

db.test_db.insert_one(
    { 'item' : 'abc', 'price' : 10, 'quantity' : 2, 'date' : datetime.datetime.utcnow() }
)
Out[43]:
<pymongo.results.InsertOneResult at 0x10bf72908>
In [41]:
#ISODate 라는 날짜 표현식으로 데이터 넣기
specific_date = datetime.datetime.strptime("2017-09-13T10:53:53.000Z", "%Y-%m-%dT%H:%M:%S.000Z")
db.test_db.insert_one(
    { 'item' : 'abc', 'price' : 10, 'quantity' : 2, 'date' : specific_date }
)
Out[41]:
<pymongo.results.InsertOneResult at 0x10bea0bc8>
In [47]:
from pprint import pprint
In [48]:
# $project의 날짜 표현 operator 활용 예
result = db.test_db.aggregate([
    {'$project' : 
        {
            '_id' : 0,
            'year' : { '$year': '$date' },
            'month' : { '$month': '$date' },
            'day' : { '$dayOfMonth': "$date" },
            'hour' : { '$hour': "$date" },
            'minutes' : { '$minute': "$date" },
            'seconds' : { '$second': "$date" },
            'milliseconds' : { '$millisecond': "$date" },
            'dayOfYear' : { '$dayOfYear': "$date" },
            'dayOfWeek' : { '$dayOfWeek': "$date" },
            'week': { '$week': "$date" }
        }
    }
])
for record in result:
    pprint(record)

{'day': 20,
 'dayOfWeek': 2,
 'dayOfYear': 324,
 'hour': 11,
 'milliseconds': 971,
 'minutes': 12,
 'month': 11,
 'seconds': 44,
 'week': 47,
 'year': 2017}

7.3. 문자열 표현식 예제

In [49]:
# $project의 $concat operator 활용 예

db.test_db.insert_one(
    { 'firstname' : 'Dave', 'lastname' : 'Lee' }
)

result = db.test_db.aggregate([
    {'$project' : 
        {
            '_id' : 0,
            'all_data' : { '$concat' : ['Full Name: ', '$firstname', ' ', '$lastname' ]}
        }
    }
])
for record in result:
    pprint(record)

{'all_data': None}
{'all_data': 'Full Name: Dave Lee'}
In [50]:
# $project의 $substr operator 활용 예


db.test_db.insert_one(
    { 'fullname' : 'Dave Lee' }
)

result = db.test_db.aggregate([
    {'$project' : 
        {
            '_id' : 0,
            'first_name' : { '$substr' : ['$fullname', 0, 4 ]},
            'last_name' : { '$substr' : ['$fullname', 5, 7 ]}
        }
    }
])
for record in result:
    pprint(record)

{'first_name': '', 'last_name': ''}
{'first_name': '', 'last_name': ''}
{'first_name': 'Dave', 'last_name': 'Lee'}
In [52]:
from pprint import pprint

result = db.restaurant.find(limit=1)
for record in result:
    pprint(record)

{'_id': ObjectId('5a095da1e8393b01d3208bb4'),
 'address': {'building': '1007',
             'coord': [-73.856077, 40.848447],
             'street': 'Morris Park Ave',
             'zipcode': '10462'},
 'borough': 'Bronx',
 'cuisine': 'Bakery',
 'grades': [{'date': datetime.datetime(2014, 3, 3, 0, 0),
             'grade': 'A',
             'score': 2},
            {'date': datetime.datetime(2013, 9, 11, 0, 0),
             'grade': 'A',
             'score': 6},
            {'date': datetime.datetime(2013, 1, 24, 0, 0),
             'grade': 'A',
             'score': 10},
            {'date': datetime.datetime(2011, 11, 23, 0, 0),
             'grade': 'A',
             'score': 9},
            {'date': datetime.datetime(2011, 3, 10, 0, 0),
             'grade': 'B',
             'score': 14}],
 'name': 'Morris Park Bake Shop',
 'restaurant_id': '30075445'}
In [81]:
# 레스토랑 데이터 예제, grades의 각 원소를 개별적인 원소가 된 문서로 변경
result = db.restaurant.aggregate([
    {'$unwind' : '$grades'},
    {'$limit' : 5}
])
for record in result:
    pprint(record)

{'_id': ObjectId('5a095da1e8393b01d3208bb4'),
 'address': {'building': '1007',
             'coord': [-73.856077, 40.848447],
             'street': 'Morris Park Ave',
             'zipcode': '10462'},
 'borough': 'Bronx',
 'cuisine': 'Bakery',
 'grades': {'date': datetime.datetime(2014, 3, 3, 0, 0),
            'grade': 'A',
            'score': 2},
 'name': 'Morris Park Bake Shop',
 'restaurant_id': '30075445'}
{'_id': ObjectId('5a095da1e8393b01d3208bb4'),
 'address': {'building': '1007',
             'coord': [-73.856077, 40.848447],
             'street': 'Morris Park Ave',
             'zipcode': '10462'},
 'borough': 'Bronx',
 'cuisine': 'Bakery',
 'grades': {'date': datetime.datetime(2013, 9, 11, 0, 0),
            'grade': 'A',
            'score': 6},
 'name': 'Morris Park Bake Shop',
 'restaurant_id': '30075445'}
{'_id': ObjectId('5a095da1e8393b01d3208bb4'),
 'address': {'building': '1007',
             'coord': [-73.856077, 40.848447],
             'street': 'Morris Park Ave',
             'zipcode': '10462'},
 'borough': 'Bronx',
 'cuisine': 'Bakery',
 'grades': {'date': datetime.datetime(2013, 1, 24, 0, 0),
            'grade': 'A',
            'score': 10},
 'name': 'Morris Park Bake Shop',
 'restaurant_id': '30075445'}
{'_id': ObjectId('5a095da1e8393b01d3208bb4'),
 'address': {'building': '1007',
             'coord': [-73.856077, 40.848447],
             'street': 'Morris Park Ave',
             'zipcode': '10462'},
 'borough': 'Bronx',
 'cuisine': 'Bakery',
 'grades': {'date': datetime.datetime(2011, 11, 23, 0, 0),
            'grade': 'A',
            'score': 9},
 'name': 'Morris Park Bake Shop',
 'restaurant_id': '30075445'}
{'_id': ObjectId('5a095da1e8393b01d3208bb4'),
 'address': {'building': '1007',
             'coord': [-73.856077, 40.848447],
             'street': 'Morris Park Ave',
             'zipcode': '10462'},
 'borough': 'Bronx',
 'cuisine': 'Bakery',
 'grades': {'date': datetime.datetime(2011, 3, 10, 0, 0),
            'grade': 'B',
            'score': 14},
 'name': 'Morris Park Bake Shop',
 'restaurant_id': '30075445'}