获取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"

            }

        ]

    }

]

或者结果应该直接进入 POJO

List<Person> resultList = nativeQuery.getResultList();

在这种情况下，我有一个带有 @OneToMany 注释的人员类和联系人类。

Answer 1

演示：db<>fiddle（本机查询）SELECT     json_agg(json_person)FROM    person pJOIN (    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) cON 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 对象将是比调用数据库更好的选择。但这当然取决于您的用例和设置。