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.

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

UPDATE my_db.my_table
SET my_column = (regexp_replace(my_column::text, ',"some_json_node":(.*),', ','))::json
WHERE NOT my_column IS NULL

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.

I think you will have to Update the complete field on Postgres 9.3, at least this is what the documentation is telling me.

Updating individual elements in a JSON document will be in 9.4 if I'm not mistaken.

Try This

UPDATE Tablename
SET columnname = replace(columnname::TEXT,'"name":','"my-other-name"')::jsonb 
WHERE id = 1;

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.