PostgreSQL中如何直接以JSON类型值构造键值对JSON对象？

现有一个经JSON聚合得到的视图（表结构），包含user_id列（作为键）和user_data_json列（JSON类型值），示例数据如下：

+-----------+------------------------------------------------------------+
| user_id   | user_data_json                                             |
+-----------+------------------------------------------------------------+
| daveb1985 | {lastlogin:1732605022730, posts_read:[18, 23, 45], etc...} |
| trish2003 | {lastlogin:1732604033135, posts_read:[101], etc...}        |
| q2342rte  | {lastlogin:1731302284832, posts_read:[], etc...}           |
+-----------+------------------------------------------------------------+

期望生成如下嵌套JSON对象：

{
    "daveb1985": {
        "lastlogin": 1732605022730,
        "posts_read": [18, 23, 45]
    },
    "trish2003": {
        "lastlogin": 1732604033135,
        "posts_read": [101]
    },
    "q2342rte": {
        "lastlogin": 1731302284832,
        "posts_read": []
    }
}

尝试使用json_object ( keys text[], values text[] ) → json结合array_agg实现，但该函数要求值为text类型；将JSON类型值转为text后，得到的是字符串而非JSON对象，客户端处理繁琐。请问是否有直接构造目标JSON对象的方法？

直接使用PostgreSQL的json_object_agg(key, value)函数即可实现，这个函数专门用于键值对的JSON对象聚合，支持值为JSON类型，无需转成文本。

基础实现SQL语句 #

假设视图名为user_data_view，执行以下查询：

SELECT json_object_agg(user_id, user_data_json) AS result_json
FROM user_data_view;

精准控制字段的实现 #

如果需要只保留user_data_json中的特定字段（比如仅保留lastlogin和posts_read），可以先通过json_build_object提取字段后再聚合：

SELECT json_object_agg(
    user_id,
    json_build_object(
        'lastlogin', (user_data_json->>'lastlogin')::bigint,
        'posts_read', user_data_json->'posts_read'
    )
) AS result_json
FROM user_data_view;

该语句会将lastlogin转为数值类型，同时保留posts_read的数组结构，最终生成完全符合预期的嵌套JSON对象。

内容的提问来源于stack exchange，提问作者pancake