Couchbase Join Failed to Return JSON

Question

Couchbase – Version 4.1.0-5005

Query Workbench Developer Review – Version 1.0.2

I am working on a process to determine if a Model exists for a Grid. I start by building the N1QL Statement section (listed below). This N1QL Statement determines the association of the Grids with the Model. Prior to generating this N1QL Statement, I ran individual statements to ensure the presented Model Id exist within both document types.

select a.model_id
  from inputs as a
       join inputs as b on keys a.model_id
where a.type = 'Model'
  and b.type = 'Grid'
  and a.model_id = 100

I modeled the N1QL Statement after the example provided by Couchbase beer-sample (listed below). In addition, I modified the Couchbase example to mirror the above.

select br.name brewery,
       b.name beer,
       b.style style
  from `beer-sample` b 
       join `beer-sample` br on keys b.brewery_id
 where b.type = 'beer'
   and br.type = 'brewery'
   and b.name = '21A IPA'

When I execute the Couchbase provided example, I receive a single JSON document. When I execute my N1QL Statement, it returns no JSON document. I verified that both types contain at least one JSON document for the model id specified. The bucket only contains a primary index. No other indexes exist.

I appreciate any suggestion in resolving this issue.

TIA

geraldss geraldss · Accepted Answer · 2016-07-14T13:56:09

The primary key (external id) of your Grid documents needs to be the same as the model_id of your Model document.

The N1QL syntax is not joining over a common field. It is joining an expression from the left hand term to the primary key of the right hand term.

Couchbase Join Failed to Return JSON

1 Answers