How to make a view equivalent to the following SQL?

N1QL in its current state isn’t fast enough (by several orders of magnitude) to use even for development right now. I understand that it is under development and performance hasn’t been a concern yet.

However, I’d like to use Couchbase now and I’m trying my hardest to select it as the database for my project. Since N1QL isn’t ready I must use views. I need performance comparable to MySQL for my queries.

I know SQL so I’ll state one of my needed queries as a SQL query. It is a pretty basic SQL query, trivial for the average SQL programmer. No joins or anything very complex:

theDocumentID As id, UIDValidity, UID, * FROM messages AS message WHERE message.type = "message_header" AND message.account = "" ORDER BY UIDValidity DESC, UID DESC LIMIT 40

This has to execute fast (<1 sec preferably) and be on the most recent data. I need to be able to change the "" and possibly “message_header” values to different values on EACH query I make. This is trivial with SQL, how hard is it with Couchbase? Is it doable?

How do I do this basic SQL query using a view?, and what are the parameters I need to give to filter by message_type and account which are dynamic and specified application-side, and what parameters do I need to sort by descending, twice (not alphabetical string descending, but numerical descending).

So far from my reading of documentation and blogs, etc, this relevantly trivial SQL seems like a really tough thing to do in Couchbase, which means I may have to drop Couchbase for use in my current project.

I’m hoping a full example of how to solve this problem and its various aspects will give me a clue as to how to handle future queries in Couchbase, and I imagine it would help others a great deal if they’re considering Couchbase.

I need this query to go over 25,000 items right now (will be hundreds of millions in production and more), and I need the data to be current as of the last write, and I need the performance to be similar to MySQL or better (easily <1-2 sec).

How would you do this SQL query, the “Couchbase” way?

the exact syntax would depend on your attribute names in JSON so you may need to do some edits here but I’ll take a stab. There are a few assumptions:

  • I assume you will have many more types of data beyond “messages” in the database (or in sql terms many tables beyond a single table). You will filter on the doc type=“messages” so you can get a dedicated index for doc.type = message.
  • I assume this is the only query, if you have other queries, you may create an index that does not emit all fields (or create a fully covering index) and instead, can look up based on keys you get from the view.

your view would look something like this:
function (doc, meta) {
if (doc.type="messages"
emit([doc.messege_type,doc.message_account],[doc. all the fields you want to emit]);

in your query you could specify the key to be [“messege_header”,“”].

@cihangirb Thanks for your example.

How to do the full query… the “ORDER BY” part (numerically)?: