Perform a join on two different result sets in N1QL

SQL++
,
1

I have two documents like this

{
	"entities": [{
			"id": "id1",
			"name": "foo",
			"desc": "some desc"
		},
		{
			"id": "id2",
			"name": "foo",
			"desc": "some desc"
		},
		{
			"id": "id2",
			"name": "bar",
			"desc": "some desc"
		}
	]
}

{
	"entityRelations": [{
			"lhsId": "id1",
			"rhsId": "x1"
		},
		{
			"lhsId": "id2",
			"rhsId": "x2"
		}
	]
}

I would want to query entities matching the name property and find all its relations.

Ex: Find all rhsId for entities with name = “foo”
The expected response is {“x1”, “x2”}

2

In 5.5 use ANSI JOIN https://blog.couchbase.com/ansi-join-support-n1ql/

Pre 5.5

SELECT  ARRYA_FLATTEN(ARRAY   (ARRAY u1.rhsId FOR u1 IN d2.entityRelations  WHEN u1. lhsId = v1.id END)  FOR v1 IN d. entities WHEN v1.name = "foo" END,2) 

 FROM default AS  d USE KEYS "doc1"
 LET d2 = (SELECT d2.* FROM default AS d2 USE KEYS "doc2")[0]
3

Thanks for the quick response.
The problem is a little more complex, which I should have been more clear on.

The join I want to perform on is on two different result sets obtained from two different queries. So the documents (1)entities and (2) entityRelations are a result of some other N1QL query(See rephrased question below)

I have 3 entity documents and 2 relationship documents
Entities:

{
  "accountId": 2,
  "id": "id1",
  "name": "foo",
  "desc": "some desc for id1"
}

{
  "accountId": 2,
  "id": "id2",
  "name": "foo",
  "desc": "some desc for id2"
}

{
  "accountId": 2,
  "id": "id3",
  "name": "bar",
  "desc": "some desc for id3"
}

Relationships:

{
  "type1" : {
    "lhsId": "id1",
    "rhsId": "x1"
  }
  "type2" :{
    "lhsId": "id1",
    "rhsId": "y1"
  }
}

{
  "type1" : {
    "lhsId": "id2",
    "rhsId": "x2"
  }
}

What I want to achieve is filter all entities with name=“foo” and then find all its related entity ids for a specific type(say ‘type1’ in this case )

4 
SELECT  
      ARRYA_FLATTEN(ARRAY 
           (ARRAY u1.rhsId FOR u1 IN entityRelations  WHEN u1. lhsId = v1.id END) 
           FOR v1 IN entities WHEN v1.name = "foo" END,2) 
LET entityRelations  = (Sub query),
        entities = (SUB query);
5

Thanks @vsr1
This helps.

One question though. Does this mean two separate queries are performed by couchbase and the result sets are later merged.
Or does couchbase optimise it by using the results of the entities sub query in the entityRelations subquery.

6

It is single query and has subqueries. subquries are optimized separately. Optimization done at prepare time and results are available at execution time, so results are not used for optimization.

If you need that client needs to separate the queries provide the result has query named/positional parameters.

7

Is there a way I can use the result from the above query you gave, in the USE KEYS to fetch other documents.

Basically once we have the rhsId, i want to pick corresponding entity and other fields from it.

8

include whole query in USE KEYS.

SELECT * FROM bucket USE KEYS (SUBQUERY) …;

If you have already have you can project
ARRYA_FLATTEN(ARRAY
(ARRAY {u1.rhsId, v1.id, v1.name,…} FOR u1 IN entityRelations WHEN u1. lhsId = v1.id END)
FOR v1 IN entities WHEN v1.name = “foo” END,2)