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

Question

Looking through the documentation for the Postgres 9 4 datatype JSONB  it is not immediately obvious to me how to do updates on JSONB columns    Documentation for JSONB types and functions    http   www postgresql org docs 9 4 static functions-json html http   www postgresql org docs 9 4 static datatype-json html  As an examples  I have this basic table structure   CREATE TABLE test id serial  data jsonb     Inserting is easy  as in   INSERT INTO test data  values     name    my-name    tags     tag1    tag2         Now  how would I update the  data  column  This is invalid syntax   UPDATE test SET data- gt  name     my-other-name  WHERE id   1    Is this documented somewhere obvious that I missed  Thanks

User · Answer

Matheus de Oliveira created handy functions for JSON CRUD operations in postgresql. They can be imported using the \i directive. Notice the jsonb fork of the functions if jsonb if your data type.

9.3 json https://gist.github.com/matheusoliveira/9488951

9.4 jsonb https://gist.github.com/inindev/2219dff96851928c2282

User · Answer

This question was asked in the context of postgres 9 4  however new viewers coming to this question should be aware that in postgres 9 5  sub-document Create Update Delete operations on JSONB fields are natively supported by the database  without the need for extension functions   See  JSONB modifying operators and functions

User · Answer

Ideally  you don t use JSON documents for structured  regular data that you want to manipulate inside a relational database  Use a normalized relational design instead   JSON is primarily intended to store whole documents that do not need to be manipulated inside the RDBMS  Related    JSONB with indexing vs  hstore   Updating a row in Postgres always writes a new version of the whole row  That s the basic principle of Postgres  MVCC model  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   Thus the advice in the manual      JSON data is subject to the same concurrency-control considerations as   any other data type when stored in a table  Although storing large   documents is practicable  keep in mind that any update acquires a   row-level lock on the whole row  Consider limiting JSON documents to a   manageable size in order to decrease lock contention among updating   transactions  Ideally  JSON documents should each represent an atomic   datum that business rules dictate cannot reasonably be further   subdivided into smaller datums that could be modified independently    The gist of it  to modify anything inside a JSON object  you have to assign a modified object to the column  Postgres supplies limited means to build and manipulate json data in addition to its storage capabilities  The arsenal of tools has grown substantially with every new release since version 9 2  But the principal remains  You always have to assign a complete modified object to the column and Postgres always writes a new row version for any update   Some techniques how to work with the tools of Postgres 9 3 or later    How do I modify fields inside the new PostgreSQL JSON datatype    This answer has attracted about as many downvotes as all my other answers on SO together  People don t seem to like the idea  a normalized design is superior for non-dynamic data  This excellent blog post by Craig Ringer explains in more detail     PostgreSQL anti-patterns  Unnecessary json hstore dynamic columns

User · Answer

update the  name  attribute   UPDATE test SET data data     name   my-other-name    WHERE id   1    and if you wanted to remove for example the  name  and  tags  attributes   UPDATE test SET data data-   name   tags     text   WHERE id   1

User · Answer

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  Creation of test table CREATE TABLE test id serial  data jsonb   INSERT INTO test data  values     quot name quot    quot my-name quot    quot tags quot     quot tag1 quot    quot tag2 quot        Update statement to change jsonb value UPDATE test  SET data   replace data  TEXT     quot my-name quot       quot my-other-name quot     jsonb  WHERE id   1   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   There are two important caveats  this will replace all values equaling  quot my-name quot  in the json  in the case you have multiple objects with the same value  this is not as efficient as jsonb set would be if you are using 9 5

User · Answer

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

User · Answer

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

User · Answer

Maybe  UPDATE test SET data     my-other-name    json WHERE id   1   It worked with my case  where data is a json type

User · Answer

I wrote small function for myself that works recursively in Postgres 9 4  I had same problem  good they did solve some of this headache in Postgres 9 5   Anyway here is the function  I hope it works well for you    CREATE OR REPLACE FUNCTION jsonb update val1 JSONB val2 JSONB  RETURNS JSONB AS    DECLARE     result JSONB      v RECORD  BEGIN     IF jsonb typeof val2     null      THEN          RETURN val1      END IF       result   val1       FOR v IN SELECT key  value FROM jsonb each val2  LOOP          IF jsonb typeof val2- gt v key     object              THEN                 result   result    jsonb build object v key  jsonb update val1- gt v key  val2- gt v key                ELSE                 result   result    jsonb build object v key  v value           END IF      END LOOP       RETURN result  END     LANGUAGE plpgsql    Here is sample use   select jsonb update    a    b    c    d  5  dd  6   cc  1    aaa  5    jsonb     a    b    c    d  15     aa  9    jsonb                               jsonb update                              ---------------------------------------------------------------------    a     b     c     d   15   dd   6    cc   1     aa   9   aaa   5   1 row    As you can see it analyze deep down and update add values where needed

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

Examples related to postgresql

Examples related to sql-update

Examples related to crud

Examples related to postgresql-9.4

Examples related to jsonb