sql, javax.persistence.criteria: check a database table column with comma separated string values ag-CodePudding

I have a database table column with comma separated string values like this:

id mycolumn
-- --------
1  A
2  A, B
3  A, B, C

I can't change this, because it's an old customizer database.

I want to check mycolumn against a list of strings:

first query against the list ['A'] should be return the record 1

second query against the list ['A','B'] should be return records 1,2

third query against the list ['A','B','C'] should be return records 1,2,3

I need a sql (postgres) statement and further a java implementation with javax.persistence.criteria.CriteriaBuilder and javax.persistence.criteria.Predicate.

Thank you very much for your hints, Ann

edit: Here my solution for the javax.persitence part:

public static void arrayContains(final CriteriaBuilder cb, final List<Predicate> where,
            final Expression<String> expression, final String values) {
        Expression<String> expressionValues = cb.function("string_to_array", String.class, cb.literal(values), cb.literal(", "));
        Expression<String> expressionDb = cb.function("string_to_array", String.class, expression, cb.literal(", "));
        where.add(cb.isTrue(cb.function("arraycontains", Boolean.class, expressionValues, expressionDb)));
    }

CodePudding user response：

In PostgreSQL use string_to_array(mycolumn,',') in the WHERE clause with the containment operator <@, e.g.

SELECT * FROM t
WHERE string_to_array(mycolumn,',') <@ ARRAY['A','B','C']

Keep in mind that you have to create an index that correspond to this condition, in case you're dealing with a large table, e.g. a GIN index.

CREATE INDEX idx_t_mycolumn_gin ON t USING gin (string_to_array(mycolumn,','));

Demo: db<>fiddle