JpaSort.unsafe fails to sort Jsonb fields [DATAJPA-1701]

[spring-projects-issues]

C.Kandaurova opened DATAJPA-1701 and commented

Hi,

I'm using Postgres with a table, containing JSONB field.

I want to sort & filter via fields in jsonb object.

 

Here is working Postgres query

 

SELECT * FROM my_object order by description->>'en-us'

 

Here is a sample test for Spring repository:

 

 

@Entity
@Table(name = "my_object")
@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)
public class MyObject {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
	
    @Type(type = "jsonb")
    @Column(name = "description")
    private Map<String, String> description;

    @Column(name = "sample")
    private String sample;
	
}

@Repository
public interface MyObjectRepository extends PagingAndSortingRepository<MyObject, Long> {
}

****
@Test
public void pagingTest() {	
	MyObject entry = new MyObject();
	Map<String, String> descr = new HashMap<>();
	descr.put("en-us", "sorcerer's stone");
	descr.put("en-gb", "philosopher's stone");
	entry.setDescription(descr);
	repository.save(entry);
	//some other test data
	
	Pageable p = PageRequest.of(1,10,
			JpaSort.unsafe(Sort.Direction.ASC,"description->>'en-us'"));
	Iterable<MyObject> all = repository.findAll(p);
	assert all != null;
}

 

 

Test fails with the following logs:

 

org.springframework.data.mapping.PropertyReferenceException: No property description->>'en-us' found for type MyObject!	at org.springframework.data.mapping.PropertyPath.<init>(PropertyPath.java:94)
	at org.springframework.data.mapping.PropertyPath.create(PropertyPath.java:382)
	at org.springframework.data.mapping.PropertyPath.create(PropertyPath.java:358)
	at org.springframework.data.mapping.PropertyPath.lambda$from$0(PropertyPath.java:311)
	at java.util.concurrent.ConcurrentMap.computeIfAbsent(ConcurrentMap.java:324)
	at org.springframework.data.mapping.PropertyPath.from(PropertyPath.java:293)
	at org.springframework.data.mapping.PropertyPath.from(PropertyPath.java:276)
	at org.springframework.data.jpa.repository.query.QueryUtils.toJpaOrder(QueryUtils.java:606)
	at org.springframework.data.jpa.repository.query.QueryUtils.toOrders(QueryUtils.java:559)
	at org.springframework.data.jpa.repository.support.SimpleJpaRepository.getQuery(SimpleJpaRepository.java:697)
	at org.springframework.data.jpa.repository.support.SimpleJpaRepository.getQuery(SimpleJpaRepository.java:651)
	at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:443)
	at org.springframework.data.jpa.repository.support.SimpleJpaRepository.findAll(SimpleJpaRepository.java:410)

 

 

Issue on stackoverflow https://stackoverflow.com/questions/45358010/spring-jpa-sorting-and-paging-with-postgresql-jsonb hints that this solution has been working some releases ago.

 

Other tests with JpaSort.unsafe also fail, for example:

 

Pageable p = PageRequest.of(1,10, JpaSort.unsafe(Sort.Direction.ASC,"LENGTH(sample)")); 
Iterable<MyObject> all = repository.findAll(p);

 

Is it still possible to query ignoring propertyType safety?

 

 

---

Affects: 2.2.5 (Moore SR5)

[xiaohulu]

2.7.5 still have this bug!

[gregturn]

#3172 duplicates this issue.

[abirami-els]

I'm facing the same issue. I'm trying to sort by JSONB column, but it's not working.

Pageable p = PageRequest.of(1,19, JpaSort.unsafe(Sort.Direction.ASC, "publicationSchedule->>'permissionDeadline'"));

[abirami-els]

Any fix for this issue?