如果PostgresSQL查询返回json，那么如何在SpringBoot中访问它呢？

Question

一个db家伙在PostgreSQL上编写了以下查询。它将返回此json：

[{"id":8,"totalDays":429,"daysRemaining":-257,"absent":[3,3,2,3,3,3,3,3,3,3,3,3],"present":[0,0,1,0,0,0,0,0,0,0,0,0],"courseBatch":"java"}]

我需要创建一个具有这些id的模型类，并将这个json值插入到该模型类中，plz是否有人可以帮助。。。如果它返回我可以处理的结果集，但它返回的是json，那么请给我一个合适的代码来克服朋友和成员。提前谢谢

查询：

@Query(value = "WITH coursedetails AS(
SELECT 
        csb.CourseId,
        csb.batchid,
        csb.startdate ,
        csb.EndDate,
        gs AS Month,
        uc.coursename
    FROM CourseScheduledBatches csb  
    cross join generate_series(1,12,1)gs
    JOIN UserCourses uc on 
        uc.courseid = csb.courseid
        AND uc.userid = 54 AND uc.coursename='java'
),
finaldata AS(
    SELECT 
        cd.CourseId,
        cd.EndDate::date - cd.StartDate::date AS totaldays,
        cd.EndDate::date - CURRENT_DATE AS daysRemaining,
        cd.Month,
        count(DISTINCT ca.TraineeId) AS TraineesPresent,
        count(DISTINCT tc.TraineeId) AS totalTrainees,
        cd.coursename
    FROM coursedetails cd 
    LEFT JOIN CourseAttendence ca on 
        ca.courseid = cd.courseid
        AND ca.batchid = cd.batchid
        AND cd.Month = extract(MONTH FROM ca.StartDate)
    LEFT JOIN TraineeCourses tc ON 
        tc.courseid = cd.courseid
        AND tc.batchid = cd.batchid
    GROUP BY 1, 2, 3, 4, 7
    ORDER BY 4
)
SELECT json_agg(row_to_json(q.*))
FROM(
    SELECT 
        courseid AS "id",
        totaldays AS "totalDays",
        daysRemaining AS "daysRemaining",
        ARRAY_AGG(totalTrainees - TraineesPresent) AS "absent",
        ARRAY_AGG(TraineesPresent) AS "present",
        coursename AS "courseBatch"
    FROM finaldata        
    group by 1, 2, 3, 6
)q;",nativeQuery = true)
    List<Object> getAttendanceAvgBasedOnUserIdAndRolename(BigInteger userId, String batchName);

Answer 1

你可以做：

Gson gson = new Gson(); 
MyType target2 = gson.fromJson(json, MyType.class);

编写类，使其具有与json字符串匹配的属性。使用数组表示缺席和出席