PostgreSQL 11: multiple key-value pairs with json_object_agg()

Question

Using PostgreSQL 11, the aggregate function json_object_agg() creates a JSON object from key and value, like the current uptime:

# SELECT json_object_agg('uptime', date_trunc('second',
  current_timestamp - pg_postmaster_start_time()));

This outputs the JSON object:

{ "uptime" : "00:45:55" }

How can I merge multiple key-value pairs into one single object? For instance, how to add the PostgreSQL version string to the object?

# SELECT json_object_agg('version', version());

The desired result may look like:

{
    "uptime": "00:60:01",
    "version": "PostgreSQL 11.7"
}

Cuga · Accepted Answer · 2021-10-28 21:32:39Z

8

You can use json_build_object() to generate a JSON object for key/value pairs.

json_object_agg(
    json_build_object(
        'uptime', date_trunc('second', current_timestamp - pg_postmaster_start_time()),
        'version', version()
    )
)

edited Oct 28, 2021 at 21:32

Cuga

17.9k32 gold badges115 silver badges169 bronze badges

answered Mar 16, 2020 at 19:04

GMB

224k25 gold badges103 silver badges151 bronze badges

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

2 Comments

laserbrain Over a year ago

The following query works:

SELECT json_build_object('uptime', date_trunc('second', current_timestamp - pg_postmaster_start_time()), 'version', version())

. Thank you.

Alex83690 Jan 28 at 14:26

JSON_AGG(JSON_BUILD_OBJECT( instead of JSON_OBJECT_AGG(JSON_BUILD_OBJECT(

Vladimir Dronov · Accepted Answer · 2020-08-04 15:53:05Z

7

In the case of an aggregated query, example:

select point_id, json_agg(
    json_build_object(
        'lat', lat,
        'lng', lon
        ))
from raw
group by point_id;

Result:

1,"[{"lat": 66.316917131, "lng": 65.308411872}, {"lat": 66.361430767, "lng": 65.218795224}]"
2,"[{"lat": 53.557419623, "lng": 102.39525849}, {"lat": 53.626151788, "lng": 102.529763433}]"
3,"[{"lat": 56.452206128, "lng": 100.731635684}, {"lat": 56.520627931, "lng": 100.771568911}]"

answered Aug 4, 2020 at 15:53

Vladimir Dronov

811 silver badge2 bronze badges

Comments

jjanes · Accepted Answer · 2020-03-16 20:06:37Z

json_object_agg is for aggregating, that is summarizing an arbitrary number of rows.

You can use it for your purpose by making it look like the data is coming in as multiple rows with 2 columns each. You can use a VALUES list for this purpose.

select jsonb_object_agg(key,val) from (
values 
    ('uptime',date_trunc('second', current_timestamp - pg_postmaster_start_time())::text),
    ('version',version())
) foo(key,val);

Using json(b)_build_object with 4 arguments and one implicit/dummy row is probably more natural for the exact case you give. That might be all you want to do at the moment, but if you are working with JSON you should know how json(b)_object_agg works even if it is not what you need right now.

Collectives™ on Stack Overflow

PostgreSQL 11: multiple key-value pairs with json_object_agg()

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related