Json: add ability to specify type of the json column

[maumar]

E.g. in postgres there are 2 types for json column: json and jsonb. We should allow specifying the type explicitly for each aggregate, once we land on final API

[shvmgpt116]

@ajcvickers @maumar
When can we expect this feature? Please could someone let me know.

[ajcvickers]

We hope to include it in EF8, but that will depend on prioritization against other features. Make sure to vote (👍) for this issue.

[rofenix2]

Sorry for reopen but does this mean that i cant change nvarchar(max) for json columns?. Cause that is exactly what i am trying to do according to Microsoft documentation to use nvarchar(4000) for performance reasons.

[roji]

@cdrfenix can you share some more context? Are you trying to store JSON documents in an nvarchar(4000) column (instead of max)? If so, why? Is it so that you can index the entire JSON document, and if so, how would you be using that index?

@shvmgpt116 some more information on why you need this would be appreciated.

[rofenix2]

No i wont index it, i am just trying to follow ms recommendation according to the documentation in here: https://learn.microsoft.com/en-us/sql/relational-databases/json/store-json-documents-in-sql-tables?view=sql-server-ver16

Even tho they tell you twice to use nvarchar(4000) "for performance reasons" it doesnt explain in detail why. So if the reason its only for using a index then i guess i dont have a problem and nvarchar(max) will just do it. Also this article could be relevant in relation to this issue (but not my use case):

https://bornsql.ca/blog/think-twice-about-storing-json-in-your-sql-server-database/

[roji]

Thanks for the links. I'm not enough of a SQL Server expert to know exactly how nvarchar(4000) vs. nvarchar(max) affect performance, but this comment does seem to point to that being important:

The nvarchar(max) data type lets you store JSON documents that are up to 2 GB in size. If you're sure that your JSON documents aren't greater than 8 KB, however, we recommend that you use NVARCHAR(4000) instead of NVARCHAR(max) for performance reasons.

Note that compression (and possibly even columnstore) is a bit orthogonal to this; but also important.

[roji]

Another need here is using varchar instead of nvarchar on SQL Server, especially for UTF8 (raised in #33643).