SELECT t2.s, ARRAY_AGG({t2.Payments, t2.a}) AS Accounts
FROM ( SELECT t1.s, t1.a, ARRAY_AGG§ AS Payments
FROM (SELECT s, a, p
FROM catalog AS s
JOIN catalog AS a ON s.uuid = a.b2b_reseller_uuid
JOIN catalog AS p ON a.uuid = p.account_uuid
WHERE s.table_type = “b2b_reseller” AND a.table_type = “b2b_account”
AND p.table_type = “b2b_payment_transaction” AND (s.status = “pending_verification” OR s.status = "active"OR s.status = “pending_debtor_id” ) AND ( a.payment_status = “payment_completed” OR a.payment_status = “admin_approved”) AND s.uuid IS NOT NULL) AS t1
GROUP BY t1.s, t1.a) AS t2
GROUP BY t2.s;
with index 
CREATE INDEX idx_account ON catalog(uuid,table_type,payment_status,b2b_reseller_uuid,country,taxrate) WHERE (table_type = “b2b_account”)
CREATE INDEX idx_payment ON catalog(uuid,table_type,account_uuid,payment) WHERE (table_type = “b2b_payment_transaction”)
CREATE INDEX idx_reseller ON catalog(uuid,b2b_portal_uuid,(type.account_emails),address,step_count,status,tax_proof_status,address_proof_status,payment_status,debtor_id,vat_number,name,vat_country_code,phone_number,company_name,tax_number,business_contact_name,updatedAt DESC,createdAt DESC) WHERE (table_type = “b2b_reseller”)
this query takes average 50s…how to make faster this query .
Check out Optimize N1QL Performance Using Request Profiling topic in https://blog.couchbase.com/n1ql-practical-guide-second-edition/ .