current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 11 down vote favorite
1

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
up vote 4 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 – Mike Mügge Aug 11 '14 at 1:57
    
Brilliant! Thanks a lot! – Lukasz Wiktor Nov 18 at 15:41
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

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.