C#
C#

help

Root Question Message

oe119
oe1191/23/2023
Compressing JSON data for SQL server, best way?

Hi, What's a good way to approach compressing big JSON data and storing it in SQL server? (libraries etc)
Binto86
Binto861/23/2023
you probably want to desterilize your json into an object
oe119
oe1191/23/2023
and then what?
Binto86
Binto861/23/2023
depends if you want to use something like dapper or efcore, or if you just feed the data by hand into some sql client
Tvde1
Tvde11/23/2023
at work we serialize it to protobuf
Tvde1
Tvde11/23/2023
so it's just some bytes
Tvde1
Tvde11/23/2023
pretty compact
oe119
oe1191/23/2023
Can you then compress the protobuf output to make it even smaller?
RiffTheRaff
RiffTheRaff1/24/2023
I would try not compress the JSON data. Insert it as it is as JSON. SQL server has built in JSON support to query that data. If you compress the data before inserting it, SQL server is not able to query information out of the JSON data structure. It can only retrieve the full compressed blob.
This fully depends on your requirements.
Example (from https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver16):
SELECT Name, Surname,
  JSON_VALUE(jsonCol, '$.info.address.PostCode') AS PostCode,
  JSON_VALUE(jsonCol, '$.info.address."Address Line 1"') + ' '
  + JSON_VALUE(jsonCol, '$.info.address."Address Line 2"') AS Address,
  JSON_QUERY(jsonCol, '$.info.skills') AS Skills
FROM People
WHERE ISJSON(jsonCol) > 0
  AND JSON_VALUE(jsonCol, '$.info.address.Town') = 'Belgrade'
  AND Status = 'Active'
ORDER BY JSON_VALUE(jsonCol, '$.info.address.PostCode')


SQL server also has build in support for compress/decompress. Check article https://techcommunity.microsoft.com/t5/sql-server-blog/storing-json-in-sql-server/ba-p/384576
What is your requirement for compression? Faster transport, DB space, ...?
oe119
oe1191/24/2023
@844594574906228736 db space
oe119
oe1191/24/2023
I achieved half the size using brotli and it works for json input and output
oe119
oe1191/24/2023
Seeing if I can make that even less
AntonC
AntonC1/24/2023
so is db space a major concern?
oe119
oe1191/24/2023
@296666311688454144 well I’ll be dealing with large json files soon so yeah
AntonC
AntonC1/24/2023
you can just store the files too
AntonC
AntonC1/24/2023
on disk
Tvde1
Tvde11/24/2023
it's already really small
Tvde1
Tvde11/24/2023
many times smaller than json
ContactFrequently Asked QuestionsJoin The DiscordBugs & Feature RequestsTerms & Privacy