from Hacker News

Aggregating NBA data, PostgreSQL vs MongoDB

by sanityinc on 2/18/14, 11:32 AM with 8 comments

  • by sanityinc on 2/18/14, 11:35 AM

    > Let's ignore the [mongo] query format itself, as it's obviously meant to be generated by a tool rather than typed by a human being. Here's the same query in SQL, with the result this time

    Priceless.

  • by raphinou on 2/18/14, 1:04 PM

    I've migrated data I stored in mongo to a postgresql, but kept it in json format. The ease of querying this brought is impressive thanks to postgresql's json support. The only problem I encountered was with nested json objects. I keep telling myself I should blog about it, to illustrate data collection framework for smaller shops for who it wouls be overkill to deploy Hadoop or Cassandra.

  • by chris_wot on 2/18/14, 12:35 PM

    So if I understand this correctly, the original article [1] shows that the mongodb queries being used rely on doing projections and selections on the data, then they apply aggregations.

    However, unlike a relational engine which uses a query plan, you need to actually tell the aggregation engine in which order to process the data.

    Doesn't this seem a little... backward? If the dataset is complex enough, the two things that come to mind are:

    1. There would be a lot of redundancy in the hierarchical dataset.

    and

    2. That seems like a lot of futzing around to get the most optimal aggregation path!

    What am I missing here?

    p.s. I do tend to agree that the SQL syntax is rather more readable, but then again I've been looking at SQL for a lot longer than I have JSON style queries. The first query is not that unreadable!

    1. http://thecodebarbarian.wordpress.com/2014/02/14/crunching-3...

  • by taylonr on 2/18/14, 2:32 PM

    Just to nitpick, the article says aggregates have been in SQL for 3 or 4 decades, but SQL wasn't an ANSI standard until 1986 (28 years ago, almost 3 decades.) I get that a language exists before it is standardized, so were aggregates in SQL in the 70s/ early 80s or is this just a hyperbolic statement?