C
C#•3y ago
oe

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)
11 Replies
Binto86
Binto86•3y ago
you probably want to desterilize your json into an object
oe
oeOP•3y ago
and then what?
Binto86
Binto86•3y ago
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
Tvde1•3y ago
at work we serialize it to protobuf so it's just some bytes pretty compact
oe
oeOP•3y ago
Can you then compress the protobuf output to make it even smaller?
rifftheraff
rifftheraff•3y ago
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')
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, ...?
Work with JSON data - SQL Server
JSON data in SQL Server
TECHCOMMUNITY.MICROSOFT.COM
Storing JSON in SQL Server
First published on MSDN on Nov 23, 2015 SQL Server offers built-in functions for processing JSON text data. In this post, we will see how you can store JSON in SQL Server database. Problem Various systems, service, loggers, format information in JSON format. This text should be stored in databas...
oe
oeOP•3y ago
@RiffTheRaff db space I achieved half the size using brotli and it works for json input and output Seeing if I can make that even less
Anton
Anton•3y ago
so is db space a major concern?
oe
oeOP•3y ago
@AntonC well I’ll be dealing with large json files soon so yeah
Anton
Anton•3y ago
you can just store the files too on disk
Tvde1
Tvde1•3y ago
it's already really small many times smaller than json

Did you find this page helpful?