4

I'm trying to solve a problem about JPA Specifications and use of JSONB array values.

Let's say we use Postgres and have a SQL entity like this

id character varying(255)
code character varying(255)
data jsonb

where in data there is a value of this type ["aaa","bbb","ccc"].

In my entity I have:

@Entity(name = "my_entity")
@TypeDefs({
        @TypeDef(name = "JsonDataType", typeClass = JsonDataType.class)
})
public class MyEntity {
    @Id
    private String id;

    @Column(name = "code")
    private String code;

    @Type(type = "JsonDataType")
    @Column(name = "data")
    private List<String> data;

    // GETTERS AND SETTERS

that is correctly serialized when inserting entity in DB.

It's easy to build a specification to query for code attribute, i.e.

public static Specification<MyEntity> searchByCode(String code) {
        return (root, criteriaQuery, criteriaBuilder) 
                       -> (code != null) 
                            ? criteriaBuilder.equal(root.<String>get("code"), code) 
                            : null;
}

Now I need to use Specification to query for a value into data json array, that is almost the same of a query like this: select * from my_entity where data ? 'aaa';

I cannot find any solution around inet. :(

Can someone give me a clue? Thanks in advance.

Luigi

Imhotep
  • 41
  • 3
  • While `jsonb` has fairly common support nowadays, I don't believe there are any abstractions when it comes to querying JSON columns. You'd need to invoke a function. Just be aware these are platform specific, so it won't be portable. See https://stackoverflow.com/questions/43900457/how-do-i-use-spring-data-jpa-to-query-jsonb-column – Christopher Schneider Dec 19 '19 at 17:04

0 Answers0