Index and range scan for primary id

Couchbase Server
1

Please bare with me if my question(s) sound stupid since I am fairly new to Couchbase…

I have the following scenario:

I have a number of documents in a bucket. They are JSON based and are “typed” with a _type field. e.g. A, B, C, D.

What I need to do is return all documents of a specific type where they document id (primary key) is within a specific range. In SQL, a standard query for this would look something like:

select * from default where _type = “A” and {PK / doc id} >= “start” and {PK / doc id} < “end”;

What would be the best way to achieve this using secondary indexes and N1QL? i.e. what kind of index should I create and what does the range query need to look like?

Ideally I want to avoid creating a primary index since I don’t need ad hoc queries, and I don’t want to have to index every single document in the bucket.

On a slightly different note… unless I am mistaken, it looks like you can’t create / use views or indexes on memcached buckets. Is that correct? Is this something that will change at any point?

Thanks!

2

Hey @ytn,

When you say document id are you referring to the document key or some property that you’ve created within the JSON document? Also, is this document id a numeric?

That information should help me better answer your question.

Best,

3

Thanks for the response. The document id I am referring to is the document key. not a property within the document itself.

Also, the document id in this case is alphanumeric. It’s actually a concatenation of UUIDs and other fields. In some cases, it’s a concatenation of alphabetic with numeric fields, where I may want to range scan on the numeric part of the key e.g.

Key 1: ABCD:1972

Key 2: ABCD:2100

In other cases, I basically just want to get all keys with a specific prefix, so I need to range scan in this way:

>= "ABCD:"  <  ("ABCD:" + "\uefff")

Thanks.

4

Hey @ytn,

You should be able to do a range scan like the following:

SELECT *
FROM `bucket-name` AS bn
WHERE META(bn).id BETWEEN 'ABCD:1972' AND 'ABCD:2100';

This becomes possible after setting a GSI like the following:

CREATE INDEX range_index
ON `bucket-name`(META().id)
USING GSI;

If you want to find all keys with a specific prefix you should be able to get away with something like this:

SELECT *
FROM `bucket-name` AS bn
WHERE META(bn).id LIKE 'ABCD:%';

Does that answer your questions?

Best,

5

Thanks, that answers my question and works as expected!

1 Like
6

No problem!

Reach out if you need anything else :slight_smile: