N1QL - dynamic USE KEYS array

SQL++
,
1

Hello,

I have documents with date property, and i need to query those by date range. the date is in the doc id, as well as in a property date. I have figured out two options:
1.

USE KEYS ["audience:aud1:2016-01-01", "audience:aud1:2016-01-02", "audience:aud1:2016-01-03", "audience:aud1:2016-01-04"]

WHERE date BETWEEN '2016-01-01' AND '2016-01-03'

I tend to believe that option #1 is more efficient, because it uses the primary key index. of course I can create a secondary index on the field date but it would consume its resources.
However, in option #1 I must pass all dates in the range. if its a range of 30 dates, it means 30 values… instead of just begin/end date with BETWEEN clause.

Is there a way to benefit both worlds, and generate the USE KEYS clause dynamically, given begin/end dates of my range?

Thanks,
Liran

2

You have two options.

(1) Option 1 is good. 30 values is not a problem.

(2) If you want a range, you need a secondary index. You may also want a covering index, depending on your query.

1 Like
3

thanks @geraldss
I’ll stick with option #1 and generate the dates my self in the client from the begin/end values.

4

Sounds good. There is also a way to generate the USE KEYS array inside N1QL, using a combination of N1QL expressions:

ARRAY_RANGE(), ARRAY your_string_concatenation … END, etc.

But you can do the same thing in your client.

5

I don’t think its the right way to go, because it will only work within a month. if I need a range from the middle of one month to the next, it won’t work. For example, I need something smart enough to know when is the last day of the month, and know to advance the month after the last day (01-30, 01-31, and then go to February with 02-01).
Also, If i do string concatenation with your suggestion, i need to handle case when day value is one digit, and add padding zero before it… and things like that, which opens more room for mistakes (so “1” turns into “01”, but “15” is just “15”)

Is there a way to do dates range somehow?
or to do USE KEYS with greater than operator? something like "give me all documents of primary key greater than ‘2016-06-01’ similiar to what we can do with regular map-reduce views

6

We would have to add a date range function. I will file a JIRA ticket.

I take it you figured out to cast to string using TO_STR() before concatenating.

1 Like
7

great, thanks. I also suggest to add an abillity to search by the primary key with ranges.

8

You can search primary key with ranges. Just not with USE KEYS. You can use a WHERE clause. Back to option 2. :slight_smile:

9

So what’s the benefit of USE KEYS over WHERE on the doc ID?

10

USE KEYS avoids the index scan. But if your use case requires an index scan, that’s totally fine. It’s why we have indexes :slight_smile:

11

So my suggestion to add that ability to USE KEYS still makes sense, if it’s
more efficient (is it?) and I guess the underlying index structure already support
it so it’s just a matter of implementing it

12

For ranges, it needs to be an explicit index. USE KEYS is only for discrete values, not for ranges.

13

Again, this is a suggestion. I unserstand that TODAY we can only use it for discrete values…

If Couchbase would allow to use it with ranges as well as I suggested, it means users would not have to create a secondary GSI index just for that purpose, and save the costs of an index. I think my suggestion makes a lot of sense.

1 Like
14

Got it. FYI, Couchbase stores data in a distributed hash table. This make key lookups very efficient, but makes range scans inefficient without an index.

15

Oh, I see. it makes sense. so i guess my range suggestion is not so good :slight_smile:

thank you for the help, and for adding my idea of adding a util function to create dates range to your JIRA.

1 Like
16

How do I add a static string to every element in an Array in USE KEYS clause?

I want to do something like this:

SELECT colA, colB
FROM
my_bucket
USE KEYS ["1234", "5678", "7777"] ||  "::ConcatString";
17 
SELECT colA, colB
FROM
my_bucket USE KEYS  (ARRAY v ||  "::ConcatString" FOR v IN  ["1234", "5678", "7777"]  END) ;
1 Like