json - row_to_json select columns from different table in postgresql

Question

I have 3 tables tbl_cph, tbl_woodland, tbl_energy with columns farm_code (tbl_cph), tco2e (tbl_woodland), total_co2e (tbl_energy). I want to select the columns from all 3 tables as properties using row_to_json. I am using this query:

SELECT row_to_json(f) As feature  
FROM (SELECT 'Feature' As type,  
             ST_AsGeoJSON(lp.geom)::json As geometry,  
             row_to_json((SELECT lp FROM (SELECT farm_code As FarmCode) As lp )) As properties  
      FROM cph As lp) As f

But here I am only retrieving the farm_code column value from table tbl_cph as properties. So how to retrieve all 3 columns value in json format from multiple tables.

Any help will be highly appreciated.

Thank You!
Rutuparna Panda

question from:https://stackoverflow.com/questions/65952150/row-to-json-select-columns-from-different-table-in-postgresql

Answer 1

Waitting for answers