How check exist element in postgresql jsonb column with jpa criteria?

Question

I have an entity as follow:

@Entity
@Data
@AllArgsConstructor
@NoArgsConstructor
@TypeDef(name = "JsonbType", typeClass = JsonBinaryType.class)
public class Message {
    @Id
    @GeneratedValue
    private Long id;

    private String content;

    @Column(columnDefinition = "jsonb")
    @Type(type = "JsonbType")
    private readTimes UserTimeSet;
}

public class UserTimeSet extend HashSet<UserTime> {
}

public class UserTime implement Serializable {
     private String username;
     private Date time;
}

Some records are as follow:

 id |                 read_times                 
----+--------------------------------------
  1 | [{"username": "user1", "time": 12312412}, {"username": "user2", "time": 123}]
  2 | [{"username": "user2", "time": 713}]
  3 | []
  4 | []

For saving object to column with Hibernate, I use Hibernate Types project.Now I want to get records there is no user1 in read_times with JPA criteria? the response must be records with id: 2, 3, 4.

Update: I solved with query but can't convert to JPA Criteria:

SELECT * FROM message WHERE jsonb_path_exists("read_times", '$[*] ? (@.username == "user1")')

score 0 · Answer 1 · answered May 12 '21 at 05:02

If using EclipseLink, REGEXP is supported, and will work on Postgres,

You can also use the SQL function to call any SQL specific syntax inside JPQL

An example for Predicates are as follows: Predicate inJsonNumbers = cb .function("jsonb_extract_path_text", String.class, root.get("json"), cb.literal("number")) .in(filter.getJsonNumbers())

Predicate inJsonNames = cb
        .function("jsonb_extract_path_text", 
                String.class, 
                root.get("json"), 
                cb.literal("name"))
        .in(filter.getJsonNames())

ALso, you can write a custom SQLFunction for the CAST(jsonField->'key' AS float) expression and then use that in JPQL.

public String render(Type firstArgumentType, List args, SessionFactoryImplementor factory) throws QueryException {
    return "CAST(" + args.get(0).toString() + "->'" + args.get(1).toString() + "' AS float)";
}

score 0 · Answer 2 · answered May 12 '21 at 08:22

0

Try this

with temp AS( select t.id, jsonb_array_elements(t.read_times)->>'username' as username from table_Name t )

select id from table_name where id not in ( select id from temp where
username='user1' )

answered May 12 '21 at 08:22

Mitko Keckaroski

954
1
8
12

I want to query with Criteria – Morteza Malvandi May 12 '21 at 08:29

How check exist element in postgresql jsonb column with jpa criteria?

2 Answers2