spring native query call a function that returns a json

Question

I am new to java and I am trying to call a function that returns a json object, However the java method returns a String.

Is there a way in postrgres to convert Json to VARCHAR, or in java to vonvert JSONObject to a json String

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


}

`    
@Service
public class CourseServices {
public JSONObject getEnrolledStudentsLight(int id){

        return repository.getStudents(id);
    }`
}

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

}

I tried toJSONString() but the out put is key:value String and I only want the values as string just like the out put from the database

create or replace  FUNCTION get_enrolled_courses (student_id int)
    RETURNS  varchar
    LANGUAGE plpgsql
As $$
DECLARE s_id INTEGER;
begin
    select student_id into s_id;
    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 = s_id
                 ) t);
end;
$$;

the output of the DB is : [ { "id": 14, "title": "Intoduction to computer scince", "credits": 9, "updated_at": "2023-05-05T20:56:58.160225", "created_at": "2023-05-05T20:56:58.160225" }, { "id": 18, "title": "Data Base 2", "credits": 12, "updated_at": "2023-05-05T20:57:20.064462", "created_at": "2023-05-05T20:57:20.064462" }, { "id": 19, "title": "Signal and Systems", "credits": 9, "updated_at": "2023-05-05T20:57:20.066494", "created_at": "2023-05-05T20:57:20.066494" } ]

any suggestions?

Could you elaborate on why exactly you need a string representation of students? — Viktor Reinok, May 15 '23 at 18:27
The idea is to send it back to the front end. What do you suggest — Obai Mohammad, May 16 '23 at 20:37

score 0 · Answer 1 · answered May 15 '23 at 18:27

I would try to create Entity aka. DBO for student instead of Returning JSONObject.
Use ObjectMapper that returns a string JSON representation.

ObjectMapper objectMapper = new ObjectMapper(); String studentsJSON = objectMapper.writeValueAsString(students);

spring native query call a function that returns a json

1 Answers1