Support for PostgreSQL 12 jsonpath

[roji]

See https://paquier.xyz/postgresql-2/postgres-12-jsonpath

See upcoming jsonpath support in System.Text.Json: dotnet/runtime#31068

[zakeryooo]

@roji can I ask if this is still potentially planned for 5.0? Is this one of the features that would need to be implemented to query inside jsonb arrays? i.e:

[Column(TypeName = "jsonb")]
public List<AnotherClass> Examples { get; set; }

ctx.Items.Where(i => i.Examples.Any(e => e.Capacity >  100))

I'm building something that will need to search inside quite a large number of jsonb lists so just wondering if this feature is on the horizon or if I should go down the less ideal route of splitting each list into their own table and using normal joins to query the data instead. Thanks!

[roji]

@zakeryooo there are many translation possibilities with jsonpath, so this could up a pretty big task done incrementally, over several releases. Given where we are and all the other work, I wouldn't depend on jsonpath being implemented for 5.0.

However, you shouldn't hesitate to use EF Core's raw SQL capability to express jsonpath queries yourself, and possibly compose additional SQL operations over that (plus EF Core will materialize your queries). This can be a very effective way to get the exact results you want, and when jsonpath support comes along you can replace those raw queries.

[roji]

Punting this to work in v7 along with the rest of the JSON support in EF.

[atrauzzi]

Just chiming in to note that any search capabilities would be awesome if they also had some way to present jsonpath support as well as embedded expression checks.

Things like where json_column @@ '$.*.*.id !== null and similar. Especially where any functionality would be able to leverage GIN indexes.

[roji]

Yeah. Some jsonpath improvements are likely coming to System.Text.Json in 7.0, hopefully we can map those APIs to PostgreSQL jsonpath queries. Otherwise we can roll our own custom APIs for translation, though that would be less nice.

[roji]

Duplicate of #2570