Issue in Joining 2 documents

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 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 =
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

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


Check Example 12