N1QL Three Document join

SQL++
1

I have three documents type For example
Seller:{ seller id:1,seller_name :“kk”}
Account :{
account_id : 1,
seller id:1,
account_name : "kontu "}
Payment:{
id: 1,
seller id:1,
account_id :1,
amount :2 },
Payment :{
id: 2,
seller id:1,
account_id :1,
amount :23}
How to write query so that i can get data given below
{
seller id:1,
seller_name :“kk”,
Accounts :[{
account_id : 1,
account_name : "kontu’
Payments :[
Payment:{
id: 1,
account_id :1,
amount :2 },
Payment :{
id: 2,
account_id :1,
amount :23}
]]
}
}

I need to make my query for fast response

2

Use ANSI JOIN with GROUP BY or
ANSI NEST

https://blog.couchbase.com/ansi-join-support-n1ql/

CREATE INDEX ix1 ON default(seller_id) WHERE type = "Seller";
CREATE INDEX ix1 ON default(seller_id) WHERE type = "Account";
CREATE INDEX ix1 ON default(account_id) WHERE type = "Payment";

SELECT t2.s.seller_id, t2.s.seller_name, ARRAY_AGG({t2.Payments, t2.a.account_id, t2.a.account_name}) AS Accounts
FROM ( SELECT t1.s, t1.a, ARRAY_AGG({p.id,p.account_id,p.amount}} AS Payments
       FROM (SELECT  s, a, p
             FROM default AS s
             JOIN default AS a ON s.seller_id = a.seller_id
             JOIN default AS p  ON a.account_id = p.account_id
             WHERE s.type = "Seller" AND a.type = "Account"
                   AND p.type = "Payment" AND s.seller_id IS NOT NULL) AS t1
       GROUP BY t1.s, t1.a) AS t2
GROUP BY t2.s;
3

but this query is too much slow

4

You are doing multiple joins/aggregations . You can checkout Optimize N1QL Performance Using Request Profiling topic in https://blog.couchbase.com/n1ql-practical-guide-second-edition/ .

Also try with EE version.