A surpassingly strange question is this: "How do I get the data out of
MongoDB into a spreadsheet?"
The variation is "How can we load the MongoDB data into a relational
database?"
I'm always perplexed by this question. It has a subtext that I find
baffling. The subtext is this "all databases are relational, right?"
In order to ask the question, one has to be laboring under the
assumption that the only difference between MongoDB and a relational
database is the clever sticker on your laptop. Mongo folks have a
little green Mango leaf. Postgres has a blue/gray elephant.
This assumption is remarkably hard to overcome.
THEM: "How can we move this mongo data into a spreadsheet?"
ME: "What?"
THEM: "You know. Get a bulk CSV extract."
ME: "Of complex, nested documents?"
THEM: "Nested documents?"
ME: "Mongo database documents include arrays and -- well --
subdocuments. They're not in first normal form. They don't fit the
spreadsheet data model."
THEM: "Whatever. Every database has a bulk unload into CSV. How do you
do that in Mongo?"
ME: "You can't represent a mongo document in rows and columns."
THEM: (Thumping desk for emphasis.) "Relational Theory is explicit.
ALL DATA CAN BE REDUCED TO ROWS AND COLUMNS!"
ME: "Right. Through a process of normalization. The Mongo data you're
looking at isn't normalized. You'd have to normalize it into a
relational table model. Then you could write a customized extract
focused on that relational model."
THEM: "That's absurd."
At this point, all we can do is give them the minimal pymongo
MongoClient code block. Hands-on queries seem to be the only way to
make progress.
from pymongo import MongoClient
from pprint import pprint
with MongoClient("mongodb://somehost:27017") as mongo:
collection = mongo.database.collection
for document in collection.find():
pprint(document)
Explanations seem to wind up in a weird circular pattern where they
keep repeating their relational assumptions. Not much seems to work:
diagrams, hand-waving, links to tutorials are all implicitly rejected
because they don't confirm SQL bias.
A few days later they call asking how they are supposed to work with a
document that has complex nested fields inside it.
This could be the beginning of wisdom. Or it could be the beginning of
a lengthy reiteration of SQL Hegemony talking points and desk
thumping.
THEM: "The document has an array of values."
ME: "Correct."
THEM: "What's that mean?"
ME: "It means there are multiple occurrences of the child object
within each parent object."
THEM: "I can see that. What does it mean?"
ME: (Rising inflection.) "The parent is associated with multiple
instances of the child."
THEM: "Don't patronize me! Stop using mongo mumbo-jumbo. Just a simple
explanation is all I want."
ME: "One Parent. Many Children."
THEM: "That's stupid. One-to-many absolutely requires a foreign key.
The children don't even have keys. Mongo must have hidden keys
somewhere. How can I see the keys on the children in this so-called
'array' structure? How can expose the underlying implementation?"
The best I can do is show them an approach to normalizing some of the
data in their collection.
from pymongo import MongoClient
from pprint import pprint
with MongoClient("mongodb://your_host:27017") as mongo:
collection = mongo.your_database.your_collection
for document in collection.find():
for child in parent['child_array']:
print( document['parent_field'], child['child_field'] )
This leads to endless confusion when some documents lack a particular
field. The Python document.get('field') is an elegant way to handle
optional fields. I like to warn them that they should not rely on
this. Sometimes document['field'] is appropriate because the field
really is mandatory. If it's missing, there are serious problems. Of
course, the simple get() method doesn't work for optional nested
documents. For this, we need document.get('field', {}). And for
optional arrays, we can use document.get('field', []).
Interestingly we sometimes have confusion over {} for document and []
for array. I chalk that up to folks who are too used to very wordy SQL
and Java. I save the questions for my next book on Python.
At some point, the "optional" items may be more significant than this.
Perhaps an if statement is required to handle business rules that
are reflected as different document structures in a single collection.
This leads to yet more desk-thumping. It's accompanied with the
laughable claim that a "real" database doesn't rely on if
statements to distinguish variant subentities that are persisted in a
single table. The presence of SQL ifnull() functions, case
expressions, and application code with if statements apparently
doesn't exist. Or -- when it is pointed out -- isn't the same thing as
writing an if statement to handle variant document subentities in
a Mongo database.
It appears to take about two weeks to successfully challenge
entrenched relational assumptions. Even then, we have to go over some
of the basics of optional fields and arrays more than once.