How do I modify fields inside the new PostgreSQL JSON datatype

Question

With postgresql 9 3 I can SELECT specific fields of a JSON data type  but how do you modify them using UPDATE  I can t find any examples of this in the postgresql documentation  or anywhere online  I have tried the obvious   postgres   create table test  data json   CREATE TABLE postgres   insert into test  data  values     a  1  b  2     INSERT 0 1 postgres   select data- gt  a  from test where data- gt  gt  b     2     column  ----------  1  1 row  postgres   update test set data- gt  a    to json 5  where data- gt  gt  b     2   ERROR   syntax error at or near  - gt   LINE 1  update test set data- gt  a    to json 5  where data- gt  gt  b     2

User · Answer

To build upon  pozs s answers  here are a couple more PostgreSQL functions which may be useful to some   Requires PostgreSQL 9 3    Delete By Key  Deletes a value from JSON structure by key   CREATE OR REPLACE FUNCTION  json object del key      json           json     key to del     TEXT     RETURNS json   LANGUAGE sql   IMMUTABLE   STRICT AS  function  SELECT CASE   WHEN   json  - gt   key to del   IS NULL THEN  json    ELSE  SELECT concat      string agg to json  key              value                        FROM  SELECT                     FROM json each  json                    WHERE  key   lt  gt   key to del                   AS  fields    json END  function     Recursive Delete By Key  Deletes a value from JSON structure by key-path   requires  pozs s json object set key function   CREATE OR REPLACE FUNCTION  json object del path      json           json     key path       TEXT       RETURNS json   LANGUAGE sql   IMMUTABLE   STRICT AS  function  SELECT CASE   WHEN   json  - gt   key path  l    IS NULL THEN  json    ELSE      CASE COALESCE array length  key path   1   0           WHEN 0 THEN  json           WHEN 1 THEN  json object del key   json    key path  l            ELSE  json object set key               json               key path  l               json object del path                COALESCE NULLIF   json  - gt   key path  l    text   null           json                key path  l 1 u                                 END     END   FROM array lower  key path   1  l         array upper  key path   1  u  function     Usage examples   s1   SELECT json object del key     hello   7 3 1   foo    mofu   fuwa    moe   kyun                                        foo                json object del path    hello   7 3 1   foo    mofu   fuwa    moe   kyun                                          foo   moe        json object del key            json object del path --------------------- -----------------------------------------    hello   7 3 1         hello   7 3 1   foo    mofu   fuwa

User · Answer

The following plpython snippet might come in handy   CREATE EXTENSION IF NOT EXISTS plpythonu  CREATE LANGUAGE plpythonu   CREATE OR REPLACE FUNCTION json update data json  key text  value text   RETURNS json  AS        import json     json data   json loads data      json data key    value     return json dumps json data  indent 4      LANGUAGE plpythonu   -- Check how JSON looks before updating  SELECT json update content  json   CFRDiagnosis mod nbs    1   FROM sc server centre document WHERE record id   35 AND template    CFRDiagnosis    -- Once satisfied update JSON inplace  UPDATE sc server centre document SET content   json update content  json   CFRDiagnosis mod nbs    1   WHERE record id   35 AND template    CFRDiagnosis

User · Answer

If you want to use values from other columns in your JSON update command you can use string concatenation  UPDATE table SET column1   column1  jsonb -  key         quot key quot        column2  text           jsonb where

User · Answer

