Can we Store JSON data as varbinary in SQL server?

Question

I am only creating a staging table to temporary work with JSON data in memory, therefore avoiding it to store in a column per field manner. Instead I would like to store that variable json values in SQL DB entity. I am stuck how to store it?

I am using .Net Framework - C# MVC - EntityFramework 6.2

By Default Entity Framework will generate nvarchar(max) type for JSON string but I have observed that nvarchar(max) will not retrieve all data only a limited chunk of it and that will lead to JSON deserialization failure in memory.

I want to avoid this and store large JSON data as varbinary, JSON object will have 200 fields (key value pairs) some of them might hold more than 500 alphanumeric characters.

How to set datatype for such scenario from entity framework code first approach, Any suggestions?

Answer 1

Sure you can store JSON as varbinary; but that don't make much sense. JSON is string and so nvarchar(max) is the better choice.
All JSON functions in T-SQL work with nvarchar, not with binary, see https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver15

but I have observed that nvarchar(max) will not retrieve all data only a limited chunk of it

Then you are doing something wrong, it works for me and all others.

Answer 2

If you store the data as nvarchar(MAX) there should be any problem in retrieving it. But I seem to recall that there is an issue if you use FOR JSON PATH. Then you need to retrieve the JSON with a stream reader, of which I do not recall the details.

But you can work around this by saying:

SET @json = (SELECT ... FOR JSON PATH)
SELECT @json AS MyJson