Contents
How to update columns of type JSONB?
The value in the data column is replaced with the final version. This is coming in 9.5 in the form of jsonb_set by Andrew Dunstan based on an existing extension jsonbx that does work with 9.4 For those that run into this issue and want a very quick fix (and are stuck on 9.4.5 or earlier), here is a potential solution:
Why is JSON used in PostgreSQL MVCC model?
JSON is primarily intended to store whole documents that do not need to be manipulated inside the RDBMS. Related: Updating a row in Postgres always writes a new version of the whole row. That’s the basic principle of Postgres’ MVCC model.
Can a JSONB column be cast to a string?
Ultimately, the accepted answer is correct in that you cannot modify an individual piece of a jsonb object (in 9.4.5 or earlier); however, you can cast the jsonb column to a string (::TEXT) and then manipulate the string and cast back to the jsonb form (::jsonb).
Why do we need to update rows in JSON?
From a performance perspective, it hardly matters whether you change a single piece of data inside a JSON object or all of it: a new version of the row has to be written. JSON data is subject to the same concurrency-control considerations as any other data type when stored in a table.
How to update complex JSONB column in PostgreSQL?
I suggest a LATERAL join, among other things to exclude the possibility of multiple matching rows that might be lumped together incorrectly in a plain join. The same can be implemented with a correlated subquery in the UPDATE (or in the SELECT as well).
Why are contacts stored in a JSONB table?
Suppose you’re implementing a customer screen to store dynamic contacts for each customer. Then you come up with the idea of storing the contacts as a JSONB column because they’re dynamic, and thus using a not relational data structure makes sense. Then you create a customers table with a JSONB contacts column and insert some data into it: