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->'a' from test where data->>'b' = '2';
 ?column?
----------
 1
(1 row)
postgres=# update test set data->'a' = to_json(5) where data->>'b' = '2';
ERROR:  syntax error at or near "->"
LINE 1: update test set data->'a' = to_json(5) where data->>'b' = '2...

Dermis

Answer 1

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 '{"a":1}' || jsonb '{"b":2}', -- will yield jsonb '{"a":1,"b":2}'
       jsonb '["a",1]' || jsonb '["b",2]'  -- will yield jsonb '["a",1,"b",2]'

So, setting a simple key can be done using:

SELECT jsonb '{"a":1}' || jsonb_build_object('<key>', '<value>')

Where <key> should be string, and <value> 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('{"a":[null,{"b":[]}]}', '{a,1,b,0}', jsonb '{"c":3}')
-- will yield jsonb '{"a":[null,{"b":[{"c":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 & 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('{"a":[null,{"b":[1,2]}]}', '{a,1,b,1000}', jsonb '3', true)
-- will yield jsonb '{"a":[null,{"b":[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('{"a":[null,{"b":[1]}]}', '{a,1,b,0}', jsonb '2')
-- will yield jsonb '{"a":[null,{"b":[2,1]}]}', and
SELECT jsonb_insert('{"a":[null,{"b":[1]}]}', '{a,1,b,0}', jsonb '2', true)
-- will yield jsonb '{"a":[null,{"b":[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('{"a":[null,{"b":[1,2]}]}', '{a,1,b,-1}', jsonb '3', true)
-- will yield jsonb '{"a":[null,{"b":[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('{"a":[null,{"b":[1]}]}', '{a,1,c}', jsonb '[2]')
-- will yield jsonb '{"a":[null,{"b":[1],"c":[2]}]}', but
SELECT jsonb_insert('{"a":[null,{"b":[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 '{"a":1,"b":2}' - 'a', -- will yield jsonb '{"b":2}'
       jsonb '["a",1,"b",2]' - 1    -- will yield jsonb '["a","b",2]'

Deleting, from deep in a JSON hierarchy can be done with the #- operator:

SELECT '{"a":[null,{"b":[3.14]}]}' #- '{a,1,b,0}'
-- will yield jsonb '{"a":[null,{"b":[]}]}'

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 "json_object_set_key"(
  "json"          json,
  "key_to_set"    TEXT,
  "value_to_set"  anyelement
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')::json
  FROM (SELECT *
          FROM json_each("json")
         WHERE "key" <> "key_to_set"
         UNION ALL
        SELECT "key_to_set", to_json("value_to_set")) AS "fields"
$function$;

SQLFiddle

Edit:

A version, which sets multiple keys & values:

CREATE OR REPLACE FUNCTION "json_object_set_keys"(
  "json"          json,
  "keys_to_set"   TEXT[],
  "values_to_set" anyarray
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')::json
  FROM (SELECT *
          FROM json_each("json")
         WHERE "key" <> ALL ("keys_to_set")
         UNION ALL
        SELECT DISTINCT ON ("keys_to_set"["index"])
               "keys_to_set"["index"],
               CASE
                 WHEN "values_to_set"["index"] IS NULL THEN 'null'::json
                 ELSE to_json("values_to_set"["index"])
               END
          FROM generate_subscripts("keys_to_set", 1) AS "keys"("index")
          JOIN generate_subscripts("values_to_set", 1) AS "values"("index")
         USING ("index")) AS "fields"
$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 "json_object_update_key"(
  "json"          json,
  "key_to_set"    TEXT,
  "value_to_set"  anyelement
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT CASE
  WHEN ("json" -> "key_to_set") IS NULL THEN "json"
  ELSE (SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')
          FROM (SELECT *
                  FROM json_each("json")
                 WHERE "key" <> "key_to_set"
                 UNION ALL
                SELECT "key_to_set", to_json("value_to_set")) AS "fields")::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 "json_object_set_path"(
  "json"          json,
  "key_path"      TEXT[],
  "value_to_set"  anyelement
)
  RETURNS json
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT CASE COALESCE(array_length("key_path", 1), 0)
         WHEN 0 THEN to_json("value_to_set")
         WHEN 1 THEN "json_object_set_key"("json", "key_path"[l], "value_to_set")
         ELSE "json_object_set_key"(
           "json",
           "key_path"[l],
           "json_object_set_path"(
             COALESCE(NULLIF(("json" -> "key_path"[l])::text, 'null'), '{}')::json,
             "key_path"[l+1:u],
             "value_to_set"
           )
         )
       END
  FROM array_lower("key_path", 1) l,
       array_upper("key_path", 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 <> existing_key
        UNION ALL
        SELECT desired_key, json_value -> existing_key
    ) AS "fields"
    -- WHERE value IS NOT NULL (Actually not required as the string_agg with value's being null will "discard" that entry)

),
    '{}'
)::json
$BODY$
  LANGUAGE sql IMMUTABLE STRICT
  COST 100;

Update: functions are compacted now.

Form

I tried your plpgsql function, but am not sure how to use it - I am seeing an error when I try select json_object_set_key((select data from test where data->>'b' = '2'), 'b', 'two'); error message is ERROR: could not determine polymorphic type because input has type "unknown" – Dermis

@Dermis Should be used as select json_object_set_key('{"b":2}', 'b', 'two'::text). Your problem is that 'two' has type unknown if you call it with a literal. Same with calling select to_json('two'). This function accepts any type as a value & converts to json with to_json(), but this has a cost that those type cannot be unknown. – Form

BTW, it's not plpgsql. It's pure SQL. – Form

Thanks @Form - this does work for me, and not needing Python/Perl/etc. is a bonus. – Dermis

This performs the equivalent of an UPSERT, not an UPDATE. If the key does not exist in the json field yet, it is added. Look to this related question for an actual UPDATE: #7711932 (This is for a composite type, but the principal is similar for json.) – Torey

@ErwinBrandstetter that is true, but in json an UPSERT usually more general than an UPDATE-like modification (consider f.ex. sqlfiddle.com/#!15/d41d8/2897 ) -- I interpreted the original question as how do you modify them (json columns) using an UPDATE statement? -- besides a single condition could transform this to UPDATE. – Form

Very useful and complete now. – Torey

This is quite a useful set of functions to have, and obviously took a lot of skill and thought to write. Many thanks, @pozs! – Electrodynamics

Nice functions, can they be used for complex objects? E.g. select json_object_set_key('{"A":{"a":1},"B":{"b":2}}'::json, '{"B"->"b"}', 'two'::text); does not alter the B->b key... – Dermis

@Dermis key here is just a simple string (text) key, not a complex key path. This function however can be used for what you want, but you must apply it twice, like: select json_object_set_key(j, 'B', json_object_set_key(j->'B', 'b', 'two'::text)) from values('{"A":{"a":1},"B":{"b":2}}'::json) v(j) (I know, it's far from optimal, but with this pattern, a recusive function could be created) – Form

@Form Thanks but I need a general solution, think I'll bite the bullet and use the python extension. – Dermis

i hope this would be built-in feature for postgresql 9.4 '___') – Mann

How do you use this with sql update? 'UPDATE entry SET data = json_object_set_key(data, $1, $2)' doesn't work - throws this error: could not determine polymorphic type – Bottomry

@maxhud that depends on the client (or client library you use). If you can, use explicit types (PostgreSQL can guess types in parametrized queries, but that usually does not work well with polymorphic functions). But at least, you can use explicit casts, like $2::text. – Form

thanks - @Form might be nice to put this in the answer! If anyone else needs it, this works with PHP: pg_query_params("UPDATE entry SET data = json_object_set_key(data, $1::text, $2::json)", array($key, $value)) – Bottomry

@Form can we have one for deleting a key path please? – Miserable

@RezaS you can create one, using the recursive variant (json_object_set_path), just use a the delete function from my other answer ( https://mcmap.net/q/88065/-postgresql-remove-attribute-from-json-column ) in the WHEN 1 case (and you can use something like 'null'::json in the WHEN 0 case, if you want to supply something meaningful, when called with an empty key-path (and obviously, you don't need the value_to_set parameter). – Form

@Form Thanks man, I did end up doing that, thought slightly different. I created json_object_del_key and json_object_del_path will be posting the SQL's to this answer shortly – Miserable

updating certain values in an array should also be added here. – Kremlin

@Form Thanks so much! you saved my day. I was stuck with Postgres v9.4 so I tried your scripts and it works like charm! – Holarctic

@Form hi, Do you see why the following return nothing, when there is 100% a key called currency? Customer.where(id: 52).select("metadata::jsonb - 'currency'"). If I just go with Customer.where(id: 52).select("metadata::jsonb") it returns the whole hash: [#<Customer:0x007fe02144f800 id: nil, metadata: {"currency"=>"EUR", "api_enabled"=>false, "scenario_ids"=>[3, 4], "system_patching_enabled"=>false}>] – Borkowski

@AndreyDeineko I'm not that familiar with ruby, but if the result will be a Customer instance, then maybe you should use a column alias too, like Customer.where(id: 52).select("metadata::jsonb - 'currency' AS metadata") -- without an alias, the column will have an arbitrary name if its value is calculated. – Form

hi, what if json data has array in it ? ex. {"category":{"color":[{blue},{yellow}]}} @ErwinBrandstetter – Hage

@johnigneel "paths" in jsonb_set() can contain JSON array indexes too. Also, for 9.6+, there is a dedicated jsonb_insert() function, if you want to expand the array (good question though, I'll update the answer soon). -- For earlier versions, it would be rather complicated to introduce array index support (but in theory, it's possible). – Form

@Form jsonb_set() sounds good but sad I'm using earlier version of postgres (9.3) I'm wondering how could it be implemented inside a function to update within the array in a json. – Hage

Answer 2

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.

Hampstead

Hi, why I can't use upper like that: update objects set body=jsonb_set(body, '{name}', upper('"Mary"'), true) where id=1; it doesn't recognize, or how can I achive the same behavior? thx – Trona

If the value I want to set is a substring from another column rather than "Mary", how would I do it? – Faro

@Andrew, you'd use to_jsonb(substring(column_name, 1, 2)) to convert the column value to jsonb. So, update objects set body=jsonb_set(body, '{name}', to_jsonb(upper(name_column)), true);. – Roxanaroxane

Answer 3

2023 Aug update:

Along with information below, see Kouber Saparev answer for postgresql 14.

---

With Postgresql 9.5 it can be done by following-

UPDATE test
SET data = data - 'a' || '{"a":5}'
WHERE data->>'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:

1. Updates the a field
2. Removes the b field
3. 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.

Nihon

and if i have to update two fields then what is the syntax? – Husch

if I have a json column with field name, how do I add lastname field to this column – Magistrate

Should be clear: UPDATE users SET profile = profile || '{"lastname":"Washington"}' WHERE profile->>'name' = 'George Washington'; – Nihon

Answer 4

Since PostgreSQL 14 you can use jsonb subscripting to access directly the elements of the JSON field and eventually update them.

UPDATE test SET data['a'] = '5' WHERE data['b'] = '2';

Subjugate

Answer 5

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->>'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

Wipe

Simple & better way to update JSON fields, if you're not worried about the property order rearrangements. – Ekaterinodar

Answer 6

UPDATE test
SET data = data::jsonb - 'a' || '{"a":5}'::jsonb
WHERE data->>'b' = '2'

This seems to be working on PostgreSQL 9.5

Atom

Works for me, as far as I have understood, this remove the field "a" from data and then append the field "a" with the new value. In my case, the value of "a" was based on a column. UPDATE test SET data = data::jsonb - 'a' || ('{"a":"'|| myColumn || '"}')::jsonb; – Marmite

Answer 7

I found previous answers more suitable for experienced PostgreSQL users. This one is for the beginners:

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": "<p>Health,<p>my address<p>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"')

Bagley

Answer 8

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'

Chari

unfortunately this reformats JSON the same way as manipulations through the JSON-specific functions – Soninlaw

Answer 9

You can try updating as below:

Syntax: UPDATE table_name SET column_name = column_name::jsonb || '{"key":new_value}' WHERE column_name condition;

For your example:

UPDATE test SET data = data::jsonb || '{"a":new_value}' WHERE data->>'b' = '2';

Hartzog

Answer 10

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" -> "key_to_del") IS NULL THEN "json"
  ELSE (SELECT concat('{', string_agg(to_json("key") || ':' || "value", ','), '}')
          FROM (SELECT *
                  FROM json_each("json")
                 WHERE "key" <> "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" -> "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" -> "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"}}

Enteric

Answer 11

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->'cars'->1->'partsList'->2, jsonb_column from jsonb_table;

update jsonb_table
set jsonb_column = json_set(jsonb_column, '["cars",1,"partsList",2]', '99');

select jsonb_column->'cars'->1->'partsList'->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.

Ratha

Answer 12

You can also increment keys atomically within jsonb like this:

UPDATE users SET counters = counters || CONCAT('{"bar":', COALESCE(counters->>'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

Furfur

It works very well on Postgres 14.1 – Dispermous

Answer 13

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 );

Kauffmann

Answer 14

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->v.key) = 'object'
            THEN
                result = result || jsonb_build_object(v.key, jsonb_update(val1->v.key, val2->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.

Calumet

Answer 15

This solution is an alternate to jsonb_set that works even if the column has NULL in it for the JSONB. The jsonb_set only works if the object exists.

In the example below, settings is a JSONB column on the Users Table.

 UPDATE public."Users"
 SET settings = coalesce("settings", '{}')::jsonb || '{ "KeyToSet" : "ValueToSet" }'
 WHERE id=35877;

Segovia

Answer 16

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->>'b' = '2';

Bennion

It's a shame Amazon RDS doesn't support plpython3u! – Capping

The value will also require a loads when setting non numeric values like strings (js[key] = loads(value)) - Otherwise: select json_update('{"a":"a"}', 'a', to_json('b')); -> {"a": "\"b\""} – Bowlder

This answer could also be modified to include deleting a key when value is set to None: `if value is None: del data[key] – Finnougric

Answer 17

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';

Splitting

Answer 18

UPDATE table_name SET attrs = jsonb_set(cast(attrs as jsonb), '{key}', '"new_value"', 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}', '"new_value"', true) WHERE attrs->>key = 'old_value';

Suzy

Answer 19

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, '{"test": "ok"}'::json)

Gareri

I think this implementation has high potential for security vulnerability. If attacker can affect the contents of tempObj1 or tempObj2 to include quotation marks he or she can modify the structure of the objects by having suitable values in the strings because the code re-interprets the resulting string as jsonb. – Uriia

Answer 20

If you want to add new fields as well you may try:

typeorm code

let keyName:string = '{key2}'
let valueName:string = '"new_value"'

emailLog: () => "jsonb_set(cast(email_log as jsonb), '" + keyNAme + "','" + valueName + "'," + "true" + ")"

Shanaeshanahan

Answer 21

For those who use mybatis, here is an example update statement:

<update id="saveAnswer">
    update quiz_execution set answer_data = jsonb_set(answer_data, concat('{', #{qid}, '}')::text[], #{value}::jsonb), updated_at = #{updatedAt}
    where id = #{id}
</update>

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,

Spence

Answer 22

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 amount of data (<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->'a1'->>'a11' as 'a11',
a->'a1'->>'a22' as 'a22',
a->'a1'->>'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...

Ratite

hi and welcome to StackOverflow! Note that there is already an accepted answer to this question. – Flemings

Note that this method has potential for security vulnerabilities depending on the content of the fields because you would need to encode the data as JSON strings or attacker can manipulate your data structure. – Uriia

Answer 23

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' || ('{"key": ' || column2::text ||  '}')::jsonb
where ...;

Burrstone

Answer 24

select * from pg_settings where name = 'deadlock_timeout';
begin;
create temp table a2(data jsonb);
insert into a2 values('{
    "key0": {
        "key01": "2018-05-06T12:36:11.916761+00:00",
        "key02": "DEFAULT_WEB_CONFIGURATION",
    "key1": {
        "key11": "Data System",
        "key12": "<p>Health,<p>my address<p>USA",
        "key13": "*Please refer to main screen labeling"
    }
}}'::jsonb);
commit;

nested jsonb structure update. can be applied to delete.

update a2 set data =
    data::jsonb #-  '{key0, key1, key13}'
        || '{"key13":"screen labeling"}'::jsonb
returning *;

Insubstantial