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
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;
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
}
At present Joins are done through document key only.
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++: Couchbase SDKs
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[*]
1 Like
FYI, This will be supported in N1QL also in next version of Couchbase-Server. MB-25757