python - Select into subdict 从多个表

Question

我有一个这样的数据库结构:

CREATE TABLE person (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER NOT NULL,
    hometown_id INTEGER REFERENCES town(id)
);

CREATE TABLE town (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    population INTEGER NOT NULL
);

我想在选择时得到如下结果:

{
    "name": "<person.name>",
    "age": "<person.age>"
    "hometown": {
        "name": "<tometown.name>",
        "population": "<tometown.population>"
    }
}

我已经在使用 psycopg2.extras.DictCursor ，所以我认为我需要使用 SQL 的 SELECT AS。

这是我尝试过但没有结果的一个例子，我做了很多类似的小调整，所有这些都引发了不同的错误:

SELECT
    person(name, age),
    town(name, population) as town,
FROM person
JOIN town ON town.id = person.hometown_id

有什么方法可以做到这一点，还是我应该单独选择所有列并在 Python 内部构建字典？

Postgres 版本信息:

psql (9.4.6, server 9.5.2)
WARNING: psql major version 9.4, server major version 9.5.
         Some psql features might not work.

Answer 1

像什么？

t=# with t as (
select to_json(town),* from town
)
select json_build_object('name',p.name,'age',age,'hometown',to_json) "NameItAsYou Wish"
from person p
join t on t.id=p.hometown_id
;
                                NameItAsYou Wish
--------------------------------------------------------------------------------
 {"name" : "a", "age" : 23, "hometown" : {"id":1,"name":"tn","population":100}}
(1 row)