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

Answer 1

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()
    )
)