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?

score 19 · Accepted Answer · answered Aug 13 '15 at 13:21

19

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

answered Aug 13 '15 at 13:21

steevel

304
2
5

2

Seems like this really ought to be included on this page: https://www.postgresql.org/docs/9.6/static/functions-json.html but instead they include a tiny note about it being on this page: https://www.postgresql.org/docs/9.6/static/functions-aggregate.html – chrismarx Feb 06 '18 at 21:42
works beautifully well for 11.4. concise and perfect – mythicalcoder Jul 07 '19 at 09:39

Clodoaldo Neto · Answer 2 · 2014-07-15T12:07:12.817

6

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

edited Jul 15 '14 at 12:07

answered Jul 15 '14 at 11:58

Clodoaldo Neto

118,695
26
233
260

I had to change `'%s:%s'` to `'"%s":%s'` personally for this to work. But it works. Thanks! – Yanick Rochon Apr 20 '15 at 19:58

score 1 · Answer 3 · answered Oct 15 '20 at 15:11

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

Create json with column values as object keys

3 Answers3

Linked