Create json with column values ​​as object keys

Question

I have a table defined as follows:

CREATE TABLE data_table AS ( id bigserial, "name" text NOT NULL, "value" text NOT NULL, CONSTRAINT data_table_pk PRIMARY KEY (id) ); INSERT INTO data_table ("name", "value") VALUES ('key_1', 'value_1'), ('key_2', 'value_2'); 

I would like to get a JSON object from this table content, which would look like this:

 { "key_1":"value_1", "key_2":"value_2" } 

Now I am using a client application to analyze a result set in JSON format. Is it possible to accomplish this using a postgresl request?

Answer 1

If you are in 9.4, you can do the following:

 $ select json_object_agg("name", "value") from data_table; json_object_agg ---------------------------------------------- { "key_1" : "value_1", "key_2" : "value_2" } 

Answer 2

 select format( '{%s}', string_agg(format( '%s:%s', to_json("name"), to_json("value") ), ',') )::json as json_object from data_table; json_object --------------------------------------- {"key_1":"value_1","key_2":"value_2"}