Does N1QL support AND's

Does N1QL support a query like so:

SELECT * FROM posts WHERE category = 'Cars' AND visibility = 'EVERYONE' ORDER BY created

Will created sort on dates? I know with views getting posts that only contain specific category and visibility text is not possible, but wondering if N1QL makes it possible.

Thanks for the update. Are you using DP1 or DP2?

1 Answer

« Back to question.

Yes, both AND and ORDER BY are supported. Let us know if you run into any problems. Thanks.

AND seems totally slow. I think I get the idea why- but I just ingested 500000 documents each with the same name and different age, created Indexes on name and age (separately) and then ran select * from testBucker where name = "Corey" and age > 50. It took several minutes to come back. I'm really excited about N1QL. I'm currently using Couchbase to manage real-time property graphs in memory streamed in from Twitter Storm. The requirement for me is that clients be able to query the real-time graph in memory. I don't need to support complex traversals, mostly just adjacency lists.

I need the ability to support more complex queries. I don't mind having to index each of the keys/values that I want the ability to query

Try using EXPLAIN SELECT ...

to see your query execution plan. You can also post or send us the result of EXPLAIN.

I've gotta point out, I love that this is reminding me of Apache Pig.

{
"resultset": [
{
"input": {
"expr": {
"operands": [
{
"left": {
"left": {
"path": "testbucket",
"type": "property"
},
"right": {
"path": "age",
"type": "property"
},
"type": "dot_member"
},
"right": {
"type": "literal_number",
"value": 5
},
"type": "greater_than"
},
{
"left": {
"left": {
"path": "testbucket",
"type": "property"
},
"right": {
"path": "name",
"type": "property"
},
"type": "dot_member"
},
"right": {
"type": "literal_string",
"value": "Corey Nolet"
},
"type": "equals"
}
],
"type": "and"
},
"input": {
"as": "testbucket",
"bucket": "testbucket",
"ids": null,
"input": {
"as": "",
"bucket": "testbucket",
"cover": false,
"index": "age",
"pool": "default",
"ranges": [
{
"inclusion": "high",
"limit": 0,
"low": [
5
]
}
],
"type": "scan"
},
"pool": "default",
"projection": null,
"type": "fetch"
},
"type": "filter"
},
"result": [
{
"as": "age",
"expr": {
"left": {
"path": "testbucket",
"type": "property"
},
"right": {
"path": "age",
"type": "property"
},
"type": "dot_member"
},
"star": false
},
{
"as": "name",
"expr": {
"left": {
"path": "testbucket",
"type": "property"
},
"right": {
"path": "name",
"type": "property"
},
"type": "dot_member"
},
"star": false
}
],
"type": "projector"
}
],
"info": [
{
"caller": "http_response:152",
"code": 100,
"key": "total_rows",
"message": "1"
},
{
"caller": "http_response:154",
"code": 101,
"key": "total_elapsed_time",
"message": "2.131262ms"
}
]
}

Is Couchbase using the filter startKey and endKey to find matches for indexed components on documents? It seems like seeing to > 5 should be a very fast operation if the keys are being maintained in sorted order. it seems like the name = Corey Nolet should be the same. It seems weird how long the query is taking given I've only got one document out of 500,000 that matches both criteria.

So after some further tinkering- it looks like the cbq-engine gets itself into a bad state from time to time (seems like it's usually after creating/removing indexes) and it looks like mosts queries I run from that point (even simple where clauses that don't require AND) tend to take >1 minute to execute.

Upon restarting the cbq-engine, the queries went back down to 5ms or less.

@cjnolet, I think Gerald meant this comment for you but commented on the question rather than the answer. Interested in hearing the results as we are looking at using elasticsearch rather than N1QL because we are concerned about similar issues. We need our queries to be near real-time as well.

Thanks for the update. Are you using DP1 or DP2?

Gerald, does N1QL treat deleted items like views do. For example in my above query, if a item is deleted does an index need to be updated so a deleted item will not show up in the result set? It would be great if N1QL did not have the issue around deleted items. Also going the other way does an index need to be updated before a result is shown in the results set? Much like the update_after on views.

@mshaw -- The DP (preview) versions of N1QL use view indexes internally, so they inherit these properties of view indexes. For Beta and production, N1QL will have its own implementation and update semantics.

@gerald - Thanks. In Beta and Production will N1QL still be an eventually consistent or will the consistency be closer to a SQL db query? Also any word on when a beta might be released?

@mshaw - We're currently defining the range of options and semantics for N1QL users. Before Beta, we'll continue to release additions via the Developer Previews. We'll also stabilize the syntax before Beta, so your N1QL work will carry forward to Beta and production.

We're aiming for a significant Beta, which Is one reason we haven't offered a narrower timeframe than 2014.