Parse native json values from array stored jsonb column in postgres with Entity Framework .NET Core NpgSql

Question

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!

Answer 1

It seems like you're trying to query out rows whose JSON array value contains something. Rather than using json_array_elements and then trying to compose over that, you should be able to query directly whether a JSON document contains another JSON document:

SELECT * FROM things WHERE value @> '5';

The @> JSON can be expressed operator in EF LINQ queries via EF.Functions.JsonContains, as per the docs.

Note also that jsonb_typeof (in EF LINQ EF.Functions.JsonTypeof) can be used to query for the type of the json document (array, string...), so you may not need a separate "type" column. If you need that in order to index over it, you can make it a computed column with json_typeof.