Create json with column values as object keys

Question

I have a table defined like this:

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 will look like this:

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

Now I'm using the client application to parse the result set into JSON format. Is it possible to accomplish this by a postgresl query?

Answer 1

In a generic scenario you can nest more than one json_object_agg functions on top of a subquery. The inner subqueries should always have at least one column that will be used by outer subquery as keys for the json_object_agg function.

In the example, in the subquery C the values of the column action are used as keys in the subquery A. In A the values of column role are used as keys in query A.

-- query A
select json_object_agg(q1.role, q1.actions) from (
-- subquery B
    select q2.role, json_object_agg(q2.action, q2.permissions) as actions from (
-- subquery C
        select r.name as role, a.name as action, json_build_object (
            'enabled', coalesce(a.bit & bit_and(p.actionids) <> 0, false), 
            'guestUnsupported', r.name = 'guest' and a."guestUnsupported"
        ) as permissions
        from role r
        left join action a on a.entity = 'route'
        left join permission p on p.roleid = r.id 
          and a.entity = p.entityname 
          and (p.entityid = 1 or p.entityid is null)
        where
        1 = 1
        and r.enabled
        and r.deleted is null
        group by r.name, a.id
    ) as q2 group by q2.role
) as q1

The result is a single row/single column with the following content:

{
    "Role 1": {
        "APIPUT": {
            "enabled": false,
            "guestUnsupported": false
        },
        "APIDELETE": {
            "enabled": false,
            "guestUnsupported": false
        },
        "APIGET": {
            "enabled": true,
            "guestUnsupported": false
        },
        "APIPOST": {
            "enabled": true,
            "guestUnsupported": false
        }
    },
    "Role 2": {
        "APIPUT": {
            "enabled": false,
            "guestUnsupported": false
        },
        "APIDELETE": {
            "enabled": false,
            "guestUnsupported": false
        },
        "APIGET": {
            "enabled": true,
            "guestUnsupported": false
        },
        "APIPOST": {
            "enabled": false,
            "guestUnsupported": false
        }
    }
}

Answer 2

If you're on 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" }