7 ms·

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

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}

troyk 13y ago
It will overwrite, JSON type is basically a text field that enforces a valid JSON constraint
DrJokepu 13y ago
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?
- joevandyk 13y ago
  I think you are answering a different question. He's asking if you can update part of a json column.
  - DrJokepu 13y ago
    I don’t think so? I mean that’s how his question started out but his real question was in the last two paragraphs.
deleted 13y ago
[deleted]
meowface 13y ago
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.

troyk 13y ago

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

DrJokepu 13y ago

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?

joevandyk 13y ago
I think you are answering a different question. He's asking if you can update part of a json column.
- DrJokepu 13y ago
  I don’t think so? I mean that’s how his question started out but his real question was in the last two paragraphs.

deleted 13y ago

[deleted]

meowface 13y ago

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.