如何用JPA Criteria实现PostgreSQL的jsonb_array_elements_text查询？

I have a table named member, where the party column is of type jsonb. Here's an example of its structure:

{ "PE": [ "fefe046d-774d-4e8b-a74c-99c89e98a96f", "720bfde7-a8c0-404f-b746-d6929c9b1109", "409cc84a-a473-4945-9ec0-c09a2ae96395" ], "TE": [] }

I've written the following native SQL query:

select distinct id from public.member,jsonb_array_elements_text(party-> 'PE') where value in ('fefe046d-774d-4e8b-a74c-99c89e98a96f','409cc84a-a473-4945-9ec0-c09a2ae96395')

How can I achieve the same query logic using JPA Criteria? Please reply as soon as possible.

Solution #

Let's break down what your native SQL is doing first: it uses jsonb_array_elements_text to unnest the JSON array from party->'PE' into individual rows, filters those rows where the element is in your target set, then returns distinct ids from the original member table.

Here's how to replicate this with JPA Criteria, assuming you have a Member entity mapped to the member table (with the party column annotated as @Column(columnDefinition = "jsonb")):

import jakarta.persistence.criteria.*;
import org.hibernate.jpa.criteria.CriteriaBuilderImpl;
import org.hibernate.jpa.criteria.path.PathImpl;

import java.util.List;
import java.util.UUID;

public List<UUID> getMatchingMemberIds(List<UUID> targetPEIds) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<UUID> criteriaQuery = cb.createQuery(UUID.class);
    Root<Member> memberRoot = criteriaQuery.from(Member.class);

    // 1. Extract the 'PE' array from the party JSONB column using PostgreSQL function
    Expression<String> peArrayExpression = cb.function(
        "jsonb_extract_path_text",
        String.class,
        memberRoot.get("party"),
        cb.literal("PE")
    );

    // 2. Unnest the array into individual rows with jsonb_array_elements_text
    Expression<String> unnestExpression = cb.function(
        "jsonb_array_elements_text",
        String.class,
        peArrayExpression
    );

    // 3. Simulate the cross join with the unnested rows (Hibernate-specific handling)
    PathImpl<String> valuePath = (PathImpl<String>) ((CriteriaBuilderImpl) cb).createCrossJoin(unnestExpression);
    valuePath.setAlias("value");

    // 4. Add filter to match elements in the target ID list
    Predicate inPredicate = valuePath.in(targetPEIds);

    // 5. Build final query: select distinct IDs and apply filter
    criteriaQuery.select(memberRoot.get("id"))
                 .distinct(true)
                 .where(inPredicate);

    return entityManager.createQuery(criteriaQuery).getResultList();
}

Key Notes #

• We use cb.function() to call PostgreSQL-specific JSONB functions since JPA doesn't have built-in support for these operations.
• The cross join is handled via Hibernate's createCrossJoin because JPA Criteria doesn't have a direct way to work with table-valued functions like jsonb_array_elements_text.
• distinct(true) ensures we get unique ids, just like the DISTINCT keyword in your native SQL.

This implementation works with Hibernate as the JPA provider. If you're using a different provider, you might need to adjust how you handle the table-valued function, but the core logic remains the same.

内容的提问来源于stack exchange，提问作者kvkchaitanya