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?

steevel · Accepted Answer · 2015-08-13 13:21:13Z

22

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, 2015 at 13:21

steevel

3342 silver badges5 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

chrismarx Over a year ago

Seems like this really ought to be included on this page: postgresql.org/docs/9.6/static/functions-json.html but instead they include a tiny note about it being on this page: postgresql.org/docs/9.6/static/functions-aggregate.html

mythicalcoder Over a year ago

works beautifully well for 11.4. concise and perfect

Clodoaldo Neto · Accepted Answer · 2014-07-15 12:07:12Z

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, 2014 at 12:07

answered Jul 15, 2014 at 11:58

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

1 Comment

Yanick Rochon Over a year ago

I had to change '%s:%s' to '"%s":%s' personally for this to work. But it works. Thanks!

sandro · Accepted Answer · 2020-10-15 15:11:28Z

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

Collectives™ on Stack Overflow

Create json with column values as object keys

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related