Update  With PostgreSQL 9 5  there are some jsonb manipulation functionality within PostgreSQL itself  but none for json  casts are required to manipulate json values   Merging 2  or more  JSON objects  or concatenating arrays   SELECT jsonb    quot a quot  1      jsonb    quot b quot  2    -- will yield jsonb    quot a quot  1  quot b quot  2          jsonb    quot a quot  1      jsonb    quot b quot  2    -- will yield jsonb    quot a quot  1  quot b quot  2    So  setting a simple key can be done using  SELECT jsonb    quot a quot  1      jsonb build object   lt key gt      lt value gt     Where  lt key gt  should be string  and  lt value gt  can be whatever type to jsonb   accepts  For setting a value deep in a JSON hierarchy  the jsonb set   function can be used  SELECT jsonb set    quot a quot   null   quot b quot            a 1 b 0    jsonb    quot c quot  3    -- will yield jsonb    quot a quot   null   quot b quot     quot c quot  3        Full parameter list of jsonb set    jsonb set target         jsonb            path           text              new value      jsonb            create missing boolean default true   path can contain JSON array indexes too  amp  negative integers that appear there count from the end of JSON arrays  However  a non-existing  but positive JSON array index will append the element to the end of the array  SELECT jsonb set    quot a quot   null   quot b quot   1 2         a 1 b 1000    jsonb  3   true  -- will yield jsonb    quot a quot   null   quot b quot   1 2 3       For inserting into JSON array  while preserving all of the original values   the jsonb insert   function can be used  in 9 6   this function only  in this section   SELECT jsonb insert    quot a quot   null   quot b quot   1         a 1 b 0    jsonb  2   -- will yield jsonb    quot a quot   null   quot b quot   2 1       and SELECT jsonb insert    quot a quot   null   quot b quot   1         a 1 b 0    jsonb  2   true  -- will yield jsonb    quot a quot   null   quot b quot   1 2       Full parameter list of jsonb insert    jsonb insert target       jsonb               path         text                 new value    jsonb               insert after boolean default false   Again  negative integers that appear in path count from the end of JSON arrays  So  f ex  appending to an end of a JSON array can be done with  SELECT jsonb insert    quot a quot   null   quot b quot   1 2         a 1 b -1    jsonb  3   true  -- will yield jsonb    quot a quot   null   quot b quot   1 2 3       and  However  this function is working slightly differently  than jsonb set    when the path in target is a JSON object s key  In that case  it will only add a new key-value pair for the JSON object when the key is not used  If it s used  it will raise an error  SELECT jsonb insert    quot a quot   null   quot b quot   1         a 1 c    jsonb   2    -- will yield jsonb    quot a quot   null   quot b quot   1   quot c quot   2       but SELECT jsonb insert    quot a quot   null   quot b quot   1         a 1 b    jsonb   2    -- will raise SQLSTATE 22023  invalid parameter value   cannot replace existing key  Deleting a key  or an index  from a JSON object  or  from an array  can be done with the - operator  SELECT jsonb    quot a quot  1  quot b quot  2   -  a   -- will yield jsonb    quot b quot  2          jsonb    quot a quot  1  quot b quot  2   - 1    -- will yield jsonb    quot a quot   quot b quot  2    Deleting  from deep in a JSON hierarchy can be done with the  - operator  SELECT    quot a quot   null   quot b quot   3 14       -   a 1 b 0   -- will yield jsonb    quot a quot   null   quot b quot          For 9 4  you can use a modified version of the original answer  below   but instead of aggregating a JSON string  you can aggregate into a json object directly with json object agg    Original answer  It is possible  without plpython or plv8  in pure SQL too  but needs 9 3   will not work with 9 2  CREATE OR REPLACE FUNCTION  quot json object set key quot      quot json quot           json     quot key to set quot     TEXT     quot value to set quot   anyelement     RETURNS json   LANGUAGE sql   IMMUTABLE   STRICT AS  function  SELECT concat      string agg to json  quot key quot              quot value quot               json   FROM  SELECT             FROM json each  quot json quot            WHERE  quot key quot   lt  gt   quot key to set quot           UNION ALL         SELECT  quot key to set quot   to json  quot value to set quot    AS  quot fields quot   function    SQLFiddle Edit  A version  which sets multiple keys  amp  values  CREATE OR REPLACE FUNCTION  quot json object set keys quot      quot json quot           json     quot keys to set quot    TEXT       quot values to set quot  anyarray     RETURNS json   LANGUAGE sql   IMMUTABLE   STRICT AS  function  SELECT concat      string agg to json  quot key quot              quot value quot               json   FROM  SELECT             FROM json each  quot json quot            WHERE  quot key quot   lt  gt  ALL   quot keys to set quot            UNION ALL         SELECT DISTINCT ON   quot keys to set quot   quot index quot                    quot keys to set quot   quot index quot                   CASE                  WHEN  quot values to set quot   quot index quot   IS NULL THEN  null   json                  ELSE to json  quot values to set quot   quot index quot                   END           FROM generate subscripts  quot keys to set quot   1  AS  quot keys quot   quot index quot             JOIN generate subscripts  quot values to set quot   1  AS  quot values quot   quot index quot            USING   quot index quot    AS  quot fields quot   function    Edit 2  as  ErwinBrandstetter noted these functions above works like a so-called UPSERT  updates a field if it exists  inserts if it does not exist   Here is a variant  which only UPDATE  CREATE OR REPLACE FUNCTION  quot json object update key quot      quot json quot           json     quot key to set quot     TEXT     quot value to set quot   anyelement     RETURNS json   LANGUAGE sql   IMMUTABLE   STRICT AS  function  SELECT CASE   WHEN   quot json quot  - gt   quot key to set quot   IS NULL THEN  quot json quot    ELSE  SELECT concat      string agg to json  quot key quot              quot value quot                        FROM  SELECT                     FROM json each  quot json quot                    WHERE  quot key quot   lt  gt   quot key to set quot                   UNION ALL                 SELECT  quot key to set quot   to json  quot value to set quot    AS  quot fields quot    json END  function    Edit 3  Here is recursive variant  which can set  UPSERT  a leaf value  and uses the first function from this answer   located at a key-path  where keys can only refer to inner objects  inner arrays not supported   CREATE OR REPLACE FUNCTION  quot json object set path quot      quot json quot           json     quot key path quot       TEXT       quot value to set quot   anyelement     RETURNS json   LANGUAGE sql   IMMUTABLE   STRICT AS  function  SELECT CASE COALESCE array length  quot key path quot   1   0           WHEN 0 THEN to json  quot value to set quot            WHEN 1 THEN  quot json object set key quot   quot json quot    quot key path quot  l    quot value to set quot            ELSE  quot json object set key quot               quot json quot               quot key path quot  l               quot json object set path quot                COALESCE NULLIF   quot json quot  - gt   quot key path quot  l    text   null           json                quot key path quot  l 1 u                 quot value to set quot                                 END   FROM array lower  quot key path quot   1  l         array upper  quot key path quot   1  u  function    Updated  Added function for replacing an existing json field s key by another given key  Can be in handy for updating data types in migrations or other scenarios like data structure amending  CREATE OR REPLACE FUNCTION json object replace key      json value json      existing key text      desired key text    RETURNS json AS  BODY  SELECT COALESCE        SELECT         string agg to json key            value                   FROM           SELECT           FROM json each json value          WHERE key  lt  gt  existing key         UNION ALL         SELECT desired key  json value - gt  existing key       AS  quot fields quot      -- WHERE value IS NOT NULL  Actually not required as the string agg with value s being null will  quot discard quot  that entry                  json  BODY    LANGUAGE sql IMMUTABLE STRICT   COST 100   Update  functions are compacted now

