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.

What 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/)

JSON processors are not required nor expected to retain object key ordering from either input or object construction order.

That 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.

The 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!

> 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.

If 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.

> Javascript environments ... guarantee that they'll preserve object key order when JSON is evaluated

Yes, but only for non-integer keys.

Is there such a thing as a non-string JSON key to be evaluated? https://www.json.org/ suggests not.

Yes, 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}'

Thanks for explaining. That behaviour is... interesting!

Yes, 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/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/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/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.

> 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.

You can count on object key order being preserved when you control the implementation. Which is why it's useful to know whether a given implementation preserves key order.

There 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

> There are situations where keeping order is useful. For example, human-editable json config files.

You might have to just normalize every time you want to edit that.

> 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.

Using 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.

> 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.

You're not storing a string, you're storing a JSON object. If you want to store a string use TEXT or VARCHAR.

That 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.

Slight caveat. It seems that you should default to JSONB for all cases except for values directly returned in the query result. As IIUC you will get a JSONB blob back and will be responsible for parsing it on the client.

So use it for writes:

    UPDATE t SET col = jsonb_*(?)

Also use it for filters if applicable (although this seems like a niche use case, I can't actually think of an example).

But if returning values you probably want to use `json_*` functions

    SELECT json_extract(col1, '$.myProp') FROM t

Otherwise you will end up receiving the binary format.

Wait is it really true that if you use SQLite's JSONB format, `select my_json_column from foo` becomes unreadable to humans? That seems.. unacceptable. One would expect it to convert its internal format back to JSON for consumption.