Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

MongoDB Is Abusing JSON

smsohan.com

Post reply on HN
Print view View original

31–40 of 61 posts

Re: MongoDB Is Abusing JSON

#31

Post by djb_hackernews »

cheald wrote: post #24

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" db.posts.f…

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.

Re: MongoDB Is Abusing JSON

#32

Post by cheald »

djb_hackernews wrote: post #31
cheald wrote: post #24

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" db.posts.f…

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.

Re: MongoDB Is Abusing JSON

#33

Post by sm_sohan »

greendestiny wrote: post #27
mglukhovsky wrote: post #8

Here's what this query looks like in RethinkDB (also based on JSON documents): r.table('orders') .pluck('cust_id','ord_date','price') .groupBy('cust_id','ord_date', r.sum('price')). .filter(r.row('reduction').gt(250)) 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 simulate…

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.

Totally agree with you.

Re: MongoDB Is Abusing JSON

#34

Post by dpe82 »

corresation wrote: post #28
benologist wrote: post #9

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...

There are extremely few modern cases where pagination at the database layer is a good approach.

How else do you do it? Select the entire table and filter it at the application layer?

Re: MongoDB Is Abusing JSON

#35

Post by sm_sohan »

samarudge wrote: post #5

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 databas…

This doesn't have to be this way. The underlying machine friendly API can be hidden under a human friendly API abstraction.

Re: MongoDB Is Abusing JSON

#36

Post by aidos »

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.

Re: MongoDB Is Abusing JSON

#37

Post by gumbo »

cheald wrote: post #24

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" db.posts.f…

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.

Re: MongoDB Is Abusing JSON

#38

Post by alanctgardner2 »

corresation wrote: post #28
benologist wrote: post #9

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...

There are extremely few modern cases where pagination at the database layer is a good approach.

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.

Re: MongoDB Is Abusing JSON

#39

Post by kogir »

corresation wrote: post #28
benologist wrote: post #9

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...

There are extremely few modern cases where pagination at the database layer is a good approach.

How about any cases where you don't want to transfer all the rows you're skipping over the wire? As in, nearly all cases?

Re: MongoDB Is Abusing JSON

#40

Post by kogir »

andrewmunsell wrote: post #12
benologist wrote: post #9

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.

What was wrong with ROW_NUMBER()? It worked just fine for me, and partitioning was frequently useful.
Post reply on HN