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?

Working Pickle · Accepted Answer · 2022-12-07 14:41:18Z

0

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

answered Dec 7, 2022 at 14:41

Working Pickle

1522 gold badges4 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related