PosgresSQL - sql to generate Json from multiple tables-CodePudding

Postgres V13

trying to understand how can i generate Json base relational data in PS(simple tables), Im writing a tool which generate SQL for generating Json based on relational data , The user provide mapping rules - which maps each Json field to the table.field and the tool build the SQL which provide the data in required json format .

for example for very similiar json I need to create on Ps, on Oracle DB(Oracle version >12.2) I get it by running:

SELECT JSON_OBJECT ('FIRST_NAME' VALUE TO_CHAR(CUSTOMER.FIRST_NAME),
'concatDeails' VALUE  JSON_OBJECT ('conctant_name' VALUE TO_CHAR(CUSTOMER.FIRST_NAME) || TO_CHAR(CUSTOMER.LAST_NAME)),
'Payments' VALUE (SELECT JSON_ARRAYAGG  ( JSON_OBJECT ('payment_id' VALUE PAYMENT.payment_id, 
'amount' VALUE payment.amount
)
 RETURNING VARCHAR2(32000) )
FROM PAYMENT WHERE PAYMENT.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID )format  json )  JSON_OUT 
FROM CUSTOMER

And there is one--> many relation between customer to payment tables, So for Postgres I have 3 tables: customer(address_id is uniq key) address (address_id is pk) payment (customer can multiple payments)

The json should look as:

{
  "first_name": "Jared", 
  "last_name": "Ely", 
  "concatDeails":{ "conctant_name": "Jared Ely"},
  "Address":{"city": "NY" ,"Zip": 123123},
  "Payments":[{"payment_id": 1,"amount":100 , "credit": null }]
 
}

I know how to create each block but from some reason , Not able to get all of them in single query .

for getting the first_name and concatDeails:

select json_build_object('first_name'  , customer.first_name ,'concatDeails' ,json_build_object('last_nam1e' , customer.last_name||customer.last_name)) 
                         from customer

Address dict:

select   
    json_build_object('address' ,json_build_object('city_id' , address.city_id, 'postal_code' ,address.postal_code) )
                         from address ;

payment list object:

select jsonb_build_array(json_build_object('payment_id'  , payment.payment_id ,'amount' , payment.amount ,"credit" , payment.credit)) from payment ;

When i try to combine them into single query , getting failed :

elect json_build_object('first_name'  , customer.first_name ,'concatDeails' ,json_build_object('last_nam1e' , customer.last_name||customer.last_name),
                            (select     json_build_object('address' ,json_build_object('city_id' , address.city_id, 'postal_code' ,address.postal_code) )
                             from address where address.address_id=customer.address_id),
                             (select jsonb_build_array(json_build_object('payment_id'  , payment.payment_id ,'amount' , payment.amount))
                             from payment where payment.customer_id=customer.customer_id)               
                             )
                             from customer;

ERROR:  more than one row returned by a subquery used as an expression
SQL state: 21000

Actually even if I try to combine address and customer I'm getting error: