如何在PostgreSQL中实现pandas .to_dict('index')效果的JSON输出

实现方法 #

直接使用PostgreSQL提供的json_object_agg聚合函数，将每行生成的键值对合并为单个顶层JSON对象即可，最终SQL如下：

SELECT json_object_agg(
    id,
    json_build_object('name', name, 'value', value)
) AS target_json
FROM my_table;

如果更偏好性能更高、支持后续JSON操作的jsonb类型，可以替换为jsonb_object_agg：

SELECT jsonb_object_agg(
    id,
    jsonb_build_object('name', name, 'value', value)
) AS target_json
FROM my_table;

输出效果 #

针对你给出的示例数据，执行后返回结果完全匹配df.set_index('id').to_dict('index')的效果：

{
  "1": {"name": "joe", "value": 6},
  "2": {"name": "bob", "value": 3},
  "3": {"name": "joey", "value": 2}
}

注：JSON规范要求对象键必须为字符串，因此数字类型的id会自动转为字符串格式的键，属于正常现象。

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