Issue in Joining 2 documents

.NET SDK
, ,
1

I am using Couchbase Lite 3.0.2 version, ASP .Net Core 6
I have to query some documents based on joining its id with id of a nested array object of another document. when I run the query I am getting the below error
“No index available for ANSI join term p”

I tried to create index as follows
CREATE INDEX idxId ON bucket(PipeId, ARRAY p.id FOR p WITHIN Pipes END) where docType=‘Test’ OR docType=‘TestDoc’
it did not work. How to create the index in this case.

My query is like this.
SELECT t.* FROM bucket t
JOIN bucket p ON t.pipeId = p.id
WHERE t.docType = ‘Test’ AND t.ownerId = ‘3’
AND p.docType = ‘TestDocument’ AND p.ownerId = ‘3’
AND ANY item IN p.Pipes SATISFIES CONTAINS(item.Name, ‘Pipe’) END
ORDER BY t.TallyNumber

2

You need an index on “id” at least since this is the column in “p” that you’re joining on.

CREATE INDEX ix_for_t ON `bucket`(`ownerId`,`docType`,`TallyNumber`,`pipeId`);
CREATE INDEX ix_for_p ON `bucket`(`ownerId`,`docType`,`id`,`Pipes`);

(If ix2 is too large, you can omit Pipes and have that be applied simply as a post-fetch filter.)

Alternatively if you’re going to have only these specific docType values:

CREATE INDEX ix_for_t ON `bucket`(`ownerId`,`TallyNumber`,`pipeId`) WHERE `docType` = "Test";
CREATE INDEX ix_for_p ON `bucket`(`ownerId`,`id`,`Pipes`) WHERE `docType` = "TestDocument";

will likely be better.

In upcoming versions some of the index requirements for joins have been relaxed (but for optimum performance you’ll still need them).

HTH.

3

Check Example 12