JPQL how to search on JSON Map by KEY name or VALUE

Question

Lets assume i have next entity, and the assignee is a Map<String, Object>

@Getter
@Setter
@Entity(name = "Permission")
public class Permission {
    
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    // It's an enum of whatever values like (TEAM, USER, ..etc)
    private PermissionType type;

    @Type(type = "jsonb")
    @Column(name = "assignee", columnDefinition = "jsonb", nullable = false)
    private Map<String, Object> assignee;

}

How can i search or select permissions by assignee key or value using JPQL?

Is that possible?

Answer 1

You will need a special SQLFunction implementation which you can then invoke from JPQL. Blaze-Persistence, a fluent query builder library that works on top of JPA, provides such functions out of the box which work for various databases: https://persistence.blazebit.com/documentation/1.6/core/manual/en_US/#json_get

The usage would look like the following:

FROM Permission p WHERE JSON_GET(p.assignee, 'jsonKey') = 'someValue'