User · Answer

Even though the following will not satisfy this request  the function json object agg is not available in PostgreSQL 9 3   the following can be useful for anyone looking for a    operator for PostgreSQL 9 4  as implemented in the upcoming PostgreSQL 9 5   CREATE OR REPLACE FUNCTION jsonb merge left JSONB  right JSONB  RETURNS JSONB AS    SELECT   CASE WHEN jsonb typeof  1     object  AND jsonb typeof  2     object  THEN         SELECT json object agg COALESCE o key  n key   CASE WHEN n key IS NOT NULL THEN n value ELSE o value END   jsonb         FROM jsonb each  1  o         FULL JOIN jsonb each  2  n ON  n key   o key      ELSE        CASE WHEN jsonb typeof  1     array  THEN LEFT  1  text  -1  ELSE       1  text END                CASE WHEN jsonb typeof  2     array  THEN RIGHT  2  text  -1  ELSE  2  text      END   jsonb    END         LANGUAGE sql IMMUTABLE STRICT  GRANT EXECUTE ON FUNCTION jsonb merge jsonb  jsonb  TO public  CREATE OPERATOR      LEFTARG   jsonb  RIGHTARG   jsonb  PROCEDURE   jsonb merge

User · Answer

what do you think about this solution   It will add the new value or update an existing one  Edit  edited to make it work with null and empty object Edit2  edited to make it work with object in the object    create or replace function updateJsonb object1 json  object2 json  returns jsonb language plpgsql as    declare     result jsonb      tempObj1 text      tempObj2 text   begin     tempObj1   substr object1  text  2  length object1  text  - 2   --remove the first   and last       tempObj2   substr object2  text  2  length object2  text  - 2   --remove the first   and last        IF object1  text         and object1  text     null  and object1  text         THEN         result           tempObj1           tempObj2          jsonb      ELSE         result           tempObj2          jsonb      END IF      return result  end       usage  update table name set data   updatejsonb data     quot test quot    quot ok quot     json

