JOIN Query not working

Hi,
I am trying to fetch 2 documents in the same bucket - bucket-cms , so the catch is I have key ‘pageId’ value for the first document ’ r’ and then from that document I need to get another key ‘contentid’ and then make the search for another document ‘a’ in which this key equals to ‘fragmentId’ I am using the below query but it is not working. Can anyone help me. Below is the query.
SELECT *
FROM bucket-cms r
JOIN bucket-cms a
ON r.contentid = a.fragmentId
WHERE r.pageId = “987675”

For given pageId you want join contentid the query is right.
If further help is needed, please post the sample documents and expected output.

This is my first document, i want to identify this first based on pageId
Document 1:

{
“name”:“Document r”,
“contentid”: [
“00000001”,
“00000002”
],
“pageId”:“98765”
}

once I get this I want to take the contentId array and find other document which contains a fragmentid key with any of this contentid array value.
Document 2:

{
“name”: “Document a”,
“description”: “desc”,
“fragmentId”: “00000001”
}

Here is the query, i am making -

SELECT *
FROM bucket-cms r
JOIN bucket-cms a
ON r.contentid = a.fragmentId
WHERE r.pageId = “987675”

and this the error I am facing -

[
{
“code”: 4330,
“msg”: “No index available for ANSI join term a”,
“query”: “SELECT *\nFROM bucket-cms r\nJOIN bucket-cms a\nON r.contentid = a.fragmentId\nWHERE a.pageId = "987675"”
}
]

CREATE INDEX ix1 ON `bucket-cms` (pageId);
CREATE INDEX ix1 ON `bucket-cms` (fragmentId);

SELECT *
FROM `bucket-cms` AS r
UNNEST r.conentid AS cid
JOIN `bucket-cms` AS a  cid  = a fragmentId
WHERE r.pageId = "98765";

Example 13: https://blog.couchbase.com/ansi-join-support-n1ql/

Thanks, this query worked

Hi @vsr1 , This query is working but there is one catch when the contentid is not present in the first document, document1 I just want this query to return the document1, where pageId is 98765. that means only first document.

But this query is not failing in this scenario and not returning anything.

Do u need LEFT UNNEST

SELECT *
FROM `bucket-cms` AS r
LEFT UNNEST r.conentid AS cid
JOIN `bucket-cms` AS a  cid  = a fragmentId
WHERE r.pageId = "98765";