PostgreSQL UPSERT JSON属性

Question

我不确定这是否可能，但我正在尝试深入了解我可以在Postgres 9.6.1中做什么，这似乎可能是可能的。因此，考虑到这个表：

DROP TABLE IF EXISTS live_data;
CREATE TABLE live_data (
rec_id TEXT,
control_data JSONB
);
CREATE UNIQUE INDEX rec_id_idx ON live_data (rec_id);

我希望能够在不必更新整个json字符串的情况下，更新控制数据json中的单个属性。

由于表中还没有任何行，因此我尝试了以下操作：

INSERT INTO live_data(rec_id, control_data) VALUES ('1', '{"set":{"some_prop": 99}}')
ON CONFLICT (rec_id) DO UPDATE SET control_data->'set'->'some_prop' = 99;

就我个人而言，我在该查询中遇到了此错误：

syntax error at or near "->"

我写的查询语句有误吗？或者我想做的事情目前不可能实现吗？

- Rooster

Answer 1

使用jsonb_set()函数：

INSERT INTO live_data(rec_id, control_data) 
VALUES ('1', '{"set":{"some_prop": 99}}');

INSERT INTO live_data(rec_id, control_data) 
VALUES ('1', '{"set":{"some_prop": 88}}')
ON CONFLICT (rec_id) DO 
UPDATE SET control_data = 
    jsonb_set(live_data.control_data, array['set','some_prop'], '88', true)
RETURNING *;

 rec_id |        control_data        
--------+----------------------------
 1      | {"set": {"some_prop": 88}}
(1 row)

- klin

Answer 2

如果你的json列有以下值，

[
   {
      "Code":"xyz",
      "Amount":[
         {
            "Type":"Pay1",
            "Amount":"999",
            "username":"henry"
         },
         {
            "Type":"Pay2",
            "Amount":"499",
            "username":"rohilpatel",
            "Bonus":"100"
         }
      ],
      "Currency":"$"
   }
]

以下SQL查询将在指定路径添加键值对，如果已存在则更新。[Upsert的工作方式如下]

update tableName
SET  columnName = jsonb_set(columnName, '{0,Amount,1,Bonus}', '200') 

- Rohil Patel