N1QL Select with multiple inclusions/values

SQL++
1

Hi,

With ORACLE, I can do this:

select * from table where column in (valA, valB, valC);

With Couchbase N1QL, I’m trying to do the same but avoid expanding it:

select * from bucket where (
(attrib = valA) or
(attrib = valB) or
(attrib = valC)
);

Is there an equivalent to the “IN” operator like that of Oracle? I went through the documentation, but I cannot see that possible.

Thanks!

Regards, Steve

2

Hi @scheok,

It’s possible with a slight modification. The value array is specified in square brackets (like an array).

SELECT * FROM table WHERE column IN [10,20,30];

3

Thank you @keshav_m. This is exactly what I was looking for! :slight_smile:

1 Like
4

(I didn’t realize I posed this question earlier this year. :slight_smile:)

What if I want to use an index like:

attrib between valA and valC
(valA, B, C are positive integers in this case)

That might look like:

create index idx_test on bucket (attrib between valA and valC);

I can no longer use the IN [ … ] pattern?
Without that restriction on the the values for the index, the IN [ …] works perfectly fine.

EDIT1: Using the index with the restriction in values, I can still do the long way:
(attrib = valA) or
(attrib = valB) or
(attrib = valC)

Just not the short way:
attrib in [valA, valB, valC]

EDIT2: Index created with restriction either:
create index idx_test on bucket (attrib between valA and valC);
or
create index idx_test on bucket (attrib) where attrib between valA and valC;

Both work with the long way ( (attrib = valA) or (attrib = valB) or (attrib = valC) ) but not the short way with the IN operator.

5

create index idx_test on bucket (attrib);
This index works for all these queries.

SELECT * FROM bucket WHERE attrib IN [10,20,30];
SELECT * FROM bucket WHERE attrib between 10 AND 30;
SELECT * FROM bucket WHERE attrib between "abc" AND "xyz";

attrib, values can be number,string,bool, arrays, objects, or combination.

Check this out https://developer.couchbase.com/documentation/server/current/performance/index-scans.html
https://dzone.com/articles/n1ql-a-practicle-guide

6

Thanks @vsr1.

In my testing, those indeed do work. However, I wanted to create a function base index only a partial such that the coverage of the index would only be for the selected values. For example, let say that the values range from 1 to 5 (e.g., {1, 2, 3, 4, 5}), I would create the index like:

create index idx_test on bucket (attrib) where attrib between 1 and 5;
or
create index idx_test on bucket (attrib between 1 and 5);

Then when I try to the N1QL query:

select * from bucket where attrib in [2, 4];

I would get the standard error stating that there is no index/create a primary index.

If I don’t put in that “between 1 and 5” as in your example for the index creation, your solution works.
Any ideas?

Thanks in advance!

7

create index idx_test on bucket (attrib) where attrib between 1 and 5;
In above index contains only the documents attrib between 1 and 5 ad none of other documents.
The query need to use that index query predicate needs to have exact index where clause or subset of it. Other wise we can’t use that index because index will not have all the data, if used it result in wrong results.
You can try select * from bucket where attrib between 2 and 4;

create index idx_test on bucket (attrib between 1 and 5);
Index key is expression attrib between 1 and 5, so index has only result of expression i.e true or false not attrib. Query predicate needs to match exactly index key.

8

The former case is what I was trying to do. So I will have to create the index as:

create index idx_test on bucket (attrib) where attrib between 1 and 5;

These work without the predicate having the format “attrib between x and y”:

select * from bucket where attrib = 2 or attrib = 4;
select * from bucket where attrib = 2;

The idea is to create an index with only the documents within the range. Not sure if this would work to include “between x and y” predicate:

select * from bucket where attrib between 2 and 2;
(untested)

The first one wouldn’t work so well/easily since it would have to look like:

select * from bucket where (attrib between 2 and 2) or (attribute between 4 and 4);
(untested)

I think writing them out as the former (without the between) format would be easier.

Thanks again @vsr1.

9

create index idx_test on bucket (attrib) where attrib between 1 and 5;

I have tested in 4.6.3 the following queries uses index
select * from bucket where attrib = 2 ;
select * from bucket where ( attrib = 2 or attrib = 4);
select * from bucket where (attrib between 1 and 5) AND ( attrib = 2 or attrib = 4);
select * from bucket where (attrib between 1 and 5) AND ( attrib = 2);
select * from bucket where (attrib between 1 and 5) AND ( attrib IN [2,4]);

Only IN clause will not use the index, I have provided the workaround above

select * from bucket where attrib IN [2,4];

10

Thanks @vsr1!
Odd, I thought I had actually tried this one before:

select * from bucket where (attrib between 1 and 5) AND ( attrib IN [2,4]);

It works! Much appreciated! :+1: