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
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"}] |
