How to perform update operations on columns of type JSONB in Postgres 9.4

by

If you’re able to upgrade to Postgresql 9.5, the jsonb_set command is available, as others have mentioned.

In each of the following SQL statements, I’ve omitted the where clause for brevity; obviously, you’d want to add that back.

Update name:

UPDATE test SET data = jsonb_set(data, '{name}', '"my-other-name"');

Replace the tags (as oppose to adding or removing tags):

UPDATE test SET data = jsonb_set(data, '{tags}', '["tag3", "tag4"]');

Replacing the second tag (0-indexed):

UPDATE test SET data = jsonb_set(data, '{tags,1}', '"tag5"');

Append a tag (this will work as long as there are fewer than 999 tags; changing argument 999 to 1000 or above generates an error. This no longer appears to be the case in Postgres 9.5.3; a much larger index can be used):

UPDATE test SET data = jsonb_set(data, '{tags,999999999}', '"tag6"', true);

Remove the last tag:

UPDATE test SET data = data #- '{tags,-1}'

Complex update (delete the last tag, insert a new tag, and change the name): 

UPDATE test SET data = jsonb_set(
    jsonb_set(data #- '{tags,-1}', '{tags,999999999}', '"tag3"', true), 
    '{name}', '"my-other-name"');

It’s important to note that in each of these examples, you’re not actually updating a single field of the JSON data. Instead, you’re creating a temporary, modified version of the data, and assigning that modified version back to the column. In practice, the result should be the same, but keeping this in mind should make complex updates, like the last example, more understandable.

In the complex example, there are three transformations and three temporary versions: First, the last tag is removed. Then, that version is transformed by adding a new tag. Next, the second version is transformed by changing the name field. The value in the data column is replaced with the final version.

More Related Contents:

  1. How to do an update + join in PostgreSQL?
  2. Flatten aggregated key/value pairs from a JSONB field?
  3. How do I do large non-blocking updates in PostgreSQL?
  4. Using jsonb_set() for updating specific jsonb array value
  5. Use variable set by psql meta-command inside of DO block
  6. How to convert PostgreSQL 9.4’s jsonb type to float
  7. Postgresql query array of objects in JSONB field
  8. how to store PostgreSQL jsonb using SpringBoot + JPA?
  9. Error: Column does not exist in postgresql for update [duplicate]
  10. user postgres launches process that takes all CPUs 100% usage
  11. Operator does not exist: json = json
  12. How to escape question mark (?) character with Spring JpaRepository
  13. PostgreSQL jsonb, `?` and JDBC
  14. Making sense of Postgres row sizes
  15. How to export table as CSV with headings on Postgresql?
  16. What’s the PostgreSQL datatype equivalent to MySQL AUTO INCREMENT?
  17. Can I automatically create a table in PostgreSQL from a csv file with headers?
  18. PostgreSQL query very slow with limit 1
  19. How to add an auto-incrementing primary key to an existing table, in PostgreSQL?
  20. Postgres query optimization (forcing an index scan)
  21. psql: FATAL: database “” does not exist
  22. Difference between timestamps with/without time zone in PostgreSQL
  23. Omitting the double quote to do query on PostgreSQL
  24. Storing Images in PostgreSQL
  25. Psql could not connect to server: No such file or directory, 5432 error?
  26. Update a column of a table with a column of another table in PostgreSQL
  27. Maximum characters in labels (table names, columns etc)
  28. Docker – Postgres and pgAdmin 4 : Connection refused
  29. What’s the difference between pg_table_size, pg_relation_size & pg_total_relation_size? (PostgreSQL)
  30. JPA and PostgreSQL with GenerationType.IDENTITY

Leave a Comment