C#•2y ago

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•2y ago

you probably want to desterilize your json into an object

oeOP•2y ago

and then what?

Binto86•2y 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•2y ago

at work we serialize it to protobuf so it's just some bytes pretty compact

oeOP•2y ago

Can you then compress the protobuf output to make it even smaller?

rifftheraff•2y 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...

oeOP•2y 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•2y ago

so is db space a major concern?

oeOP•2y ago

@AntonC well I’ll be dealing with large json files soon so yeah

Anton•2y ago

you can just store the files too on disk

Tvde1•2y ago

it's already really small many times smaller than json

Gaming

Programming

Compressing JSON data for SQL server, best way?