[Request] implement ISJSON check

[pietrodicaprio]

EntityFramework Core 7 (released Nov 2022) implements the support to JSON data soring/querying via Linq.
https://devblogs.microsoft.com/dotnet/announcing-ef7-release-candidate-2/

Microsoft suggests the use of the ISJSON CHECK to improve performance:
https://learn.microsoft.com/en-us/sql/relational-databases/json/optimize-json-processing-with-in-memory-oltp

EF does not seem to provide an option to enable the ISJSON check. Any plan to provide it in this extension?

[roji]

@pietrodicaprio I don't think adding an ISJSON check constraint would improve performance - it would only make sure that only JSON data is ever present in the column (and in fact would hurt performance by doing so). Can you point to where you saw that?

In any case, this plugin is just a shortcut for some common constraints - you can always define your own check constraints yourself.

[pietrodicaprio]

@pietrodicaprio I don't think adding an ISJSON check constraint would improve performance - it would only make sure that only JSON data is ever present in the column (and in fact would hurt performance by doing so). Can you point to where you saw that?

In any case, this plugin is just a shortcut for some common constraints - you can always define your own check constraints yourself.

I'm sorry, used wrong wording: I meant they suggest it to improve the processing by, indeed, enabling the check that ensures only JSON data is stored in the column (that is what I'm looking for)

[roji]

SQL Server: ISJSON
SQLite: json_valid
MySQL: JSON_VALID
PostgreSQL: not needed, since the column type (jsonb) already enforces JSON

[Xor-el]

SQL Server: ISJSON SQLite: json_valid MySQL: JSON_VALID PostgreSQL: not needed, since the column type (jsonb) already enforces JSON

Oracle: IS JSON

[molinch]

@roji I think what might be even more useful is to be able to constraint JSON data as any other data.
For example, you can have a PrimitiveCollection which gets serialized as a JSON array (on SQL Server), but unfortunately you won't receive checked constraints for items in the array.
Example: the collection gets serialized as a JSON array of genders ["male", "female", "other"]. Right now you cannot prevent having "dog" within it.

[roji]

@molinch this issue currently on tracks that a column contains valid JSON, not that the values inside a collection are in a specific valid list. How do you propoes we implement your proposal?

[molinch]

So if you have a PrimitiveCollection then the checked constraint could be as ISJSON(json_col, ARRAY) (see: https://learn.microsoft.com/en-us/sql/t-sql/functions/isjson-transact-sql?view=sql-server-ver16)

However that would still not validate the content of the array. It would just ensure we do have a JSON array.
In order to go that far I don't see any good solution except writing a user defined function and having a checked constraint calling it ... That seems way beyond what this library should do.

[roji]

@molinch yeah, I'm definitely aware of ISJSON - see the above comparison.