I am using postgresql 9.4
I have one json column in the table and it contains the value:
"{
"title" : "risk",
"name" : "David"
}"
I have to perform two functionality
my final value should be in my table column
"{
"title" : "risk",
"name" : "John",
"city" : "paris"
}"
Basically, I want update and insert query for this json column and I can not change the database to higher version
smth like:
update table
set jbcolumn = json_build_object('title',jbcolumn->>'title','name','John','city','Paris') where bcolumn->>'name' = 'David
in postgres 9.4
https://www.postgresql.org/docs/9.4/static/functions-json.html
json_build_object(VARIADIC "any")
There is useful || operator for JSON type in the later versions of PostgreSQL. For 9.4 version you can to create it yourself:
create function my_json_cat(json, json) returns json stable strict language sql as $$
select json_object_agg(coalesce(x.key, y.key), coalesce(y.value, x.value))
from json_each($1) as x full join json_each($2) as y on (x.key = y.key)
$$;
create operator || (
leftarg=json, rightarg=json,
procedure=my_json_cat);
with t(x,y) as (values('{
"title" : "risk",
"name" : "David"
}'::json, '{
"title" : "risk",
"name" : "John",
"city" : "paris"
}'::json))
select x || y from t;
So your final statement could be
update your_table set
your_column = your_column || '{"name": "John", "city": "paris"}'
where ...
After upgrade to the later version of PostgreSQL just remove function and operator declarations from your DB script, nothing else should be changed.
