Hierarchical joins

The arrayfromSelect and documentfromSelect functions shorthand "[]" and "{}" respectively can be used to perform hierarchical joins. These two functions accept a select statement as an input parameter. These functions can then be nested multiple levels to output a rich JSON document with input from multiple tables.


The below query joins two collections to the NewsFeed collection to retrieve Comments associated with an entry in the newsFeed as well as the user details associated with the comment. Note only 20 entries in the NewsFeed collection are returned as well as only the first 10 comments per post are returned.

SELECT summary, post_date, _id, [( SELECT _id, text, {(SELECT _id, display_name FROM Users where _id = c.userid)} as user FROM comments c WHERE postId = n.id LIMIT 10 )] as comments FROM news_feed n WHERE tags = 'tech' and postdate < '2015-10-02T01:11:18.965Z' LIMIT 20 # The above query will output documents with the below structure. { "summary" : "...", "post_date" : "...", "_id" : "..", "comments" : [ { "_id" : "...", "text" : "...", "user" : { "_id" : "...", "display_name" : "..." } } ] }