json_agg() with dynamic column names

Question

I'm trying to get the result from the table as JSON using a snippet like this.

SELECT json_agg(json_build_object('latitude',my_table.latitude,
                                  'longitude',my_table.longitude
))  from my_table;

Result:

[
  { "latitude": -24.719103042657146, "longitude": 16.97771468195161 }
]

Unfortunately, hard-coding the column names will not work since the column names are dynamic. I'm not sure how to achieve the same result without knowing exactly what the column names will be.

I've figured out that I can get the column names by doing this, but further than this I'm not too sure where to go.

SELECT column_name 
from information_schema.columns 
WHERE table_name = 'my_table';

Is my approach wrong or is there a simpler way?

Answer 1

You can convert a complete row to a JSON value using to_jsonb which would use the column names as the JSON keys:

select jsonb_agg(to_jsonb(t))
from (
   select latitude, longitude
   from my_table
) t