How to build JSON array with custom columns in postgres-CodePudding

I am trying to extract data from table in JSON format using postgres. In table i have columns lets say: water = 1, airport = 2, What i need is to add additional columns to final json object which should look like this:

"[
{"type": "water", "value": 1},
{"type": "airport", "value": 2},
]"

I have tried something like this:

SELECT array_to_json(array_agg(
     json_build_object('type', 'water', 'value', water)
 ))
 FROM  table;

but I am able to produce just an object for water... output looks like this:

"[{"type" : "port", "value" : 1}]"

Thanks

CodePudding user response：

somethings like this based on your code.

SELECT COALESCE(jsonb_agg( json_build_object('type', 'water', 'value', water))::jsonb ||
    jsonb_agg( json_build_object('type', 'water', 'value', airport) )::jsonb,
    jsonb_agg( json_build_object('type', 'water', 'value', water))::jsonb,
    jsonb_agg( json_build_object('type', 'water', 'value', airport)))
    AS data
FROM   table;