User · Answer

If your field type is of json the following will work for you   UPDATE  table name SET field name   field name  jsonb -  key        key  new val    WHERE field name- gt  gt  key     old value     Operator  -  delete key value pair or string element from left operand  Key value pairs are matched based on their key value   Operator      concatenate two jsonb values into a new jsonb value   Since these are jsonb operators you just need to typecast to  jsonb  More info    JSON Functions and Operators   You can read my note here

User · Answer

So  for example my string looks like this     a1    a11   x   a22   y   a33   z     I update jsons by using temp table  which is good enough for rather small ammounth of data   lt 1 000 000   I found a different way  but then went on vacation and forgot it     So  the query will be something like this    with temp table as   select  a id  a- gt  a1 - gt  gt  a11  as  a11   a- gt  a1 - gt  gt  a22  as  a22   a- gt  a1 - gt  gt  a33  as  a33   u1 a11updated from foo a join table with updates u1 on u1 id   a id      update foo a     set a       a1     a11        t a11updated                a22       t a22                a33       t a33           jsonb     from temp table t     where t id   a id    It has more to do with string than json  but it works  Basically  it pulls all the data into temp table  creates a string while plugging concat holes with the data you backed up  and converts it into jsonb    Json set might be more efficient  but I m still getting a hang of it  First time I tried to use it  I messed up the string completely

User · Answer

For those who use mybatis  here is an example update statement    lt update id  saveAnswer  gt      update quiz execution set answer data   jsonb set answer data  concat        qid         text      value   jsonb   updated at     updatedAt      where id     id   lt  update gt     Params    qid  the key for field  value  is a valid json string  for field value  e g converted from object to json string via jackson

User · Answer

Sadly  I ve not found anything in the documentation  but you can use some workaround  for example you could write some extended function   For example  in Python   CREATE or REPLACE FUNCTION json update data json  key text  value json  returns json as    from json import loads  dumps if key is None  return data js   loads data  js key    value return dumps js     language plpython3u   and then  update test set data json update data   a   to json 5   where data- gt  gt  b     2

User · Answer

You can try updating as below  Syntax  UPDATE table name SET column name   column name  jsonb       quot key quot  new value   WHERE column name condition  For your example  UPDATE test SET data   data  jsonb       quot a quot  new value   WHERE data- gt  gt  b     2

User · Answer

This worked for me  when trying to update a string type field  UPDATE table name  SET body   jsonb set body    some key    to json  value   text   jsonb    Hope it helps someone else out  Assuming the table table name has a jsonb column named body and you want to change body some key    value

User · Answer

With PostgreSQL 9 4  we ve implemented the following python function  It may also work with PostgreSQL 9 3   create language plpython2u   create or replace function json set jdata jsonb  jpaths jsonb  jvalue jsonb  returns jsonb as    import json  a   json loads jdata  b   json loads jpaths   if a   class     name       dict  and a   class     name       list     raise plpy Error  The json data must be an object or a string     if b   class     name       list      raise plpy Error  The json path must be an array of paths to traverse     c   a for i in range 0  len b      p   b i    plpy notice  p        str p      if i    len b  - 1      c p    json loads jvalue     else      if p   class     name       unicode         plpy notice  Traversing      p              if c   class     name       dict           raise plpy Error    The value here is not a dictionary          else          c   c p       if p   class     name       int         plpy notice  Traversing     str p         if c   class     name       list           raise plpy Error    The value here is not a list          else          c   c p       if c is None        break      return json dumps a     language plpython2u     Example usage   create table jsonb table  jsonb column jsonb   insert into jsonb table values     cars    Jaguar     type   Unknown   partsList   12  34  56     Atom        select jsonb column- gt  cars - gt 1- gt  partsList - gt 2  jsonb column from jsonb table   update jsonb table set jsonb column   json set jsonb column     cars  1  partsList  2     99     select jsonb column- gt  cars - gt 1- gt  partsList - gt 2  jsonb column from jsonb table    Note that for a previous employer  I have written a set of C functions for manipulating JSON data as text  not as a json or jsonb type  for PostgreSQL 7  8 and 9  For example  extracting data with json path    obj   12  34    num  -45 67         obj 2   num      setting data with json path set    obj   12  34    num  -45 67         obj 2   num      99 87   and so on  It took about 3 days work  so if you need it to run on legacy systems and have the time to spare  it may be worth the effort  I imagine the C version is much faster than the python version

