How to query jsonb column using spring data specification?

Question

I have below table temp_tbl (postgres):

    ID(int)     NAME(TEXT)    LINKS(jsonb)
    --------    ----------    -------------------
    1           Name1         ["1","23","3", "32]
    2           Name2         ["11","3","31", "2]
    3           Name3         ["21","13","3", "12]

Now my native query to get rows which has 'LINKS' with value as "3" is:

    select * from temp_tbl where links @> '["3"]'

returns rows 2 & 3.

I want implement this query using org.springframework.data.jpa.domain.Specification

I have implemented something like below where the jsonb column is not-an-array, but has key-value json using jsonb_extract_path_text. But the above column stores only values in an array.

My entity class.

@Entity
@Table(name = "temp_tbl")
public class TempTbl {
  @Id
  @Column(name = "ID")
  private Long id;

  @Column(name = "NAME", nullable = false)
  private String name;

  @Column(name = "LINKS", columnDefinition = "jsonb null")
  @Convert(converter = JsonbConverter.class)
  private List<String> linkIds;
}

I need help in translating above query into specification using criteriabuilder.

Answer 1

One way to check if a jsonb array contains a String using Jpa specifications is to use the functions jsonb_contains and jsonb_build_array, the latter of which is used to change the String into a jsonb array for use in jsonb_contains.

    public static Specification<TempTbl> linkInLinks(String linkId) {
        return (root, query, builder) -> {
            Expression toJsonbArray = builder.function("jsonb_build_array", String.class, builder.literal(linkId));
            return builder.equal(builder.function("jsonb_contains", String.class, root.get("linkIds"), toJsonbArray), true);
        };
    }