Postgresql – json field not mapped correctly with custom projection

After reading "Entities or DTOs – When should you use which projection?" and "Why, When and How to Use DTO Projections with JPA and Hibernate" from Thorben Janssen, it is clear that Hibernate uses constructors in DTO projections, and the constructor must match the exact types being returned by the query.

Since Hibernate sees the JSON field as a generic Object, or perhaps another default type like String, depending on your Hibernate dialect, you need to make sure your FooProjection constructor can handle this.

Update the FooProjection to accept the JSON field as the type Hibernate is converting it to.
If it is String (common case), you would have something like:

@Getter
@Builder
@RequiredArgsConstructor
public class FooProjection {

  private final Long id;
  private final String configJson; // Changed to String assuming JSON is returned as a String

  // Constructor matching the query projection
  public FooProjection(Long id, String configJson) {
      this.id = id;
      this.config = Config.createFromJson(configJson); // Assuming you have a method to handle JSON parsing
  }
}

If Hibernate is not naturally converting the JSON to a String, you might need to explicitly cast or convert it within the query. That is dependent on your specific database dialect. For PostgreSQL, you could modify the query to cast JSON to text:

@Query(
  """
    SELECT new path.to.package.FooProjection(
      f.id,
      f.config::jsonb#>>'{}'  // Casts to JSONB and fetches the whole JSON object as text
    )
    FROM Foo f
    WHERE f.id = :fooId
  """)
Optional<FooProjection> fetchMinimalFoo(@Param("fooId") Long fooId);

That assumes that config can be represented entirely as a string.
If config includes nested objects, and you are trying to retrieve a specific value, you would need to adjust the path in the #>> operator accordingly.

Make sure you have a method in your Config class or related utility to convert a JSON string back to your Config object.
The JSON to Config object conversion should happen at the right place in your code:

• directly in the DTO’s constructor, as I previously described, or
• in a service method where you process data after retrieval but before use in the business logic or presentation layers.

import com.fasterxml.jackson.databind.ObjectMapper;
import com.fasterxml.jackson.core.JsonProcessingException;

public class JsonUtil {

    private static final ObjectMapper objectMapper = new ObjectMapper();

    public static Config createFromJson(String json) {
        try {
            return objectMapper.readValue(json, Config.class);
        } catch (JsonProcessingException e) {
            // Log the error or throw a custom exception as per your error handling policy
            System.err.println("Error parsing JSON to Config: " + e.getMessage());
            return null; // or throw, depending on how you want to handle parse errors
        }
    }
}

The FooProjection DTO must use this method correctly: if the config field in FooProjection is of type Config, you need to modify the constructor to parse the JSON string:

@Getter
@Builder
@RequiredArgsConstructor
public class FooProjection {

    private final Long id;
    private Config config;  // Not final, to allow setting after parsing

    public FooProjection(Long id, String configJson) {
        this.id = id;
        this.config = JsonUtil.createFromJson(configJson);  // Convert JSON string to Config
    }
}