current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 7 down vote favorite

I can't figure how can I update an element in a postgresql 9.3 datatype.

My exemple the db

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": "[email protected]",
        "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": "[email protected]",
        "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 that 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. Thanks for any help you could give me

share|improve this question

2 Answers 2

up vote 3 down vote

I think you will have to Update the complete field on Postgres9.3

share|improve this answer
    
Thanks frlan I was afraid that would be my only option ;) –  user1795779 Dec 11 '13 at 7:04
    
At least this is what documentation is also telling me. Maybe you can post on postgres-mailinglists –  frlan Dec 11 '13 at 8:08
1  
@user1795779: frlan is correct. Updating individual elements in a JSON document will be in 9.4 however (if I'm not mistaken) –  a_horse_with_no_name Apr 28 '14 at 13:12
up vote 1 down vote

For now though 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
share|improve this answer
    
Note, for all my JSON data, I keep an "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 –  M.Mugge Aug 11 '14 at 1:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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