5 ms·
Is there any downside to storing JSON-B even if you’re not planning to query it? For example, size on disk, read/write performance?
by blowski 3y ago
Is there any downside to storing JSON-B even if you’re not planning to query it? For example, size on disk, read/write performance?
- jamesfinlayson 3y agoIf the order of items in the JSON blob matters then JSONB probably wouldn't preserve the order.
- mike_d 3y agoJSON is unordered. Nothing in your code should assume otherwise. "An object is an unordered collection of zero or more name/value pairs, where a name is a string and a value is a string, number, boolean, null, object, or array."
- hakunin 3y agoThat’s exactly the kind of difference between json and jsonb that you gotta keep in mind. Object properties are unordered, but a json string is very much ordered. It’s the same sequence of characters and lines each time, unless you parse it and dump it again. So if you want to preserve an unmodified original json string for some (e.g. cosmetic) reasons, you probably want json.
- rowanG077 3y agoI would expect you are threading on dangerous grounds to assume a type called JSON is going to preserve the data byte for byte. It might currently but I doubt that is in the API contract. You really want to use TEXT if that is your requirement
- cztomsik 3y agoI don't know what SQLite does, but in JS the order is actually defined. JS is not Java, object is not HashMap, if anything, it's closer to LinkedHashMap, but even that is not correct because there are numeric slots which always go first. https://tc39.es/ecma262/#sec-ordinaryownpropertykeys https://tc39.es/ecma262/#sec-ordinaryownpropertykeys
- dspillett 3y ago> but in JS the order is actually defined Defined yes, but still arbitrary and may not be consistent between JSON values of the same schema, as per the document you linked to: > in ascending chronological order of property creation Also, while JSON came from JS sort-of, it is, for better or worse (better than XML!) a standard apart from JS with its own definitions and used in many other contexts. JSON as specified does not have a prescribed order for properties, so it is not safe to assume one. JS may generally impose a particular order, but other things may not when [re]creating a JSON string from their internal format (JSONB in this case), so by assuming a particular order will be preserved you would be relying on a behaviour that is undefined in the context of JSON.
- cztomsik 3y agoYes, I know, the point was not to say that it's safe to depend on this universally, but rather why it's safe in JS and why it's not elsewhere -> other languages use hash maps simply because authors were either lazy or unaware of the original behaviour. (which sucks, in my opinion, but nobody can fix it now)
- ElectricalUnion 3y ago> other languages use hash maps simply because authors were either lazy or unaware Because it is idiomatic in that language, and you often don't need the higher overhead of tracking insertion order. Before Python 3.7, json.loads used dict (undefined order) and you needed to explicitly need to override the load calls with the kwarg `object_pairs_hook=collections.OrderedDict` to accept ordered dictionaries. Since Python 3.7 all `dict`s are effectively `collections.OrderedDict` because people now expect this kind of inefficient default behavior everywhere. ¯\_(ツ)_/¯
- cdogl 3y agoThe same goes for maps in Go, which now explicitly randomizes map iteration with the range keyword to prevent developers from relying on a particular ordering. Neat trick.
- mike_hock 3y agoThat might get them to rely on the randomization, though :)
- deleted 3y ago[deleted]
- jamesfinlayson 3y agoI don't disagree, but people might still assume it. If you serialise a Map in Java, some Map implementations will maintain insertion order for example.
- ifwinterco 3y agoI think this is why JS and Python chose to make key ordering defined - the most popular implementations maintained insertion order anyway, so it was inevitable people would end up writing code relying on it
- plq 3y agoYou make it sound like it's one of the laws of physics. ON part of JSON doesn't know about objects. When serialized, object entries are just an array of key-value pairs with a weird syntax and a well-defined order. That's true for any serialization format actually. It's the JS part of JSON that imposes non-duplicate keys with undefined order constraint. You are the engineer, you can decide how you use your tools depending on your use case. Unless eg. you need interop with the rest of the world, it's your JSON, (mis)treat it to your heart's content.
- mike_d 3y ago> You make it sound like it's one of the laws of physics. The text I quoted is from the RFC. json.org and ECMA-404 both agree. You are welcome to do whatever you want, but then it isn't JSON anymore.
- The_Colonel 3y agoThat does not follow. JSON formatted data remains JSON no matter if you use it incorrectly. This happens all the time in the real world - applications unknowingly rely on undefined (but generally true) behavior. If you e.g. need to integrate with a legacy application where you're not completely sure how it handles JSON, then it's likely better to use plain text JSON. You may take the risk as well, but then good luck explaining that those devs 10 years out of the company are responsible for the breakage happening after you've converted JSON to JSONB. In some cases, insisting on ignoring the key order is too expensive luxury, since it basically forces you to parse the whole document first and only then process it. In case you have huge documents, you have to stream-read and this often implies relying on a particular key order (which isn't a problem since those same huge documents will likely be stream-written with a particular order too).
- throwaway290 3y agoIt is very much still JSON, and your code can very much assume keys are ordered if your JSON tools respect it. ECMA-404 agrees: > The JSON syntax does not impose any restrictions on the strings used as names, does not require that name strings be unique, and does not assign any significance to the ordering of name/value pairs. These are all semantic considerations that may be defined by JSON processors or in specifications defining specific uses of JSON for data interchange. If you work in environments that respect JSON key order (like browser and I think also Python) then unordered behavior of JSONB would be the exception not the rule.
- ako 3y agoAssume you're writing an editor for JSON files. Don't think many users of that editor would be very happy if you change the order of the attributes in their json files, even though technically it's the same...
- ash 3y agoSQLite JSONB does maintain order: https://news.ycombinator.com/item?id=38547254 https://news.ycombinator.com/item?id=38547254
- oefrha 3y agoThere’s processing to be done with JSONB on every read/write, which is wasted if you’re always reading/writing the full blob.
- lifthrasiir 3y agoWhich occurs with JSON as well (SQLite doesn't have a dedicated JSON nor JSONB type). The only actual cost would be the conversion between JSONB and JSON.
- oefrha 3y agoNo, SQLite's "JSON" is just TEXT, there's no overhead with reading/writing a string.
- lifthrasiir 3y agoThat's what I said I think? "JSON" is a TEXT that is handled as a JSON string by `json_*` functions, while "JSONB" is a BLOB that is handled as an internal format by `jsonb_*` functions. You generally don't want JSONB in the application side though, so you do need a conversion for that.
- oefrha 3y agoThere’s no point using json_* functions if you’re always reading/writing the full blob.
- lifthrasiir 3y agoOf course (and I never said that), but if you need to store a JSON value in the DB and use a JSONB-encoded BLOB as an optimization, you eventually read it back to a textual JSON. It's just like having a UNIX timestamp in your DB and converting back to a parsed date and time for application uses, except that applications may handle a UNIX timestamp directly and can't handle JSONB at all.
- 3y ago
- chippiewill 3y agoNot specifically JSONB, but I do recall that with MongoDB's equivalent, BSON, the sizes of the binary equivalent tend to be larger in practice, I would expect JSONB to have a similar trade off. There'll also be a conversion cost if you ultimately want it back in JSON form.
- cowsandmilk 3y agoFrom the post: > JSONB is also slightly smaller than text JSON in most cases (about 5% or 10% smaller) so you might also see a modest reduction in your database size if you use a lot of JSON.
- kevincox 3y agoAccording to the linked announcement the data size is 5-10% smaller. So you are exchanging a small processing cost for smaller storage size. It will depend on your application but in many cases smaller disk reads and writes as well as more efficient cache utilization will make storing as JSONB a better choice even if you never manipulate it in SQL. Although the difference is likely small.
- Someone 3y agoI haven’t delved into it, but you likely lose the ability to round-trip your json strings, just as in postgreSQL’s jsonb type. Jsonb_extract(jsonb(foo), '€') likely - will remove/change white space in ‘foo’, - does not guarantee to keep field order - will fail if a dictionary in the ‘foo’ string has duplicate fields - will fail if ‘foo’ doesn’t contain a valid json string
- cryptonector 3y agoPG's JSONB is compressible yet also indexed (objects' key/value pairs are sorted on key and serialized array-like; arrays are indexed by integers; therefore binary searching large objects works). The history of PG's JSONB type is very interesting.