/*
{
"first_name": "Jared",
"last_name": "Ely",
"concatDeails":{ "conctant_name": "Jared Ely"},
"Address":{"city": "NY" ,"Zip": 123123},
"Payments":[{"payment_id": 1,"amount":100 , "credit": null }]
}
*/
create table customer (id int, first_name varchar, last_name varchar);
insert into customer values (1, 'Jared', 'Ely');
create table address (customer_id int, city varchar, Zip varchar);
insert into address values (1, 'NY', '123123');
create table payment (payment_id int, customer_id int, amount int, credit int);
insert into payment values (1, 1, 100, null), (2, 1, 50, 50);
select
json_build_object(
'first_name', customer.first_name ,
'concatDeails', json_build_object(
'conctant_name', customer.first_name || ' ' || customer.last_name
),
'Address', json_build_object(
'city', address.city,
'Zip', address.Zip
),
'Payments', payment
)
from customer
left join address on address.customer_id = customer.id
left join (
select
customer_id,
array_agg(json_build_object(
'payment_id', payment_id, 'amount', amount, 'credit', credit
))
from payment
group by customer_id
) payment on payment.customer_id = customer.id;