Create json with column values as object keys

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

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

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