4 ms·

Indeed. Those who use Postgres are already familiar with the difference. Rule of thumb: if you are not sure, or do not have time for the nuances, just use JSON

Indeed. Those who use Postgres are already familiar with the difference. Rule of thumb: if you are not sure, or do not have time for the nuances, just use JSONB.

kevincox 3y ago
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.
- yencabulator 3y ago
  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.
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 ago
  JSON processors are not required nor expected to retain object key ordering from either input or object construction order.
  - fl0ki 3y ago
    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.
  - btown 3y ago
    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!
    - 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 ago
        Is 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 ago
        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}'
        3y ago
- ash 3y ago
  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/ 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 ago
    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.
    - 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 ago
      You'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 ago
        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.

Indeed. Those who use Postgres are already familiar with the difference. Rule of thumb: if you are not sure, or do not have time for the nuances, just use JSONB.

kevincox 3y ago

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.

yencabulator 3y ago
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.

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 ago
JSON processors are not required nor expected to retain object key ordering from either input or object construction order.
- fl0ki 3y ago
  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.
- btown 3y ago
  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!
  - 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 ago
      Is 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 ago
        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}'
        3y ago
ash 3y ago
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/ 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 ago
  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.
  - 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 ago
    You'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 ago
      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.