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