N1ql query views

SQL++
1

Hi,

I’m just getting started with Couchbase and I discovered N1QL which makes things easy …

Is it possible to query “views” using N1QL ?

Thanks,

2

Hello,

The answer is yes.

CREATE INDEX index-name ON bucket-name(field-name)

will create a view in the background.

EXPLAIN SELECT … WHERE field-name …

will show the query plan, which will indicate if your SELECT statement would use the view.

3

Can u please provide an example of it. Especially in ’ WHERE field-name ’ it’s not clear

4

CREATE INDEX idx_abv ON beer-sample(abv);

EXPLAIN SELECT COUNT(*) FROM beer-sample WHERE abv > 20;

SELECT COUNT(*) FROM beer-sample WHERE abv > 20;

5

Ok, I’m understood idea. So what i want to do:

  • i have a list of tags in a document,
  • there are few use-cases based on these tags

What i did,
First, create index actions_idx on default (actions). ‘actions’ a dynamic field.
Second, I fount this index(actions_idx) in interface and placed these lines in it

*** var actions = [];

for(var i in doc.tags){
var t = doc.tags;
if(t.member.action1)
actions.push(“Action1”);
if(t.member.action2)
actions.push(“Action2”);
}
doc.actions = actions;***

for field actions ( ex, [“Action1”, “Action2” …]).
Third, now when i’m testing
explain select count() from default where any act=‘Action1’ OVER actions AS act*.
I got an error Syntax error…
It’s seems like usage of OVER clause is not right…

Fourth, So i simplified view, so actions field contain just one value.
Quering: explain select * from default where action='Action1’ - showed me that it is using my view.
But without explain it returns nothing.

If you understand anything from what i wrote. Please, Help =)

6

What is the output of:

select * from default where action=‘Action1’ limit 1;

Please post the result here.

Also, are you using the N1QL documentation? Here it is: http://docs.couchbase.com/prebuilt/n1ql/n1ql-dp3/

7

Hey Gerald,

I’m trying to follow along with the original question and I’m not sure if I’m getting it properly.
is it possible to run a n1ql query against an existing view? something like this.
Select * From MY_VIEW_INSTEAD_OF_BUCKET where id = 12. with the example you gave I’m not seeing how couch knows to run against your view.

Bonus Question what is the difference between the index you created and the one given in all of the tutorials which is CREATE PRIMARY INDEX ON beer-sample USING GSI; this “Using GSI” part i’m not understanding since GSI isnt a field of any document in that bucket.

8

Hi @mlblount45,

I would refer you to the N1QL documentation. In short, you cannot query views directly. N1QL can create and use indexes using views, and it can also create and use indexes using a dedicated technology called GSI. All the details are in the documentation.

1 Like