Can we Store JSON data as varbinary in SQL server?

Question

Can we Store JSON data as varbinary in SQL server?

Dev_Fuel07 21

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?

Bruce (SqlWork.com) 78,236 Reputation points Volunteer Moderator

2022-04-04T15:33:20.16+00:00

the size constraints are the same for image and text data in sqlserver. the sql tools typically default to a max returned string/image size. you can update n setting.

note: azure data studio has better json support than sql management studio.

Accepted answer

1 additional answer

Your answer

Bruce (SqlWork.com) 78,236 Reputation points Volunteer Moderator

2022-04-04T15:33:20.16+00:00

the size constraints are the same for image and text data in sqlserver. the sql tools typically default to a max returned string/image size. you can update n setting.

note: azure data studio has better json support than sql management studio.

Answer 1

Olaf Helper 47,516

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.

Dev_Fuel07 21 Reputation points

2022-04-04T06:13:57.617+00:00

Ok, I will Debug the issue with nvarchar(max). Just to be sure, I am not missing any BLOB datatype in SQL server like BLOB type in mySQL or Oracle? Please let me know if there is BLOB type in MSSQL for storing large data? (Just for the knowledge and clarity) :)
Naomi Nosonovsky 8,431 Reputation points

2022-04-04T14:51:41.727+00:00

No, NVARCHAR(max) is the analogue of BLOB for characters.

Answer 2

Erland Sommarskog 122K MVP Volunteer Moderator

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

Share via

Can we Store JSON data as varbinary in SQL server?

1 additional answer

Your answer