获取nativeQuery with table join result list into

Question

我正在使用 postgress 开发一个 spring boot 项目。我在将 jpa 本机查询（使用表连接）结果列表解析为 JSONObject 时遇到问题我有两张桌子create table person(  id           serial primary key,  name        varchar(50));create table contacts(  id serial primary key,  person_id int,  key   varchar(50),  value varchar(100),  foreign key (person_id) references person (id));表格中的示例数据insert into person values (1, 'A');insert into person values (2, 'B');insert into contacts values (1, 1, 'EMAIL', '[e-mail]');insert into contacts values (2, 1, 'PHONE', '123');insert into contacts values (3, 2, 'EMAIL', '[e-mail]');insert into contacts values (4, 2, 'PHONE', '456');使用本机查询获取数据的 Spring 引导代码。String query = "select p.id, p.name, c.key, c.value from person p LEFT JOIN contacts c on p.id = c.person_id";Query nativeQuery = entityManager.createNativeQuery(query);List<Object[]> resultList = nativeQuery.getResultList();我想将输出作为 JSONObject 的列表[{        "id": 1,        "name": "A",        "contacts": [{                "key": "EMAIL",                "value": "[e-mail]"            },            {                "key": "PHONE",                "value": "123"            }        ]    },    {        "id": 2,        "name": "B",        "contacts": [{                "key": "EMAIL",                "value": "[e-mail]"            },            {                "key": "PHONE",                "value": "456"            }        ]    }]或者结果应该直接进入 POJOList<Person> resultList = nativeQuery.getResultList();在这种情况下，我有一个带有 @OneToMany 注释的人员类和联系人类。

Answer 1

演示：db<>fiddle（本机查询）

SELECT 

    json_agg(json_person)

FROM

    person p

JOIN (

    SELECT

        person_id,

        json_agg(json_contacts) AS contacts

    FROM

        contacts,

        json_build_object('key', key, 'value', value) as json_contacts

    GROUP BY person_id

) c

ON c.person_id = p.id,

json_build_object('id', p.id, 'name', p.name, 'contacts', c.contacts) AS json_person

文档：Postgres JSON 函数

您必须使用创建一个 JSON 对象json_build_object()并将这些结果聚合到一个数组中json_agg()。两次。

免责声明：如评论中所述：在这种情况下，两个表的简单 JOIN 就可以将逻辑传输到 Java 后端。我猜，因为您使用的是 JPA，所以您只有实体管理器中的数据。因此，使用 Java 工具（例如gson）生成 JSON 对象将是比调用数据库更好的选择。但这当然取决于您的用例和设置。