MongoDB Is Abusing JSON
smsohan.com
smsohan.com
r.table('orders')
.pluck('cust_id','ord_date','price')
.groupBy('cust_id','ord_date', r.sum('price')).
.filter(r.row('reduction').gt(250))
Its not an abuse of JSON to use it as a way of representing queries, but its probably a shame that Mongo haven't provided a better way of generating queries.
After seeing the screencast of RethinkDB it seems to me that it's just like Mongo but a much better API and easier handling of sharding and replication. The only con I see of using RethinkDB is that it's very new (so is Mongo...) and probably is little too early for production. Basically I hope not to find worse surprises down the road than I hear of Mongo :P
Under the same effect I'm not a fan of mongo style query({}).limit(count).skip(num) format and usually I move the limit and skip into the initial query.
It does a lot of things better than SQL, too. Consider, for example, the query "Give me a list of all posts with all of these tags, by any of these authors, sorted by post date descending"
db.posts.find({
tags: {$all: ["foo", "bar", "baz"]},
author: {$in: ["Joe", "Jane"]}
}).sort({post_date: -1})
SELECT posts.* FROM posts
INNER JOIN post_tags t1 ON t1.tag = "foo" AND t1.post_id = posts.id
INNER JOIN post_tags t2 ON t2.tag = "bar" AND t2.post_id = posts.id
INNER JOIN post_tags t3 ON t3.tag = "baz" AND t3.post_id = posts.id
WHERE posts.author = "Joe" or post.author = "Jane"
ORDER BY post_date DESC;
SELECT posts.* FROM posts
INNER JOIN post_tags pt ON pt.post_id = posts.id AND pt.tag IN ('foo', 'bar', 'baz')
WHERE posts.author IN ('Joe', 'Jane')
ORDER BY post_date DESC;
It's worth noting that I threw the SQL query a bone by denormalizing post_tags into one table. In a properly relational DB, you'd have a tags table, a posts table, and a post_tags join table, so the query gets even hairier (or you have to do two queries).
SELECT posts.* FROM posts
INNER JOIN post_tags pt1 ON pt1.post_id = posts.id
INNER JOIN tags t1 ON t1.tag = "foo" and pt1.tag_id = t1.id
INNER JOIN post_tags pt2 ON pt2.post_id = posts.id
INNER JOIN tags t2 ON t3.tag = "bar" and pt2.tag_id = t2.id
INNER JOIN post_tags pt3 ON pt3.post_id = posts.id
INNER JOIN tags t3 ON t3.tag = "baz" and pt3.tag_id = t3.id
WHERE posts.author = "Joe" or post.author = "Jane"
ORDER BY post_date DESC;
It's also worth noting that this generates a massive temp table to be sorted, which is very likely going to end up causing you to have to do a filesort. In practice, you'd probably break this down into 3 queries (forgive my mixing languages):
$tag_ids = SELECT id FROM tags WHERE tag IN ("foo", "bar", "baz")
$post_ids = SELECT posts.id FROM posts
INNER JOIN post_tags pt1 ON pt1.post_id = posts.id and pt1.tag_id = $tag_ids[0]
INNER JOIN post_tags pt2 ON pt2.post_id = posts.id and pt2.tag_id = $tag_ids[1]
INNER JOIN post_tags pt3 ON pt3.post_id = posts.id and pt3.tag_id = $tag_ids[2]
WHERE posts.author = "Joe" or posts.author = "Jane"
$posts = SELECT posts.* FROM posts WHERE id IN ($post_ids) ORDER BY post_date DESC;
SELECT * FROM post
WHERE id IN
(
select post_id from post_tags
INNER JOIN tags ON post_tag.tag_id = tag.id
WHERE tag.tag in ("foo","bar","baz")
GROUP BY post_id
HAVING COUNT(*) = 3
)
AND author IN ("Joe","Jane")
ORDER BY post_date DESC;
SELECT * FROM post
WHERE id IN
(
select post_id from post_tags
WHERE tag in ("foo","bar","baz")
GROUP BY post_id
HAVING COUNT(*) = 3
)
AND author IN ("Joe","Jane")
ORDER BY post_date DESC;Also what I like about the count approach is you can do "show me all the posts with at least 2 out of the 3 tags matching" fuzzier search if you desire.
In fact, you can take out the second part of the inner join condition and move it in the where clause, like this (execution plan will stay the same):
SELECT posts.* FROM posts
INNER JOIN post_tags pt ON pt.post_id = posts.id
WHERE posts.author IN ('Joe', 'Jane') AND pt.tag IN ('foo', 'bar', 'baz')
ORDER BY post_date DESC;SQL was originally designed so that people who were savvy but not necessarily developers were able to query databases, but I can't think of the last time my boss would have wanted to run some random query against our production database.
Trying to shove a MySQL square into the Mongo triangle just isn't going to work out that well.
Also that might be one thing it does but it also allows people to transition from SQL queries they know to mongodb queries. It helps the learning process.
I fully support it as a learning tool, but Mongo's query language isn't all that hard to pick up if you already know how SQL works. Getting stuck into the mindset of "just write relational DB-oriented software and then generate some Mongo queries for it" is an awful practice that is sure to lead to much pain and suffering.
Other gotchas too, like feeling like you can store any old json structure in your db when you can't.
Dots are reserved because they're part of the query syntax. Fair enough, but it's pretty crappy to have to unpick a whole data structure because it was fine until a random bit of UGC was entered (that's where my last fews hours just went).
It does feel like the data and the query syntax are too crossed over to me.
MongoDB's limit(x) and skip(y) are a shitload nicer than most of Microsoft's ideas about pagination. It was only in SQL Server 2012 that they came up with "OFFSET" instead of "google it".... http://stackoverflow.com/questions/2244322/how-to-do-paginat...
The thing I don't know about, though, is the "database is almost always the most constrained part of a solution". On this simple site search, we tend to notice the apaches taking a lot more cpu than the databases calls. I guess when our site gets bigger, you'd imagine it is easier to scale out the apaches than the database (with sharding), but we would still have a lot of room to improve the mysql layer anyway (memcached for example).
I assume the alternative is paginating server-side, which wastes some network bandwidth and processing time on the server.
The vast majority of pagination procedures occurs on the final rendered set. Meaning you've done 100% of the work on the database server to retrieve n% of the product. And when you come back for page 2, you're again doing 100% of the work for n% of the product. Pagination is not a SQL shortcut, and the sole "savings" it provides is network bandwidth between app server and database, which is seldom a limitation.
If you're keeping the result server-side, how are you storing it between requests in a stateless context like a web app? If I ask for page 1, and you get all the pages, do you cache them server-side? That would add a ton of complexity. I suspect your app is sending the first n rows to the client and throwing the rest away.
Having a dataset your web server can crunch and cache is just one use case.
If you don't like dealing with it directly, use something like MongoEngine so you're not working with the raw queries, or if having readable, easy to understand queries is important, use a SQL database.
Everything is a compromise, with Mongo's query language you're sacrificing readability for performance.
( This is not a comparison of a SQL database to Mongo, just the time it takes for a SQL engine to parse the query into an execution plan )
Maybe a little more accurate to say JSON is used as a base layer for the query language.
JSON is "JavaScript Object Notation". But the "meaning" of the query is in the objects being denoted, not the notation used to represent them as text. So comparing Mongo's use of JSON to SQL is apples-to-oranges.
We could encode SQL as JSON too:
{"query": "SELECT * FROM things;"}
{"query": [
{"SELECT": "*"},
{"FROM": "things"} ] }
For me the Mongo shell is just enough so-called "richness" and "expressiveness" (Try it yourself: http://try.mongodb.org/). There's a certain magic to passing objects to functions (and being able to, say, read the body of a function by typing that object into the CLI).
db.orders.aggregate [
$group:
_id:
cust_id: "$cust_id"
ord_date: "$ord_date"
total:
$sum: "$price"
,
$match:
total:
$gt: 250
]Plus, the API isn't really abusing JSON. It isn't pretty, but it's not abuse.
You can make a relational database that doesn't support the SQL syntax, and you can use SQL syntax to interact with schemaless data (for added fun, try throwing JSON in a mysql/postgres text field).
I'd agree with the article saying this is an abuse of JSON, though. It's a format to represent data; more accurately, potentially-nested key:value stores, arrays, and scalar types. A query is not data (unless you're one of those "my database has a 'queries' table" types)
Yes, it's bytes in memory like any other data, but it's the means rather than the end. You care about the data, and the query (or code) is what you use to get it.