N1ql query clarification

n1ql

#1

sample : {
“id”:123,
“ref_id”:xyz123,
“name”:ttt
}

sample:{
“id”:456,
“ref_id”:abc123,
“name”:ghg,
“main_ref_id”:xyz123
}

how can I get both the above records when I give id = 123


#2

@appy789.

You can use the below string functions LINK BELOW.
https://developer.couchbase.com/documentation/server/current/n1ql/n1ql-language-reference/stringfun.html

So you might want to do this:
SELECT * FROM bucket WHERE SUBSTR(sample.ref_id , 3) = 123 OR SUBSTR(sample.main_ref_id , 3) = 123 OR id = 123;


or
You could do this and do the queries in parrell.

SELECT * FROM cake 
UNION ALL
SELECT * FROM cake WHERE SUBSTR(sample.ref_id , 3) = 123 
UNION ALL
SELECT * FROM cake WHERE SUBSTR(sample.main_ref_id , 3) = 123 
UNION ALL
SELECT * FROM cake WHERE id = 123;


#3

thank you so much for the reply. it is not about the substring. please refer the below sample document structure. the document containing id=“123”, has ref_id:xyz and other two documents has “main_ref_id”:xyz.

I should be manage to fetch all the below records . when i give id =“123” in a where clause or so. and fetch all the documents which contains “main_ref_id”:xyz that is nothing but the ref_id of the document which has id=123

sample : {
“id”:123,
“ref_id”:xyz,
“name”:ttt
}

sample:{
“id”:456,
“ref_id”:abc,
“name”:ghg,
“main_ref_id”:xyz
}

sample:{
“id”:896,
“ref_id”: dfg,
“name”: ram,
“main_ref_id”:xyz
}


#4

At present Joins are done through document key only.


#5

@appy789,

So you want this IMAGE BELOW.

@vsr1 is correct you can not do that in N1QL , BUT you can do it in Couchbase Analytics SQL++ query.

Here is a link on JOINS with SQL++: https://developer.couchbase.com/documentation/server/4.5/analytics/3_query.html#story-h2-5

NOTE as the name implies Couchbase Analytics SQL++ is not to be designed to be as fast as N1QL but more expressive and functional. To do things like match and find deep relationships between documents or even inter-document.

BUT if you need lots of speed and if you can change your document design around. You can do JOINS in N1QL.

You could do this.

SELECT * FROM cake AS a 
JOIN cake AS b 
ON KEYS a.ref_id[*]


#6

FYI, This will be supported in N1QL also in next version of Couchbase-Server. MB-25757