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