Spring Native Query调用PostgreSQL JSON返回函数的适配问题

问题根源 #

你的代码存在两个核心问题：

1. PostgreSQL函数返回类型为varchar，但实际生成的是JSON数据，存在不必要的类型隐式转换
2. Java层用JSONObject接收结果后调用toJSONString()，导致输出多了一层引号/转义，和原生JSON格式不一致

解决方案1：优化PostgreSQL函数（推荐） #

将函数返回类型改为json，直接返回原生JSON数据，避免类型转换：

create or replace FUNCTION get_enrolled_courses (student_id int)
    RETURNS json  -- 替换原varchar为json类型
    LANGUAGE plpgsql
As $$
begin
    -- 无需额外声明变量，直接使用入参
    return (select COALESCE(array_to_json(array_agg(row_to_json(t)) ),'[]'::json)
            from (
                     select c.id,c.title,c.credits,c.updated_at,c.created_at
                     from course_student cs join courses c on c.id = cs.course_id
                     where cs.student_id = student_id
                 ) t);
end;
$$;

解决方案2：调整Java代码，匹配原生JSON输出 #

方法A：直接返回原生JSON字符串

修改Repository层直接返回String，跳过JSONObject的额外包装：

@Repository
public interface CourseRepository extends JpaRepository<Course, Long> {
    @Query(value = "select get_enrolled_courses(:id);", nativeQuery = true)
    String getStudents(@Param("id") int courseId);
}

Service层同步调整返回类型：

@Service
public class CourseServices {
    public String getEnrolledStudentsLight(int id){
        return repository.getStudents(id);
    }
}

Controller层直接返回字符串，Spring会自动识别为JSON响应：

@RestController
@RequestMapping("/api/v2/courses")
public class CourseController {
    @GetMapping("/{courseId}/students/light")
    public String getEnrolledStudentsLight (@PathVariable int courseId ){
        return service.getEnrolledStudentsLight(courseId);
    }
}

方法B：映射为Java对象列表（更优雅）

如果需要业务处理Java对象，可直接将JSON数组映射为List<Course>：

1. 确保项目依赖Jackson（Maven示例）：

<dependency>
    <groupId>com.fasterxml.jackson.core</groupId>
    <artifactId>jackson-databind</artifactId>
</dependency>

2. 修改Repository层返回List<Course>：

@Repository
public interface CourseRepository extends JpaRepository<Course, Long> {
    @Query(value = "select get_enrolled_courses(:id);", nativeQuery = true)
    List<Course> getStudents(@Param("id") int courseId);
}

3. 同步调整Service和Controller：

@Service
public class CourseServices {
    public List<Course> getEnrolledStudentsLight(int id){
        return repository.getStudents(id);
    }
}

@RestController
@RequestMapping("/api/v2/courses")
public class CourseController {
    @GetMapping("/{courseId}/students/light")
    public List<Course> getEnrolledStudentsLight (@PathVariable int courseId ){
        return service.getEnrolledStudentsLight(courseId);
    }
}

这种方式Spring会自动将对象序列化为标准JSON数组，和数据库输出格式完全一致，同时支持后续业务逻辑处理。

内容的提问来源于stack exchange，提问作者Obai Mohammad