Comment by testing12341234 | Hacker News Reader

Comment by testing12341234 | Hacker News Reader

Parent Full thread

testing12341234

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}

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.

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

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