SQLize Online / PHPize Online

/* { "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;