Relational method for checking whether a path in a JSON exists

[roji]

All relational databases support some way of checking whether a given JSON PATH exists in a JSON document. We should add a relational-level EF.Functions.JsonExists (or similar) which the different providers can translate to this.

Database	Function
PG	jsonb_path_exists
SQL Server	JSON_PATH_EXISTS (since SQL Server 2022)
SQLite	json_type (returns NULL for non-existing path, as opposed to 'null' for JSON null values)
MariaDB	JSON_EXISTS
MySQL	JSON_CONTAINS_PATH
Oracle	JSON_EXISTS

[joelmandell]

Depending on what happens with this PR https://github.com/dotnet/efcore/pull/30010. I might continue with adding those as well...

[abcdefgabcefg]

Hi @roji as I see @joelmandell PR is stuck. I'd like to try to implement this by:

1. Create JsonExists in RelationalDbFunctionsExtensions that throws an exception
2. Create SqlServerJsonExistTranslator
3. Create SqliteJsonExistTranslator
4. Register translators at SqlServerMethodCallTranslatorProvider and SqlliteMethodCallTranslatorProvider accordingly I believe that will support JSON PATH for SQL Server and SQLite providers, but will throw an exception for any other relational provider unless their developers implement support for one. Can I do it?

[joelmandell]

@abcdefgabcefg I am just waiting for feedback on that PR before I started on the other things. That's why I am lagging behind

[roji]

@joelmandell oh sorry, I don't I was aware you were waiting for feedback there... we've unfortunately gone into feature freeze for 8.0 so we won't be able to merge it for that...

[abcdefgabcefg]

@roji please tell, this issue is also frozen till EF 8?

[roji]

EF 8 will be out in November, and feature work on it has stopped; we're now stabilizing/fixing bugs only. Any feature PR will generally be merged for EF 9 at this point, which will come out in November 2024.

[imangd]

Hi @roji , is this issue still open for contribution?

[roji]

@Ricketiman and others, @joelmandell made good progress on this in #30010 - it's my fault it hasn't progressed. I'm generally trying to make progress on dormant PRs, I'll try to pick up this work again - I don't think it makes sense to start a new PR.