RIGHT JOIN Support?

Couchbase Server
1

I am using Version: 4.5.0-2601 Enterprise Edition (build-2601)
Does N1QL support RIGHT JOIN?
my doc as following:

{"id":"p1","type":"parent","name":"parent1"};
{"id":"p2","type":"parent","name":"parent2"};
{"id":"p3","type":"parent","name":"parent3"};
{"id":"c1","type":"child","name":"child1","parent_id":"p1"};
{"id":"c2","type":"child","name":"child2","parent_id":"p1"};
{"id":"c3","type":"child","name":"child3","parent_id":"p1"};
{"id":"c4","type":"child","name":"child4","parent_id":"p2"};
{"id":"c5","type":"child","name":"child5","parent_id":"p2"};

I want to get the following data:

[
  {
	"id":"p1"
	"type":"parent"
	"name":"parent1"
	"chieldlist":[
		{
			"id":"c1"
			"type":"child"
			"name":"child1"
		},
		{
			"id":"c2"
			"type":"child"
			"name":"child2"
		},
		{
			"id":"c3"
			"type":"child"
			"name":"child3"
		}
	]
  },
  {
	"id":"p2"
	"type":"parent"
	"name":"parent1"
	"chieldlist":[
		{
			"id":"c4"
			"type":"child"
			"name":"child4"
		},
		{
			"id":"c5"
			"type":"child"
			"name":"child5"
		}
	]
  },
  {
	"id":"p3"
	"type":"parent"
	"name":"parent1"
	"chieldlist":[]
  }
]
2

N1QL does not support RIGHT JOIN. Usually, you can rewrite the query using LEFT JOIN.

3

For this example, How should I using LEFT JOIN to query data that I want?

4

For your example.

CREATE INDEX idx_parent ON mydata( parent_id );

SELECT *
FROM mydata parent
LEFT NEST mydata children ON KEY children.parent_id FOR parent;
5

Thank you,The following N1QL works:

SELECT meta(parent).id,parent.name,parent.type,array_agg(child) as childlist
FROM mydata parent
LEFT NEST mydata child ON KEY child.parent_id FOR parent 
WHERE parent.type == "parent" 
group by meta(parent).id,parent.name,parent.type;
6

@geraldss

I want to query the following data:

[
  {
	"id":"p1"
	"type":"parent"
	"name":"parent1"
	"chieldlist":[
		{
			"id":"c1"
			"type":"child"
			"name":"child1"
		},
		{
			"id":"c2"
			"type":"child"
			"name":"child2"
		},
		{
			"id":"c3"
			"type":"child"
			"name":"child3"
		}
	]
  },
  {
	"id":"p2"
	"type":"parent"
	"name":"parent1"
	"chieldlist":[
		{
			"id":"c4"
			"type":"child"
			"name":"child4"
		},
		{
			"id":"c5"
			"type":"child"
			"name":"child5"
		}
	]
  },
  {
	"id":"p3"
	"type":"parent"
	"name":"parent1"
	"chieldlist":[]
  }
]

but the N1QL

SELECT meta(parent).id,parent.name,parent.type,
array_agg(
{
"id":meta(child).id,
"type":child.type,
"name":child.name
}
) as childlist
FROM mydb parent
LEFT NEST mydb child ON KEY child.parent_id FOR parent 
WHERE parent.type == "parent" 
group by meta(parent).id,parent.name,parent.type;

return the following data:

[
  {
    "childlist": [
      {}
    ],
    "id": "p1",
    "name": "parent1",
    "type": "parent"
  },
  {
    "childlist": [
      {}
    ],
    "id": "p2",
    "name": "parent2",
    "type": "parent"
  },
  {
    "childlist": [
      {}
    ],
    "id": "p3",
    "name": "parent3",
    "type": "parent"
  }
]
7

Here you go.

CREATE INDEX idx_parent ON mydata( parent_id );

SELECT parent.*, children
FROM mydata parent
LEFT NEST mydata children ON KEY children.parent_id FOR parent;
8

No,I means that I want to only query child.name and child.type ,not child.*
or even I want to query some fields of child by object_remove() function. but it looks like not work.

9

Ok. Use the ARRAY operator to perform your array mapping.

CREATE INDEX idx_parent ON mydata( parent_id );

SELECT parent.*, ARRAY { "name": c.name, ... } FOR c IN children END as children
FROM mydata parent
LEFT NEST mydata children ON KEY children.parent_id FOR parent;
1 Like
10

Thank you,It works! Can I use object_remove function?

11

No changes. It works :slight_smile:

12

Thank you,The Following N1QL using object_remove function works:

SELECT parent.*,
ARRAY  object_remove(c,"parent_id") FOR c IN child END as childlist
FROM mydb parent
LEFT NEST mydb child ON KEY child.parent_id FOR parent 
WHERE parent.type == "parent"
1 Like