Filter integer array of jsonb column in postgres using EF Core and NpgSql

Question

I want to filter rows based on json array of integers, for example, my table looks like this

| Id  | Name       | TypeJson |
| --- | ---------- | -------- |
| 1   | Name One   | [1,2]    |
| 2   | Name Two   | [2,3]    |
| 3   | Name Three | [4,7]    |

where Id is type of int, Name - text and TypeJson - jsonb.

I'm trying to filter the rows by following query

await _dbContect.Table
.Where(x => !string.IsNullOrEmpty(x.TypeJson) && 
            EF.Functions.JsonExistAny(x.TypeJson, "2")
.ToListAsync();

I expect to get 1 and 2 rows as a result but I'm getting unexpected error

Npgsql.PostgresException (0x80004005): 42883: function btrim(jsonb, unknown) does not exist
...
  Exception data:
    Severity: ERROR
    SqlState: 42883
    MessageText: function btrim(jsonb, unknown) does not exist
    Hint: No function matches the given name and argument types. You might need to add explicit type casts.
    Position: 593
    File: parse_func.c
    Line: 528
    Routine: ParseFuncOrColumn

am I supposed to pass "2" in some specific format or what is the exception thrown by?

Answer 1

Posted this problem on npgsql github and as expected that's a problem on my side, as the column is jsonb type string functions like IsNullOrEmpty() and IsNullOrWhiteSpace() can't be applied on it, moreover it's not even needed as EF json functions will filter null values as false anyways.

Link to the issue on github