DB Lab6 solutions
1. count number of ..
a) - number of works in 2015:
papers> db.mag.find({year: 2015}).size()
26750
b) number of papers by Elsevier or Springer
papers> db.mag.find({publisher: {$in: ['Elsevier', 'Springer']}}).size()
5036
c) papers between 2009 and 2019 inclusive:
papers> db.mag.find({ $and: [{ year: { $gt: 2008 } }, { year: { $lt: 2020 } }] }). size()
402299
QUESTION why doesn’t comma-separation work? Why do even impossible conditions returns docs? What is happening here?..
papers> db.mag.find({ year: { $gt: 2008 }, year: { $lt: 2020 } }). size()
999991
papers> db.mag.find({ year: { $gt: 2008 }, year: { $lt: 2006 } }). size()
473083
d) Papers with a DOI
papers> db.mag.find({ doi: { $exists: true } }). size()
93469
e) Nested queries: Query on Embedded/Nested Documents — MongoDB Manual Nested queries on arrays: Query an Array of Embedded Documents — MongoDB Manual
All papers by Einstein:
papers> db.mag.find({ 'authors.name': 'Albert Einstein' })
as first author:
papers> db.mag.find({ 'authors.0.name': 'Albert Einstein'}).size()
4
Sorted by TITLE in descending order:
papers> db.mag.find({ 'authors.0.name': 'Albert Einstein'},{'title':1,_id:0}).sort({"title":-1})
[
{ title: 'Über die gegenwärtige Krise der theoretischen Physik' },
{ title: 'The Theory of Special Relativity' },
{ title: 'Segundo centenario de Isaac Newton' },
{ title: 'Correspondencia con Michele Besso: (1903-1955)' }
]
f) Papers with first author name like mine:
papers> db.mag.find({ 'authors.0.name': {$regex: '^Serhii'}}, {_id:0, 'authors':1, 'year':2})
[
{
authors: [ { name: 'Serhii Dembitskyi', id: '2740441064' } ],
year: 2010
},
{
authors: [ { name: 'Serhii Plokhy', id: '2611291034' } ],
year: 2012
},
{
authors: [ { name: 'Serhii Plokhy', id: '337413867' } ],
year: 2012
},
{
authors: [ { name: 'Serhii Plokhy', id: '2641626031' } ],
year: 2006
}
]
g) where (all?) author names have two “a"s Is this papers where at least one author matches, or papers where ALL authors match?
papers> db.mag.find({ 'authors.name': { $regex: '^[^a]*a[^a]*a[^a]*$', $options: 'i' } })
- QUESTION: how do I do “all elements of an array have to match condition?”
- How to match documents where all array elements match predicate. - http://asya999.github.io/ sugggest to use interesting discrete math logic - “no elements that match…”
- Answer: no easy answer
I’m not sure I’m getting the question right, giving up for now EDIT: I wasn’t, it’s about “aa”/“Aa”/“AA” and the regex should be much easier
h) Papers containing ‘analysis’ in the title:
papers> db.mag.find({ 'title': { $regex: '.*analysis.*', $options: 'i' } }). size()
8878
2. Aggregation
“The little MongoDB book” is awesome. Chapter 6 (p.50) is about aggregating data. https://www.openmymind.net/mongodb.pdf a) Number of published papers per year, desc. by paper count:
papers> db.mag.aggregate([{ $group: { _id: '$year', total: { $sum: 1 } } }]).sort({'total':-1})
[
{ _id: 2012, total: 72292 },
{ _id: 2013, total: 68714 },
{ _id: 2011, total: 66872 },
{ _id: 2010, total: 58041 },
{ _id: 2014, total: 54801 },
{ _id: 2009, total: 50953 },
{ _id: 2008, total: 44261 },
{ _id: 2007, total: 41071 },
{ _id: 2006, total: 39277 },
{ _id: 2005, total: 37169 },
{ _id: 2004, total: 35361 },
{ _id: 2003, total: 33501 },
{ _id: 2002, total: 30158 },
{ _id: 2015, total: 26750 },
{ _id: 2001, total: 25196 },
{ _id: 2000, total: 23602 },
{ _id: 1999, total: 21775 },
{ _id: 1998, total: 20133 },
{ _id: 1997, total: 18259 },
{ _id: 1996, total: 17434 }
]
b) Docs by type
papers> db.mag.aggregate([{ $group: { _id: '$doc_type', count: { $sum: 1 } } }])
[
{ _id: 'Patent', count: 160618 },
{ _id: 'Conference', count: 4657 },
{ _id: 'BookChapter', count: 206 },
{ _id: '', count: 733783 },
{ _id: 'Book', count: 964 },
{ _id: 'Journal', count: 99772 }
]
c) Average citation count per document type:
papers> db.mag.aggregate([{ $group: { _id: '$doc_type', avg_citations: { $avg: '$n_citation' }} }]). sort({'avg_citations': -1})
{ _id: 'Book', avg_citations: 33.3558091286307 },
{ _id: 'Patent', avg_citations: 9.905545490203403 },
{ _id: 'Conference', avg_citations: 5.705389735881469 },
{ _id: 'Journal', avg_citations: 5.345320784596726 },
{ _id: 'BookChapter', avg_citations: 0.8786407766990292 },
{ _id: '', avg_citations: 0.518265792285384 }