4 ms·
There’s a huge nuance worth mentioning: with JSONB you lose key ordering in objects. This may be desired - it makes two effectively-equal objects have the same
by btown 3y ago
There’s a huge nuance worth mentioning: with JSONB you lose key ordering in objects.
This may be desired - it makes two effectively-equal objects have the same representation at rest!
But if you are storing human-written JSON - say, configs from an internal interface, where one might collocate a “__foo_comments” key above “foo” - their layout will be lost, and this may lead to someone visually seeing their changes scrambled on save.
- cryptonector 3y agoJSON processors are not required nor expected to retain object key ordering from either input or object construction order.
- fl0ki 3y agoThat compounds really badly with another lack of specificity in JSON: parsers can treat duplicate keys in arbitrary ways, e.g. keep only the first or last value, combine values somehow, error out, etc. When keys are not unique, their order can matter again, and can matter differently to different parsers.
- btown 3y agoThe question is whether people think of their database as a processor of data or a storage location for data, exactly as it was provided. Given that a lot of people use SQLite as the latter, it's a worthwhile caveat to make people aware of. Additionally, Javascript environments (formalized in ES2015), your text editor, and your filesystem all guarantee that they'll preserve object key order when JSON is evaluated. It's not unreasonable that someone would expect this of their database!
- ash 3y ago> Javascript environments ... guarantee that they'll preserve object key order when JSON is evaluated Yes, but only for non-integer keys.
- gmfawcett 3y agoIs there such a thing as a non-string JSON key to be evaluated? https://www.json.org/ https://www.json.org/ suggests not.
- ash 3y agoYes, in the context of JSON handling by JavaScript. I meant string keys that can be interpreted as non-negative numbers. Those are traversed first, the rest of the keys are coming in insertion order. For example: > JSON.stringify({'one': 1, '3': 3, 'two': 2, '2': 2, 'three': 3, '1': 1}) '{"1":1,"2":2,"3":3,"one":1,"two":2,"three":3}'
- gmfawcett 3y agoThanks for explaining. That behaviour is... interesting!
- seanhunter 3y agoIf you're storing it exactly as provided then surely until you know it's actually valid json you want a string, not a json/jsonb column.
- cryptonector 3y ago> The question is whether people think of their database as a processor of data or a storage location for data If you use the database's JSON type(s) and/or functions, then yes, your database is a JSON processor. And, yes, your database is not a dumb store, not if it's an RDBMS. The whole point of it is that it's not a dumb store.
- ash 3y agoYes, but it's not impossible to achieve it in practice. For example, JavaScript JSON.stringify and JSON.parse have well-defined behaviour: > Properties are visited using the same algorithm as Object.keys(), which has a well-defined order and is stable across implementations https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/JSON/stringify https://developer.mozilla.org/en-US/docs/Web/JavaScript/Refe... > The traversal order, as of modern ECMAScript specification, is well-defined and consistent across implementations. Within each component of the prototype chain, all non-negative integer keys (those that can be array indices) will be traversed first in ascending order by value, then other string keys in ascending chronological order of property creation. https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Statements/for...in https://developer.mozilla.org/en-US/docs/Web/JavaScript/Refe... Similarly, Python json module guarantees that "encoders and decoders preserve input and output order by default. Order is only lost if the underlying containers are unordered." Since 3.7 dict maintains insertion order. https://docs.python.org/3/library/json.html https://docs.python.org/3/library/json.html https://docs.python.org/3/library/stdtypes.html#dict https://docs.python.org/3/library/stdtypes.html#dict Yes, JS and Python behaviour are not the same for all cases, however, non-integer keys do maintain order across Python and JavaScript.
- cryptonector 3y ago> Yes, but it's not impossible to achieve it in practice. For any one implementation. But there's a very large number of implementations. You just can't count on object key order being preserved, so don't.
- ash 3y agoThere are situations where keeping order is useful. For example, human-editable json config files. I also gave an example of two implementations that are compatible for a useful subset of keys. By the way, SQLite JSONB keeps object keys in insertion order, similar to Python: https://news.ycombinator.com/item?id=38547254 https://news.ycombinator.com/item?id=38547254
- 3y ago
- ash 3y agoWhat about SQLite JSONB? Does it maintain order? UPDATE: I think SQLite JSONB does maintain order. For example: select json(jsonb('{"a": 1, "b": 2, "c": 3, "d": 4}'));" -- {"a":1,"b":2,"c":3,"d":4} And it does maintain order when adding new keys: select json(jsonb_insert(jsonb('{"a": 1, "b": 2, "c": 3, "d": 4}'), '$.e', 99)); -- {"a":1,"b":2,"c":3,"d":4,"e":99} (tested on https://codapi.org/sqlite/ https://codapi.org/sqlite/)
- sgbeal 3y ago> There’s a huge nuance worth mentioning: with JSONB you lose key ordering in objects. JSON never guarantees any ordering. From json.org: > An object is an unordered set of name/value pairs. If you depend on a given implementation doing so, you're depending on a quirk of that implementation, not on a JSON-defined behavior.
- pie_flavor 3y agoUsing a function to query a fact about a string does not imply that the string should change, regardless of what guarantees are present within the format that the string is in. If you expect to get out the same string you put in, then there's nothing implementation-quirk about that.
- sgbeal 3y ago> Using a function to query a fact about a string does not imply that the string should change, ... then there's nothing implementation-quirk about that. Fair enough when considering only the string value. My ill-expressed point was more about JSON as a data exchange format - it may pass through any number of JSON parser/storage implementations, any of which may use an arbitrary order for the keys.
- ric2b 3y agoYou're not storing a string, you're storing a JSON object. If you want to store a string use TEXT or VARCHAR.
- pie_flavor 3y agoThat is what this post chain is about. The nuance between choosing to store your JSON as a string in a TEXT column or as JSONB in a BLOB column.