The document says:
jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])
In your example, the second parameter - '{k2,value}' is the search path, but since the first path is k2 that does not exist, results to NULL before value could be added/replaced.
In simple terms – jsonb_set is not meant to build entire JSON document the way you tried using search path, but to add or replace single key/value.
If you want to add/replace entirely new set of JSON like that you can use || (concatenate) operator instead like that:
-- Add example:
SELECT $${"k1": {"value": "v1"}}$$::jsonb || $${ "k2": { "value": "v2"}}$$::jsonb;
?column?
------------------------------------------------
{"k1": {"value": "v1"}, "k2": {"value": "v2"}}
(1 row)
-- Replace example
SELECT $${"k1": {"value": "v1"}}$$::jsonb || $${ "k1": { "value": "v2"}}$$::jsonb;
?column?
-------------------------
{"k1": {"value": "v2"}}
(1 row)
OR
You can use jsonb_set() like that instead:
SELECT JSONB_SET(
'{"k1": {"value": "v1"}}',
'{k2}',
'{"value": "v2"}',
TRUE
);
jsonb_set
------------------------------------------------
{"k1": {"value": "v1"}, "k2": {"value": "v2"}}
(1 row)
The document says:
jsonb_set(target jsonb, path text[], new_value jsonb[, create_missing boolean])
In your example, the second parameter - '{k2,value}' is the search path, but since the first path is k2 that does not exist, results to NULL before value could be added/replaced.
In simple terms – jsonb_set is not meant to build entire JSON document the way you tried using search path, but to add or replace single key/value.
If you want to add/replace entirely new set of JSON like that you can use || (concatenate) operator instead like that:
-- Add example:
SELECT $${"k1": {"value": "v1"}}$$::jsonb || $${ "k2": { "value": "v2"}}$$::jsonb;
?column?
------------------------------------------------
{"k1": {"value": "v1"}, "k2": {"value": "v2"}}
(1 row)
-- Replace example
SELECT $${"k1": {"value": "v1"}}$$::jsonb || $${ "k1": { "value": "v2"}}$$::jsonb;
?column?
-------------------------
{"k1": {"value": "v2"}}
(1 row)
OR
You can use jsonb_set() like that instead:
SELECT JSONB_SET(
'{"k1": {"value": "v1"}}',
'{k2}',
'{"value": "v2"}',
TRUE
);
jsonb_set
------------------------------------------------
{"k1": {"value": "v1"}, "k2": {"value": "v2"}}
(1 row)
I just faced the same issue and created a simple postgres-function for it. It may not be the fastest solution and might especially be slow for deep inserts, but so far works fine for me!
CREATE OR REPLACE FUNCTION jsonb_deep_set(curjson jsonb, globalpath text[], newval jsonb) RETURNS jsonb AS
$$
BEGIN
IF curjson is null THEN
curjson := '{}'::jsonb;
END IF;
FOR index IN 1..ARRAY_LENGTH(globalpath, 1) LOOP
IF curjson #> globalpath[1:index] is null THEN
curjson := jsonb_set(curjson, globalpath[1:index], '{}');
END IF;
END LOOP;
curjson := jsonb_set(curjson, globalpath, newval);
RETURN curjson;
END;
$$
LANGUAGE 'plpgsql';
Relacing jsonb with json makes it also work for json objects.
The documentation says:
The || operator concatenates the elements at the top level of each of its operands. ... For example, if both operands are objects with a common key field name, the value of the field in the result will just be the value from the right hand operand.
So using your example data:
update table_name set
data = data || '{"city": "ottawa", "phone": "phonenum", "prefix": "prefixedName"}'
where number = 1;
Additionally if the object you want to edit is not at the top level - just combine the concatenation and jsonb_set function. For example, if the original data looks like
{"location": {"name": "firstName", "city": "toronto", "province": "ON"}}
then
...
data = jsonb_set(
data,
'{location}', data->'location' || '{"city": "ottawa", "phone": "phonenum", "prefix": "prefixedName"}')
...
You can try this
Here we are using jsonb concatation operator || to Concatenate two jsonb objects
update table_name set data = (select val from (
(select
CASE WHEN data ? key THEN jsonb_set(data, '{' || key || '}', quote_nullable(updated_value))
ELSE
data || ('{' || quote_ident(key) || ':' || quote_ident(some_value) || '}')::jsonb
END val
from json_each_text((select data::json from tbl))
CROSS JOIN tbl t
where key in ('city','phone','prefix') and number=1)) where number=1
There are two ways to accomplish this:
Simply concatenating the new key/value pair:
update the_table
set attr = attr || '{"is_default": false}';
This works because when concatenating two jsonb values, existing keys will be overwritten.
The other way is to use jsonb_set() that updates a value at a location specified through a "path" (which is defined through the elements of an array)
update the_table
set attr = jsonb_set(attr, array['is_default'], to_jsonb(false));
If you're on version 14 (released September 2021) or greater, you can simplify this to:
update the_table
set attr['is_default'] = to_jsonb(false);
Let's say we got this jsonb
{
"animals": [
{"name": "Cat", "age": 5},
{"name": "Dog", "age": 10}
]
}
We want to update the age of Cat from 5 to 15. The function jsonb_set(...) returns a new jsonb with updated value.
select
jsonb_set(
'{"animals": [{"name": "Cat", "age": 5}, {"name": "Dog", "age": 10}]}',
'{animals,0,age}', -- Path to the field to update
'15'::jsonb, -- New value to set
false -- If true, this will make a new key if it doesn't exist in the jsonb
)
;
If you would like to do something like that in an SQL update, this would go like;
update animals
set
animals_metadata = jsonb_set(animals_metadata, '{animals,0,age}', to_jsonb(15), false)
where id = '123';
Check out the official documentation for more!