My team has a table in postgres, let's call it Thing. It looks like this:
Thing Table:
| id | value | type |
|---|---|---|
| integer | jsonb | string |
Here are some sample records
| id | value | type |
|---|---|---|
| 1 | "hello" | string |
| 2 | 123 | number |
| 3 | { "name": "foo" } | object |
| 4 | [1, 5, 10, 15] | array |
| 5 | true | boolean |
| 6 | [2, 3, 7] | array |
Thing class
public class Thing
{
public int Id { get; set; }
public JsonElement Value { get; set; }
public string Type { get; set; }
}
We were hoping to construct an EF query that must be transpiled into SQL and invoked in Postgres.
Here is the query we were hoping to have work:
var results = context.Things.Where(thing => thing.type == "array" && thing.Value.EnumerateArray().Any(v => v.GetInt32() == 5)));
The result of this query would return record #4.
Unfortunately, the statement can't be transpiled and gives the following error:
(IEnumerable<JsonElement>)p1.Value.EnumerateArray() .Any(v => v.GetInt32() == 5))' could not be translated. Additional information: Translation of method 'System.Text.Json.JsonElement.EnumerateArray' failed.
One would think that EnumerateArray() would be transpiled properly to use the Postgres json_array_elements(json) function, but no such luck.
As previously stated, this query must be invoked on the database (client side filtering is not an option).
We are aware of the EF.Functions.JsonXXX methods, however, none seem to be able to work with json arrays of native elements (it seems that at the very least, the array needs to contain objects with properties).
That said, has anyone had to implement something similar to this without having to write raw sql and be willing to share?
We are really hoping to avoid having to write and maintain raw sql.
Thanks!
json_array_elements