[Request] implement ISJSON check about efcore.checkconstraints HOT 8 OPEN

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

Oracle: IS JSON

from efcore.checkconstraints.

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

from efcore.checkconstraints.

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

from efcore.checkconstraints.

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

from efcore.checkconstraints.

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

from efcore.checkconstraints.

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

from efcore.checkconstraints.

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

from efcore.checkconstraints.

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.

from efcore.checkconstraints.