How to join on non-indexed fields

Couchbase Server
1

I am trying to join two sets on non-indexed fields using the below query and facing issue!

select sm.vpmn, ir.networkid from
(select * from config where __t = “Alpha”) as sm
join
(select * from config where __t = “Beta”) as ir
on keys sm.vpmn=ir.networkid

but facing below issue-
“code”: 3000,
“msg”: “JOIN must be done on a keyspace. - at end of input \n Error while parsing: runtime error: invalid memory address or nil pointer dereference - at end of input”,

More info:- __t has a index in the config bucket.
Kindly suggest the possible solutions.

2

You can have subquery on LHS of Join and RHS must be bucket (no sub query).
You must have index on RHS as described in https://blog.couchbase.com/ansi-join-support-n1ql/.

If that is not possible and you are query produces limited data (Due to Cartesian JOIN) you can use UNNEST

select sm.vpmn, ir.networkid from
(select a.* from  `config`  AS a where  a,__t = “Alpha”) as sm
UNNEST
(select b.* from  `config`  AS b where b.__t = “Beta”) as ir
WHERE  sm.vpmn=ir.networkid;
3

I tried below but it doesn’t result the desired output. It seems it is not working for sm.vpmn=ir.networkid maybe because the modelling is different for Alpha and Beta document.

Kindly suggest any viable option to get the same result as for join.

4

Try updated query if not working post both documents and its keys

5

Updated query didn’t worked!
both documents are under same bucket config with different name ‘Alpha’ and ‘Beta’ under indexed column __t .
Both the document have different modelling.
Alpha Document Key:- “Alpha-hpmnType-vpmnType-hpmn-vpmn-nai-pref”
Beta Decument Key:- “Beta-networkid”

we need to join the both the document on vpmn/hpmn with networkid.

Kindly suggest!

6

The following works. Check what is issue with your data.

INSERT INTO default VALUES ("k01", {"__t" : "Alpha", "vpmn": "xyz1234"});
INSERT INTO default VALUES ("k02", {"__t" : "Beta", "networkid": "xyz1234"});

SELECT sm.vpmn, ir.networkid
FROM (SELECT a.* FROM  default AS a WHERE  a.__t = "Alpha") AS sm
UNNEST (SELECT b.* FROM  default AS b WHERE  b.__t = "Beta") AS ir
WHERE  sm.vpmn = ir.networkid;