CODEWITHSUNDEEP
couchbasesql++
P T
P T

Reputation: 75

Couchbase N1QL: index and query on array fields

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);

Upvotes: 1

Views: 3772

Answers (1)

Nic Raboy
Nic Raboy

Reputation: 3153

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,

Upvotes: 1

Related Questions