It's not JSON, but rather that MongoDB's query language sucks. I'm not entirely sure how to fix it - perhaps make it slightly more verbose and meaningful?
Probably there are better ways to express a query in JSON compared to how MongoDB does it, but I'd take a step back and ask whether a nested map is the best approach to think about it.
I actually prefer Mongo's query language to SQL, because it makes sense to my programmer brain. It's basically "push operand, push values", which makes a ton of sense. It's just plain old Polish notation.
I actually dislike this tool a fair bit, because it gets people to continue thinking of Mongo as "Mysql plus WEB SCALE" or whatever. It's a totally different database and doesn't do well with highly-normalized relational-style data, and the idea of an "automated converter" seems to reinforce the idea that it's just a drop in for MySQL that automatically solves all your scaling woes, when that's just utterly and completely false.
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.
No, I mean MySQL. The tool linked is specifically tooled for MySQL queries.
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.
MongoDB is a NoSQL-type database, so it wouldn't make sense to have a SQL query interface... I think they did a good job with the API for not using SQL.
Plus, the API isn't really abusing JSON. It isn't pretty, but it's not abuse.
People seem to be confusing "NoSQL" and "non-relational". Mongo happens to be both, but there's nothing fundamental that puts the two together. You may not get the full capabilities of SQL with non-relational data (JOINs, etc), but there's no reason that non-relational data stores couldn't parse normal SQL and execute the appropriate queries.
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.
JSON is used as a query language because it's fast, easy to parse and easy to generate dynamically. If you have a query interface for users, SQL is probably a better choice, but Mongo chose JSON for performance reasons.
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 use the hard-coded attribute 'reduction' because groupBy automatically gets compiled to our distributed map-reduce infrastructure. There is currently no as command (though it could easily be simulated with map). I'll add a GitHub issue for this shortly, since we should add sugar for it.
Most ORMs use dot notation, even for mongo (e.g. mongo engine) to build queries. I think the mongo queries are more true to what it should be since dot notation in most languages implies a function/method is being called and order matters in those cases. Here it's just acting like a query builder that runs with .run().
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.
The beauty of JSON is you could incredibly easily write such jquery like chained expression to generate the mongo query. JSON is a good ascii representation of structured data thats compact, quite readable and reasonably human editable.
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.
What are the main differences between MongoDB and RethinkDB? Things that you hear people care about? I don't use Mongo but I've seen projects that use it. I ask because I want to store & retrieve some analytics.
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
We're going to publish some comparisons in a few days, but basically you nailed it (both pros and con). We're trying to build a stellar data-oriented development environment/query language, and a stellar scalability infrastructure. All the main pieces are there, and Rethink is a lot of fun to use. The rough edges will be polished out in over the next few months.
You picked an ugly mongo query, and there are many. You compared it to a concise SQL query, and there are many that are not.
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...
I literally just ran into the same issue with SQL Server. Since we're running an older version, I was very confused when I found the alternative to MySQL's "LIMIT"-- it wasn't pretty.
That depends upon the definition of "entire table". Most situations have already pre-filtered data to some reasonably small quantities -- e.g. category, genre, exchange date, etc. Yes, caching and working on that set is almost always the superior option.
This is an interesting statement to me. My goal in retrieving data is normally to reduce the result set as fast as possible. If I only need a subset of the data, I would do the bare amount of joining, aggregation, etc. before paginating.
I assume the alternative is paginating server-side, which wastes some network bandwidth and processing time on the server.
If I only need a subset of the data, I would do the bare amount of joining, aggregation, etc. before paginating.
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.
I think your whole argument is based on the false assumption that you have to paginate after you've done everything else. If you have a well-normalized database, any decently complex query will involve a bunch of joins. If your sort is keyed on one or two fields in one table, why not sort and filter that table and join the result to the others? Admittedly I'm a bit of a DB novice, and perhaps you can speed the join up better by indexing, but it seems like to get 50 rows from two 1000 row tables, it makes more sense to filter 50 rows, then join, rather than the other way around.
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.
You wouldn't be "skipping" those rows because they would be transferred once. The database is almost always the most constrained part of a solution, and the various hack pagination techniques are almost always (not 100%. More like 98%) a naive mistake.
I am really interested in this argument, because I am currently converting a mysql based web search to use apache solr. I have been thinking about all the arguments about pagination a lot.
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 don't think it's fair to take JSON out of context from the rest of the query API. Or maybe it's being overly generous, I'm not sure. Either way, this is the same way object literals are constructed in JavaScript. So is the beef w/JavaScript?
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).
I've been through the hassle of programatically piecing together complex SQL queries, and I'd far rather be able to just put together hashes that represent my query.
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.
I am not sure of its origin. But these days you'll find good ORMs that would craft the query for you. My point is, the Mongo API seems to be more of a machine readable API with the $ keys used somewhat in a hacky way.
Surely by the time you're programmatically generating SQL queries you should be using either an ORM or some other kind of SQL expression language embedded in a more expressive language (such as SQLAlchemy or the myriad lisp DSPs for relational databases)?
I feel the same about the query language of Mongo.
The first goal of a query language should be ease of use. With mongo having to type all those extra characters quotes, brakets, square brakets, colons is very annoying.
You need to type a lot to get any reasonable output.
Aggregation is absolutely one of Mongo's weaknesses. It's not great at ad-hoc aggregation like MySQL or whatnot is, and the fact that it tends to lend itself to denormalized data makes SQL-style reporting clunky at best.
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"
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;
Its strength is denormalization; since you can denormalize entire lists or maps of data into a document, and then index and query on them, you can end up performing queries that would be ridiculously ugly and tedious in SQL.
not to take away from any of your great points in this post but isn't it the same as:
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;
My SQL is rusty, I could be missing something but they seem essentially equivalent if you use the SQL helpers such as IN.
No, because that'll select any post that contains any of those three tags, not the posts that contain all three tags. AND vs OR.
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;
Yikes.
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;
Easily doable in both languages, but Mongo's denormalized structure makes this sort of use case a ton simpler.
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;
Of course normalizing tags is silly as they are natural keys so it should be as you first stated:
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;
Sorry I only use proper DB's like PostgreSQL and MSSQL who optimizer's have no problems with this ;).
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.
I'm not sure what you mean, but the parent's SQL is valid and returns the same data as the gp's, only in a more efficient manner. No need to do three inner joins when you can get away with one.
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;
Hashes are very handy and that a strength of mongo.
However as someone using mongo on a daily basis (and often from within the console), i need to admit that performing query is hard. However if you are using it from a app you are building there are many library that make it easier.
I'm just finishing off a project that was built using Mongo and I've run into this as well.
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.
I kinda agree (about the free part), but I just wanted to say that if you are parsing sql strings you really should get an ORM. Plug in a well tested, optimised library and never think about parsing sql strings again. It also makes things easier to test because you get nice separation of concerns.
60 comments
[ 3.2 ms ] story [ 125 ms ] threadTrying 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.
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.
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:
or without affecting the expressive power of the SQL language one bit.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.
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
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...
Having a dataset your web server can crunch and cache is just one use case.
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.
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).
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).
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.
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"
In SQL, you'd end up with something like: Its strength is denormalization; since you can denormalize entire lists or maps of data into a document, and then index and query on them, you can end up performing queries that would be ridiculously ugly and tedious in SQL.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).
Yikes.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):
Easily doable in both languages, but Mongo's denormalized structure makes this sort of use case a ton simpler.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):
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.