Hacker News Reader

Comment by DrJokepu | Hacker News Reader

HStore and especially JSON are useful because they allow you to store complex data structures fairly easily. Do you have something that contains a number of somethings that contain a number of somethings that contain a number of somethings? Normally you would need like, 4 tables to store this and carefully planned queries to work with them efficiently. However, if the object graph has no external references (besides the top-level object) you can just safely shove the whole thing into a JSON without losing referential integrity.

Your reasons are very valid however so obviously you have to think about the consequences before using non-standard features. However, not using advanced or novel features (which non-standard features tend to be) can also lead to stagnation and loss of competitiveness so you have to also be careful with being careful, if that makes sense.

View on HN

I'm not very familiar with the JSON datatype in postgres. Is it possible to update subvalues without writing the entire JSON bag?

For example: {"a": 1, "b": 2} (in psuedocode)

User 1: select JSON

User 2 select JSON

User 1: update JSON set "a" = 11

User 2: update JSON set "b" = 22

What would the value of the JSON column be after that. In other systems I've worked in (where it's JSON in a database column), the answer is {"a": 1, "b", 22} or a failure on the second update letting the client know that their copy is out of date.

I'd love to know if postgres can deal with this in the normal relational way so that the result is actually {"a": 11, "b": 22}

It will overwrite, JSON type is basically a text field that enforces a valid JSON constraint

Depending on at which point these transactions are started and committed and the isolation level of the transaction this would either result in {"a": 1, "b", 22}, failure at step 3, failure at step 4 or failure when one of the transactions is committed. However, since locks are on row level rather than on value (cell) level, this would be the same even if these values were stored separately in ordinary column types.

You could still achieve what you want by using read committed transaction isolation and keeping your values in separate rows, e.g. a row with a/1 and a second row with b/2. But basically you would need a separate record for every property in your object graph that you want this behaviour to apply to, which could get pretty messy. Does that make sense to you?

I think you are answering a different question. He's asking if you can update part of a json column.

I don’t think so? I mean that’s how his question started out but his real question was in the last two paragraphs.

If you want to do that, you'll pretty much need to use a database like MongoDB or CouchDB. All operations are focused on JSON traversal and updating.

Reply on news.ycombinator.com