search

dotnet / efcore

EF Core is a modern object-database mapper for .NET. It supports LINQ queries, change tracking, updates, and schema migrations.
https://docs.microsoft.com/ef/
MIT License
13.68k stars 3.16k forks source link

Relational method for checking whether a path in a JSON exists #31136

Open roji opened 1 year ago

roji commented 1 year ago

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 commented 1 year ago

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

abcdefgabcefg commented 1 year ago

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 commented 1 year ago

@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 commented 1 year ago

@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 commented 1 year ago

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

roji commented 1 year ago

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 commented 3 months ago

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

roji commented 3 months ago

@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.