Many to many relationship using N1ql couchbase query

Question

User bucket data structure

Docid : "user123456"
{
Name: utpal,
Phone: 98222333,
Age:30,
addressids:["add_123","add_1234"]

}

Address bucket

Docid : add_123
{
Name: behala,
Location: kolkata
}

Docid : add_123
{
Name: behala1,
Location: kolkata1
}

I want to Desire results

[{
Name: utpal,
Phone: 98222333,
Age:30,

addressids:["add_123","add_1234"],

address:[
{
Name: behala,
Location: kolkata
},
{
Name: behala1,
Location: kolkata1
}
]

Any one help me how to write a N1ql query for that result

Answer 1

INSERT INTO default VALUES("user123456" { "Name": "utpal", "Phone": "98222333", "Age":30, "addressids":["add_123","add_1234"], "type":"user"});
INSERT INTO default VALUES("add_123", { "Name": "behala", "Location": "kolkata" });
INSERT INTO default VALUES("add_1234", { "Name": "behala4", "Location": "kolkata4" });


SELECT u.*, address
FROM default AS u
LET address = (SELECT RAW a FROM default AS a USE KEYS u.addressids)
WHERE u.type = "user";

OR

SELECT u.*, address
FROM default AS u
NEST default AS address ON u.addressids
WHERE u.type = "user";