4 ms·

Quick question regarding JSONB: Is attribute order stable? Obviously, order is not preserved, but if the order changes on subsequent accesses, this causes prob

Quick question regarding JSONB: Is attribute order stable? Obviously, order is not preserved, but if the order changes on subsequent accesses, this causes problems if you ever serve content directly from a jsonb field without sorting the attributes manually.

arthursilva 12y ago
Fields are always sorted (internally), I didn't test but I assume it's preserved on the wire (as json) as well.
matthewrudy 12y ago
According to the json spec "An object is an unordered set of name/value pairs." So you should not be relying on ordering of json fields.
- steventhedev 12y ago
  In theory, correct. In practice, here are the two use cases which that approach breaks: 1. Cache control using etags. If the content changes by a single byte, even if semantically identical, the etag should change. Hence '{"foo":1,"bar":2}' is not equivalent to '{"bar":2,"foo":1}'. I can see serving such information directly, or embedding it into a larger JSON response. 2. Committed JSON files. This is an anti-pattern, but one I've seen many times. In one case, it was a translation file that was generated in a separate project. When I joined, it had been like that for more than 5 years. It was like that when I left, although I at least monkey patched REXML to emit sorted attributes in XML (also unordered in theory). So while in practice, I don't depend on a specific ordering, but I need the order (whatever it is) to be stable. For all I care, it could be sorted by the cryptographic hash of the keys, so long as it is consistent. Just to gather what others have said here: JSONB appears to store fields sorted lexicographically for binary search purposes, and emits them in that order as well. This could be undocumented behavior, yet important for the above two use cases.
  - perlgeek 12y ago
    You can still use the 'json' type (not 'jsonb'), which preserves the text representation. Or you can sort the keys on serialization when you serve or store the JSON.
  - lucian1900 12y ago
    For the former, you really need to sort the keys. If you ever end up passing the JSON through any other encode/decode step, your ETags will break and it won't be obvious why. For hand-written JSON, I'm not sure there's a good solution besides modding the users' editors to sort for them.
jeltz 12y ago
The attributes are stored and returned in sorted order. The reason for this is to allow binary search in JSON objects with many keys.
azdle 12y ago
Why would this cause problems? Order is irrelevant in JSON. "An object is an unordered set of name/value pairs."[0] From a there is absolutely no difference between {"name": "Patrick", "age": 24} and {"age": 24, "name": "Patrick"}. [0] http://json.org/ http://json.org/ EDIT: Well, now four of us have replied at the same time saying the same thing, I'd say this topic is now well covered.
- gburt 12y ago
  Of course, it is still an interesting question of whether it would be practically stable in Postgres or not.
- skybrian 12y ago
  Sometimes it matters whether serialization to a string is a deterministic function. If the JSON is the same, you want the output to be the same. A JSON parser won't care, but it's useful to do a string comparison without parsing (for example when diffing output).
  - lsaferite 12y ago
    But that's not really a correct thing to expect is it? If an object is an unordered set of name/value pairs then multiple serialized versions of the object may indeed be the same object data.
    - skybrian 12y ago
      It's not something you can assume, but sorting the keys is sometimes a desirable feature in a JSON serializer. (Just like pretty-printing is.)
      - tomiko_nakamura 12y ago
        AFAIK the default serializer does not guarantee the order to be stable, but I haven't participated in the development of this feature that closely. So maybe I'm wrong. Anyway, it should be possible to write a simple serializer on your own - a trivial PL/V8 function should suffice, I guess. And then you can define a CAST using that function (but generally adding casts is a bit dangerous, as it may have unexpected consequences).

arthursilva 12y ago

Fields are always sorted (internally), I didn't test but I assume it's preserved on the wire (as json) as well.

matthewrudy 12y ago

According to the json spec "An object is an unordered set of name/value pairs." So you should not be relying on ordering of json fields.

steventhedev 12y ago
In theory, correct. In practice, here are the two use cases which that approach breaks: 1. Cache control using etags. If the content changes by a single byte, even if semantically identical, the etag should change. Hence '{"foo":1,"bar":2}' is not equivalent to '{"bar":2,"foo":1}'. I can see serving such information directly, or embedding it into a larger JSON response. 2. Committed JSON files. This is an anti-pattern, but one I've seen many times. In one case, it was a translation file that was generated in a separate project. When I joined, it had been like that for more than 5 years. It was like that when I left, although I at least monkey patched REXML to emit sorted attributes in XML (also unordered in theory). So while in practice, I don't depend on a specific ordering, but I need the order (whatever it is) to be stable. For all I care, it could be sorted by the cryptographic hash of the keys, so long as it is consistent. Just to gather what others have said here: JSONB appears to store fields sorted lexicographically for binary search purposes, and emits them in that order as well. This could be undocumented behavior, yet important for the above two use cases.
- perlgeek 12y ago
  You can still use the 'json' type (not 'jsonb'), which preserves the text representation. Or you can sort the keys on serialization when you serve or store the JSON.
- lucian1900 12y ago
  For the former, you really need to sort the keys. If you ever end up passing the JSON through any other encode/decode step, your ETags will break and it won't be obvious why. For hand-written JSON, I'm not sure there's a good solution besides modding the users' editors to sort for them.

jeltz 12y ago

The attributes are stored and returned in sorted order. The reason for this is to allow binary search in JSON objects with many keys.

azdle 12y ago

Why would this cause problems? Order is irrelevant in JSON. "An object is an unordered set of name/value pairs."[0] From a there is absolutely no difference between {"name": "Patrick", "age": 24} and {"age": 24, "name": "Patrick"}. [0] http://json.org/ http://json.org/ EDIT: Well, now four of us have replied at the same time saying the same thing, I'd say this topic is now well covered.

gburt 12y ago
Of course, it is still an interesting question of whether it would be practically stable in Postgres or not.
skybrian 12y ago
Sometimes it matters whether serialization to a string is a deterministic function. If the JSON is the same, you want the output to be the same. A JSON parser won't care, but it's useful to do a string comparison without parsing (for example when diffing output).
- lsaferite 12y ago
  But that's not really a correct thing to expect is it? If an object is an unordered set of name/value pairs then multiple serialized versions of the object may indeed be the same object data.
  - skybrian 12y ago
    It's not something you can assume, but sorting the keys is sometimes a desirable feature in a JSON serializer. (Just like pretty-printing is.)
    - tomiko_nakamura 12y ago
      AFAIK the default serializer does not guarantee the order to be stable, but I haven't participated in the development of this feature that closely. So maybe I'm wrong. Anyway, it should be possible to write a simple serializer on your own - a trivial PL/V8 function should suffice, I guess. And then you can define a CAST using that function (but generally adding casts is a bit dangerous, as it may have unexpected consequences).