JOIN Query not working

SQL++
1

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”

2

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

3

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"”
}
]

4 
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/

5

Thanks, this query worked

6

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.

7

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