PostgreSQL output to JSON object/array

Question

I'm running PostgreSQL and are trying to convert this:

SELECT car_id AS id, car_model AS model FROM cars

into this:

[{ "id" : 123, "model" : "Fiat" }, {"id": 124, "model": "BMW"}, ...]

I've tried:

SELECT json_agg(
           json_build_object(car_id AS id),
           json_build_object(car_model AS model)
       ) FROM cars

and a lot other stuff, but seems to be stuck

Answer 1

You can try to use only one json_build_object in json_agg function.

json_build_object explain from the document.

Builds a JSON object out of a variadic argument list. By convention, the argument list consists of alternating keys and values.

using json_build_object function parameter will be like

json_build_object([key1],[Key_Value1],[key2],[Key_Value2]..)

TestDLL

CREATE TABLE cars(
   car_id INT,
   Car_Model VARCHAR(50)
);

INSERT INTO cars VALUES (1,'TEST1');
INSERT INTO cars VALUES (2,'TEST2');

Query

SELECT json_agg(json_build_object('ID', car_id , 'Model', car_model ))
from cars

Result

|                                                         result |
|----------------------------------------------------------------|
| [{"ID" : 1, "Model" : "TEST1"}, {"ID" : 2, "Model" : "TEST2"}] |

sqlfiddle