Couchbase N1QL: index and query on array fields

Question

Platform: Couchbase Server 4.0 beta, Java client 2.1.3

I am looking for something similar to SQL JOIN. For example I have documents of the form where field2 is embedded in the document instead of in a separate table as you would have in a relational DB:

    {field1:" ..", field2:[{key:1, ...},{key:3, ...},..],...}.

How can I achieve something like this:

    select * from bucket where field2.key=3;

And how can I index the key, a hypothetical example:

    create index idx_key on bucket(field2.key);

Nic Raboy · Accepted Answer · 2015-07-09 16:34:10Z

1

What if you did something like this:

SELECT 
    *
FROM `your-bucket-here` AS fields
WHERE 
    ANY field IN fields.field2 SATISFIES field.key = 3 END

This way as long as one nested array item contains your value, it will be returned.

In terms of creating an index, are you looking to create a secondary index or a primary index? You could always do something like this:

CREATE PRIMARY INDEX index_name ON `your-bucket-name-here` USING GSI;
CREATE INDEX index_name ON `your-bucket-name-here` USING GSI;

Let me know how all that goes!

Best,

answered Jul 9, 2015 at 16:34

Nic Raboy

3,15127 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

P T Over a year ago

Your query works, thanks @Nic. I came up with a different query using UNNEST that also works. The second part of the question still remains, how do you create a secondary index on field2.key.

Simon Baslé Over a year ago

the CREATE INDEX (secondary index) syntax should provide the field in parenthesis like CREATE INDEX index_name ON bucket(field2) USING GSI;. So I wonder if you could replace field2 with field2.key, but I'm not sure...

Simon Baslé Over a year ago

to check that an index is used, you can use EXPLAIN SELECT... (prefixing your statement with EXPLAIN to get the execution plan and look for the index name in there)

P T Over a year ago

Thanks @Simon for the suggestion. Already tried. Did not work. By the way this is how multikey index looks like in MongoDB.

Collectives™ on Stack Overflow

Couchbase N1QL: index and query on array fields

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related