Join Two documents

SQL++
,
1

Suppose I have documents
Account :{
account_id : 1,
account_name : "kontu "}
Payment:{
id: 1,
account_id :1,
amount :2 },
Payment :{
id: 2,
account_id :1,
amount :23}

How to write query so that i can get data
given below:
Account :{
account_id : 1,
account_name : "kontu’
Payments :[
Payment:{
id: 1,
account_id :1,
amount :2 },
Payment :{
id: 2,
account_id :1,
amount :23}
]
}

2

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

SELECT  a.*, p AS Payments
FROM default AS a 
NEST default AS p ON a.account_id = p. account_id
WHERE a.type = "accounts";

OR

SELECT  a.*, ARRAY_AGG(p) AS Payments
FROM default AS a 
JOIN default AS p ON a.account_id = p. account_id
WHERE a.type = "accounts"
GROUP BY a;
3

Hi vsr,
Can you explain why you have a predicate for a.type=‘accounts’, but not p.type = ‘payments’?

4

If it JOIN query you can add it to WHERE or ON clause.

If it is NEST you can add that only to ON clause, NOT to WHERE clause, due to nature of NEST, which is explained in Example 17 referenced above