部分更新PostgreSQL中的JSON字段

Question

在Postgres中，我有一个像这样的表：

CREATE TABLE storehouse
(
  user_id bigint NOT NULL,
  capacity integer NOT NULL,
  storehouse json NOT NULL,
  last_modified timestamp without time zone NOT NULL,
  CONSTRAINT storehouse_pkey PRIMARY KEY (user_id)
)

而 storehouse.storehouse 存储的数据如下：

{
    "slots":[
        {
            "slot" : 1,
            "id" : 938
        },
        {
            "slot" : 2,
            "id" : 127
        },
    ]
}

事情是这样的，我想更新storehouse.storehouse.slots [2]，但我不知道如何做到。

我知道如何更改整个storehouse.storehouse字段，但我在想，由于Postgres支持json类型，它应该支持部分修改，否则json类型和text类型之间没有区别。（我知道json类型还具有类型验证，与text不同）

- bxshi

Answer 1

目前不支持JSON索引和部分更新。PostgreSQL 9.2中的JSON支持基本，仅限于验证JSON并将行和数组转换为JSON。在内部，json实际上就是text。

正在进行增强工作，如部分更新、索引等。但无论如何，当JSON值的一部分更改时，PostgreSQL都无法避免重写整个行，因为这是并发MVCC模型固有的。使其成为可能的唯一方法是将JSON值拆分成多个元组放入侧面关系表中，例如TOAST表 - 这是可能的，但很可能性能很差，并且到目前为止远未被考虑。

正如Chris Travers所指出的，您可以使用PL/V8函数或其他语言（如Perl或Python）中具有json支持的函数提取值，然后在这些函数上创建表达式索引。

- Craig Ringer

---

我只想补充一点，使用plv8js编写的函数可能是管理更新过程的好方法。也许可以在plperlu中编写一个窄函数来完成相同的操作。 - Chris Travers

最新的Postgres确实支持这个功能，请看下面的答案。 - Radical Ed

Answer 2

自从PostgreSQL 9.5版本以来，有一个名为jsonb_set的函数，它接受以下输入参数：

• JSON对象
• 指示路径（键和子键）的数组
• 要存储的新值（也是JSON对象）

例子：

 # SELECT jsonb_set('{"name": "James", "contact": {"phone": "01234 567890", "fax": "01987 543210"}}'::jsonb,
            '{contact,phone}',
            '"07900 112233"'::jsonb);
                                  jsonb_replace                                  
 --------------------------------------------------------------------------------
  {"name": "James", "contact": {"fax": "01987 543210", "phone": "07900 112233"}}
 (1 row)

- augustomen