jquery – How to build JSON array with custom columns in postgres

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

Read more here: Source link