Support JOINs in Mango

[wohali]

@davisp:

JOINs make me squeamish.

@janl:

I hear ya, but devs love em :) — And a native API would simplify a lot of app and library code.

I’d definitely say it shouldn’t be full SQL JOIN semantics, but a simple LEFT JOIN on a key over two views should be fine and address a good bunch of use-cases. Basically, anything that isn’t conceptually different from merging multiple shard-view results on the top node, just with two views.

@nolanlawson:

Relevant pouchdb-find discussion: https://github.com/nolanlawson/pouchdb-find/issues/157

[natcohen]

I think it will make CouchDB more appealing to "SQL-familiar" developers...

I remember reading a post from @janl (for the 2.0 release) about a potential SQL to Mango adapter in the future. Mango is probably far from this but adding JOINs would be a big step in this direction.

[wohali]

@natcohen FYI one way this can be achieved today is via the partial indexing feature. It's not perfect, but you might want to give it a look. Basically, make an index that represents the first part of the join, then use a selector on that partial index specifically to restrict the query further.

[natcohen]

@wohali Thanks for the workaround but like you said, it's not perfect and it's a "workaround". I think CouchDB will have to support more SQL functions to attract more developers. Storing data, which CouchDB does perfectly, is one thing but developers also need to retrieve it and frankly CouchDB is not very good at it. I really think CouchDB developers should focus on that in the following releases...but that's just my opinion!

Thanks for the hard work!

PS for the skeptics https://blog.timescale.com/why-sql-beating-nosql-what-this-means-for-future-of-data-time-series-database-348b777b847a and https://static.googleusercontent.com/media/research.google.com/en//pubs/archive/46103.pdf

[jeremielodi]

Why not to incorporation features like https://github.com/AlaSQL/alasql for retrieving data?