FilterExpressionBuilder with "in" clause

[civgio]

Hi everyone,
I try to use the org.springframework.ai.vectorstore.filter.FilterExpressionBuilder to create a little bit complex where clause in my pgvector database, but the sql built contains a grammar error.
I have a problem with in clause. I try to use this method of openAI library to add the in clause:

    public Op in(String key, List<Object> values) {
        return new Op(new Filter.Expression(ExpressionType.IN, new Filter.Key(key), new Filter.Value(values)));
    }

but I believe that it will not called because my method is catch by this method of openAI library:

    public Op in(String key, Object... values) {
        return this.in(key, List.of(values));
    }

So, my list is interpreted as Object and not as List, and the SpringAI library create a List of my List, returned a grammar error on the sql built.
Anyone have my same error?

[civgio]

I have found the solution. I must cast the List in List

[civgio]

After the fixing, the sql doesn't work still.

Looks like the IN clause doesn't works for WHERE clause with the operator jsob @@ jsonpath (see the postgres documentation to understand this operator).

My code is the follow:

FilterExpressionBuilder b = new FilterExpressionBuilder();
List<String> fruits= getFruits();
b.in("FOOD_NAME", (List) fruits));
SearchRequest searchRequest = SearchRequest.query("apple")
                                                   .withFilterExpression(b.build())
                                                   .withTopK(5);

the where clause in output is look like this:

WHERE metadata::jsonb @@ '$.FOOD_NAME in ["ananas","apple","banana",...]'::jsonpath

but the execution of this query return the follow error:

Errore SQL [42601]: ERROR: syntax error at or near " " of jsonpath input

If I try to use the == operator combined with ||, it works fine:

WHERE metadata::jsonb @@ '$.FOOD_NAME == "ananas" || $.FOOD_NAME == "apple" || $.FOOD_NAME == "banana" || ...'::jsonpath

or using the like_regex filter:

WHERE metadata::jsonb @@ '$.FOOD_NAME like_regex "ananas|apple|banana|..."'::jsonpath

Can you chech this and introduce the like_regex method for FilterExpressionBuilder?

[adhawan325]

@civgio - I am having the same problem. Can you share how you are using the == operator combined with || with the Open AI library?

[civgio]

Sorry @adhawan325 for the delay, I don't see the notification.

I found this solution:

FilterExpressionBuilder b = new FilterExpressionBuilder();
FilterExpressionBuilder.Op op;
List<String> fruits = getFruits();
for (String fruit : fruits) {
    if (op == null) {
        op = b.eq("FOOD_NAME", fruit);
    }
    else {
        op = b.or(op, b.eq("FOOD_NAME", fruit));
    }
}

SearchRequest searchRequest = SearchRequest.query("apple")
                                                     .withFilterExpression(op.build())
                                                     .withTopK(5);

I hope to help you!