4 ms·
At work, we're using sql server, and i stored all json as base64 string though.
by revskill 2y ago
At work, we're using sql server, and i stored all json as base64 string though.
- dontdoxxme 2y agoSubmitted it to The Daily WTF yet?
- revskill 2y agoWhy ??
- mcny 2y agoHow do you query json with SQL server like let's say you have one data point like this { "id": 42, "quantity": 12, bla bla bla And you want rows where this column has quantity and quantity ≥ 20 How do you do it if you encode everything as base 64?
- isoprophlex 2y agoYou slap a full text index on the base64 string. There's only a finite number of base64 substrings for the un-encoded substrings "id", 42, etcetera, so you first filter on those. Then you decode those full strings into json and do the final filtering application side. Easy!
- 6510 2y ago<joking>have col names id, quantity, json and greaterthan20
- thih9 2y agoThis is only a joke until a manager hears it. Then it’s part of the Q1 roadmap and we will refactor it in Q3.
- eterm 2y agoOthers are being mean by not explaining the joke. Firstly, SQL server has a built-in JSON type, which lets you query and manipulate the JSON directly: https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/relational-databases/j... Secondly, JSON is already serialized, so it doesn't make sense to store as a base64 string. You're adding 30% data overhead to transform a string into a string. Base64 is useful for serializing opaque binary formats. Lastly, some people might be getting a wry smile that you have the power of a relational database but are just trying to store "json" rather than an actual relational model.
- szundi 2y agoThis needs some explanation
- revskill 2y agoI'm still figuring out why i do this.
- revendell_elf 2y ago[dead]
- p2detar 2y agoNot sure what your exact use case is, I'm curious actually, but storing JSON strings should work much better. JSON functions are supported since SQL Server 2016 [0]. This is how I do it atm. I store only indexible content in table columns and everything else goes into an `attributes` JSON column. MSSQL supports indexes even on JSON fields, but I have not tried that, yet. 0 - https://learn.microsoft.com/en-us/sql/relational-databases/json/json-data-sql-server?view=sql-server-ver16 https://learn.microsoft.com/en-us/sql/relational-databases/j...