User · Answer

With Postgresql 9 5 it can be done by following-  UPDATE test SET data   data -  a        a  5   WHERE data- gt  gt  b     2     OR  UPDATE test SET data   jsonb set data    a     5   jsonb       Somebody asked how to update many fields in jsonb value at once  Suppose we create a table   CREATE TABLE testjsonb   id SERIAL PRIMARY KEY  object JSONB      Then we INSERT a experimental row   INSERT INTO testjsonb VALUES  DEFAULT     a   one    b   two    c    c1   see1   c2   see2   c3   see3         Then we UPDATE the row   UPDATE testjsonb SET object   object -  b        a  1  d  4      Which does the following    Updates the a field Removes the b field Add the d field   Selecting the data   SELECT jsonb pretty object  FROM testjsonb    Will result in         jsonb pretty -------------------------                                 a   1                     c                             c1    see1                c2    see2                c3    see3                                      d   4                   1 row    To update field inside  Dont use the concat operator     Use jsonb set instead  Which is not simple   UPDATE testjsonb SET object   jsonb set jsonb set object    c c1     seeme      c c2     seehim       Using the concat operator for  c c1  for example   UPDATE testjsonb SET object   object       c    c1   seedoctor        Will remove  c c2  and  c c3    For more power  seek power at postgresql json functions documentation  One might be interested in the  - operator  jsonb set function and also jsonb insert function

User · Answer

UPDATE table name SET attrs   jsonb set cast attrs as jsonb     key      quot new value quot    true  WHERE id    some id    This what worked for me  attrs is a json type field  first cast to jsonb then update  or UPDATE table name SET attrs   jsonb set cast attrs as jsonb     key      quot new value quot    true  WHERE attrs- gt  gt key    old value

User · Answer

I found previous answers are suitable experienced PostgreSQL users  hence my answer   Assume you have the a table-column of type JSONB with the following value          key0              key01    2018-05-06T12 36 11 916761 00 00            key02    DEFAULT WEB CONFIGURATION         key1              key11    Data System            key12     lt p gt Health  lt p gt my address lt p gt USA            key13     Please refer to main screen labeling            let s assume we want to set a new value in the row    key13     Please refer to main screen labeling    and instead place the value    key13    See main screen labeling    we use the json set   function to assign a new value to the key13  the parameters to jsonb set    jsonb set target jsonb  path text    new value jsonb   create missing boolean     in  target  - I will place the jsonb column-name  this is the table column that is being modified    path - is the  json keys path  leading-to  and including  the key that we are going to overwrite    new value  -  this is the new value we assign   in our case we want to update the value of key13 which resides under key1    key1 -  key13      hence the path syntax is      key1 key13    The path was the most tricky part to crack - because the tutorials are terrible   jsonb set jsonb column   key1 key13     See main screen labeling

User · Answer

You can also increment keys atomically within jsonb like this   UPDATE users SET counters   counters    CONCAT    bar     COALESCE counters- gt  gt  bar   0    int   1        jsonb WHERE id   1   SELECT   FROM users    id      counters ---- ------------   1     bar   1    Undefined key -  assumes starting value of 0   For more detailed explanation  see my answer here  https   stackoverflow com a 39076637

User · Answer

UPDATE test SET data   data  jsonb -  a        a  5    jsonb WHERE data- gt  gt  b     2    This seems to be working on PostgreSQL 9 5

User · Answer

With 9 5 use jsonb set-  UPDATE objects SET body   jsonb set body    name      Mary    true  WHERE id   1     where body is a jsonb column type

User · Answer

I wrote small function for myself that works recursively in Postgres 9 4  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

[json] How do I modify fields inside the new PostgreSQL JSON datatype?

Examples related to json

Examples related to postgresql

Examples related to postgresql-9.3

Examples related to postgresql-json