Hacker News new | past | comments | ask | show | jobs | submit login

One other (big) difference between json and jsonb is that the former preserves extra white space. The exact text string of the json is stored. If that matters to you, say you're taking a signature of the original value, then you should use non-b json. Otherwise, stick to jsonb.



Probably the biggest gotcha of JSON is that there is no equality operator implemented for it. The reason is the philosophy of postgres to never do anything if you don't do it right, and you can't compare JSON as strings because of key order and whitespace.

In itself it doesn't seem like a big deal, but you'll lose generic things that normally always work. An example would be a trigger that updates an updated_at column, by checking

IF OLD IS DISTINCT FROM NEW

This syntax normally works on everything, but will throw if a column is of type JSON.


An even bigger difference is that with jsonb, strings and numbers are converted to their equivalent PostgreSQL types. That means that strings can't contain null characters or characters that violate the database's encoding rules. Consequentially, not all valid JSON documents can be stored in jsonb, which is usually fine but can sometimes introduce headaches.


Why does whitespace (outside of string values) ever matter in JSON? Most servers will strip as much whitespace as possible to reduce response size. Also stripping before signing is pretty trivial.


just throwing something out, but if you're allowing users to enter in their own json.


Why would you want to maintain extra whitespace (again outside of text strings) after a save? Any sort of JSON editor is going to have some sort of formatting function that could restore it on load.


One exmaple: implement a text editor like GitHub's gist naively.

You can try to be clever and encode the incoming data to reduce whitespace, but the business requirement is whatever user entered is what user expects to get back. Otherwise, your best bet is save the entire text.


But in that case, should you really be storing the JSON as JSON? Isn't it just a string as far as you're concerned? You're not going to be querying the user's JSON, are you?


You are correct and it depends on how you want to save your data (poor or bad!). In the article it does use log as an example. Perhaps that would be a more convincing-ish example.




Guidelines | FAQ | Lists | API | Security | Legal | Apply to YC | Contact

Search: