I am trying for the life of me to understand the proper syntax of subqueries and join in n1ql, plus understanding what is better thing to use for my simple use case, but I can’t find anything like that n the forum or documentation.
There are examples but they are not thoroughly described and it is hard to understand to syntax of a subquery and of JOIN. A general form of subquery and join would be wonderful in documentations…
for my question, I have three doc types on the same bucket, users, vouchers and brands.
each voucher has a “userID” field which holds the id of a user and a brandID which holds an id of a brand.
I want to get a table of users, each with a field “vouchers” (what you call NESTED) in which each line has some voucher data plus some brand data of the brand of that voucher.
so 2 questions:
what is better, subquery or join?
if subquery, do you allow them in SELECT clause? (syntax would be great + an example).
if join, syntax would be great+ an example.
You can do do the following using JOINs. We will add more examples to the documentation.
SELECT `user`, ARRAY_AGG({"voucher":voucher, "brand":brand}) AS vouchers
FROM my_bucket voucher
JOIN my_bucket `user` ON KEYS voucher.userID
JOIN my_bucket brand ON KEYS voucher.brandID
WHERE voucher.type = "voucher"
GROUP BY `user`;
what does array_agg does exactly (isn’t it like nest?)
here you join user and brand to voucher. that means that if there is a user that has no vouchers, he won’t show up in the results. That’s not what I need. I need a left join of users to vouchers and vouchers to brands (that is, also, if the voucher has a brand that doesn’t exist it will still show up in the user’s vouchers sub-table).
You can do the following using Couchbase 4.1. You will need an index on userID. Note that ARRAY_AGG() is explained in the documentation.
SELECT `user`, ARRAY_AGG({"voucher":voucher, "brand":brand}) AS vouchers
FROM my_bucket `user`
LEFT JOIN my_bucket voucher ON KEY voucher.userID FOR `user`
LEFT JOIN my_bucket brand ON KEYS voucher.brandID
WHERE `user`.type = "user"
GROUP BY `user`;
But if we talk about documentation already, I think I’ll take the stage to note that you probably should overhaul your docs.
These are the links that I have for n1ql
if you notice, the first link doesn’t do a very good job in explaining beyond the basics and on very specific examples (which repeat themselves in terms of use case, what about other use cases?)
the second link gives examples but fails to specify the syntax for more general or less general cases. Very hard to understand query structure from that.
the third one lists features but fails to link each feature to a documentation page.
I think these things should be attended as part of your n1ql effort because as of now I’m using n1ql but find myself experimenting or searching too much to get what I am after.
I know the Tech is new, but docs are important.
Thanks for the feedback. I have shared your note with our docs team. For more advanced usage, real-world scenarios like yours will help shape the documentation of advanced use cases.
Of course, I am happy to help promote new techs, especially ideas like N1QL.
I would suggest to actively form a community and track it on stackoverflow as it is a starting point for questions for devs (at least for the ones I know).
I learned most of what I know from answers from there and not specific forums.
Just give yourself a go on google
When you search about redis, mongo, sql and what not, you usually end up with more than one tab of a well explained answer regarding what you seek (if you know how to search).
Up until now, I don’t think I ended up finding any of my questions on Stackoverflow.
What I would think of doing is dumping your forum into there, cause your forum does supply answers. That is all good and well but I think the issue is that although N1QL is new, Couchbase is not and everyone is excited about using your new tool and it can be a bit frustrating writing in your forum knowing only a few people will reach it.
Your forums don’t come up high in google search results, you see…