SQL join query (joined field as a json object)

Question

I'm using PostgreSQL and I'm using Nodejs and PG package. I have these tables:

request_tbl:

 req_id |  customer_id  | details              
----+--------------------------------------
  1     |    1          | something
  

customers_tbl:

 cust_id |  full_name | age             
----+--------------------------------------
  1      |  tarik hh  | 23
    

I need a query that gives this output after converting it to json.

It's a left join but I need the joined columns as an Object.

  [
    {
       "req_id":"1" , 
       "details":"something" ,
       "customer":{
                    "cust_id":"1",
                    "full_name":"tarik hh"
                  }
    }
      
  ]

Answer 1

Use postgresql's built in json_build_object function and create your own function like so:

CREATE FUNCTION get_req_w_cust(
  req_id_in int, 
  out results json
) AS $$
BEGIN
  SELECT json_build_object(
    'req_id', request_tbl.req_id,
    'details', request_tbl.details,
    'customer', json_build_object(
      'cust_id', customers_tbl.cust_id,
      'full_name', customers_tbl.full_name
    )
  ) INTO results
  FROM request_tbl, customers_tbl
    WHERE request_tbl.customer_id = customers_tbl.cust_id
    AND request_tbl.req_id = req_id_in
END
$$ language plpgsql;

Then you can access it from your code with

let {rows: [{results}] = await pg.query('select get_req_w_cust($1) AS results',[id]);

Alternatively just use the query without wrapping it in a function.