I can't figure how can I update an element in a PostgreSQL 9.3 datatype.
My example:
CREATE TABLE "user"
(
id uuid NOT NULL,
password character varying(255),
profiles json,
gender integer NOT NULL DEFAULT 0,
created timestamp with time zone,
connected timestamp with time zone,
modified timestamp with time zone,
active integer NOT NULL DEFAULT 1,
settings json,
seo character varying(255) NOT NULL,
CONSTRAINT id_1 PRIMARY KEY (id)
)
WITH (
OIDS=TRUE
);
ALTER TABLE "user"
OWNER TO postgres;
The json part in "profiles"
{
"Facebook": {
"identifier": "xxxxxxxxxxx",
"profileURL": "none",
"webSiteURL": "none",
"photoURL": "none",
"displayName": "test2 test2",
"description": "none",
"firstName": "test2",
"lastName": "test2",
"gender": 2,
"language": "none",
"age": "none",
"birthDay": "none",
"birthMonth": "none",
"birthYear": "none",
"email": "test@test.com",
"emailVerified": "none",
"Added": null,
"phone": "none",
"address": "none",
"country": "none",
"region": "none",
"city": "none",
"zip": "none"
},
"Google": {
"identifier": "xxxxxxxxxxxxxxxxxxxxxx",
"profileURL": "none",
"webSiteURL": "none",
"photoURL": "none",
"displayName": "test2 test2",
"description": "none",
"firstName": "test2",
"lastName": "test2",
"gender": 2,
"language": "none",
"age": "none",
"birthDay": "none",
"birthMonth": "none",
"birthYear": "none",
"email": "test@test.com",
"emailVerified": "none",
"Added": null,
"phone": "none",
"address": "none",
"country": "none",
"region": "none",
"city": "none",
"zip": "none"
}
}
I'm using x-edit for the frontend, and I was hoping that something like this would work, but it doesn't:
UPDATE public.user
SET "profiles"->'Facebook'->'social'->'facebook' = 'test' WHERE` id='id'
I can't seem to find any information on how to update a json datatype.
Best Answer
Since it's just a string, you might be able to accomplish a simple change/deletion of a node with the
regex_replace
function.For example, this is how I recently deleted a certain JSON node in a table (all rows):
Note, for all my JSON data, I keep a
"version":"(n).(n)"
(i.e. schema version) node in the object. That way I can update objects that comply with a specific version. Your requirements may not be that complex, but if they are, it certainly helps.