更新 json 數據類型中的 json 元素

我不知道如何更新 PostgreSQL 9.3 數據類型中的元素。

我的例子：

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;

“配置文件”中的 json 部分

{
   "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"
   }
}

我在前端使用 x-edit，我希望這樣的東西可以工作，但它沒有：

UPDATE public.user 
SET "profiles"->'Facebook'->'social'->'facebook' = 'test' WHERE` id='id'

我似乎找不到任何有關如何更新 json 數據類型的資訊。

由於它只是一個字元串，因此您可以使用該regex_replace函式完成對節點的簡單更改/刪除。

例如，這就是我最近刪除表中某個 JSON 節點（所有行）的方式：

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

請注意，對於我的所有JSON 數據，我"version":"(n).(n)"在對像中保留了一個（即模式版本）節點。這樣我就可以更新符合特定版本的對象。您的要求可能沒有那麼複雜，但如果是，它肯定會有所幫助。

嘗試這個

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

引用自：https://dba.stackexchange.com/questions/54663