Use JSON_EXTRACT to get value from JSON in Spring JPA Custom Query for custom DTO

Question

I want to get value from the JSON column and return custom DTO in spring JPA.

Table structure

userId (int)
name (string)
street (string)
zipcode (string)
state (string)
country (string)
meta (JSON)

meta column contains age for example {"age": "45"}

I want to fetch a list of users having id, name, and age. As the data can be huge I created a custom DTO UserDataDto

Below is an example of the same:

@Query("SELECT new com.model.UserDataDto(userId, name, FUNCTION('JSON_EXTRACT', meta, '$.age')) " +
    "FROM User " +
    "WHERE userId IN (:userIds) ")
  List<UserDataDto> findUsersByIdIn(@Param("userIds") List<Long> userIds);

User Entity:

@Data
@Table(name = "user")
@Entity
public class User {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long userId;

  private String name;
  private String street;
  private String zipcode;
  private String state;
  private String country;

  @NotNull
  @Convert(converter = UserMetaConverter.class)
  private UserMeta meta;

}

UserMeta structure:

@Data
public class UserMeta {

  private Integer age;

}

UserDataDto structure:

public class UserDataDto {
  private Long userId;
  private String name;
  private Integer age;
}

On starting the spring boot application I'm getting

WARN | Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'userRepo': Invocation of init method failed; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List com.db.UserRepo.findUsersByIdIn(List<java.lang.Long>)!

The only solution that I can come up with is using a Native query, any other solution is highly appreciated.

Answer 1

The only solution I can come up with is using @NamedNativeQuery and @SqlResultSetMapping

in User Entity

@Data
@Table(name = "user")
@Entity
@NamedNativeQuery(
  name = "findUsersByIdIn",
  query = "SELECT userId, name, meta->>'$.age' as age " +
    "FROM user " +
    "WHERE user_id = :userIds",
  resultSetMapping = "UserDataDto"
)
@SqlResultSetMapping(
  name = "UserDataDto",
  classes = @ConstructorResult(
    targetClass = UserDataDto.class,
    columns = {
      @ColumnResult(name = "userId", type = Long.class),
      @ColumnResult(name = "name", type = String.class),
      @ColumnResult(name = "age", type = Integer.class)
    }
  )
)
public class User {
  @Id
  @GeneratedValue(strategy = GenerationType.IDENTITY)
  private Long id;

  private String name;
  private String street;
  private String zipcode;
  private String state;
  private String country;

  @NotNull
  @Convert(converter = UserMetaConverter.class)
  private UserMeta meta;

}

in Repository

public interface UserRepo extends JpaRepository<User, Long> {

  @Query("findUsersByIdIn", nativeQuery = true)
  List<UserDataDto> findUsersByIdIn(@Param("userIds") List<Long> userIds);
}