postgresql：在一个查询中更新多个jsonb键

Question

我有以下postgresql行作为JSONB行：

{age:26}

我想把它替换成这样的i：

{age: 30, city: "new york city"}

我该如何在PostgreSQL中实现这个？有人提到可以使用jsonb_set()，但我还没有看到任何同时更新多个键的示例。

- ApathyBear

---

postgres jsonb_set 多个键更新 - Abelisto

Answer 1

在 jsonb_set() 中使用 jsonb_set()

jsonb_set(jsonb_set('{age:26}'::jsonb,'{age}','"30"'::jsonb)::jsonb,'{city}','"new york city"'::jsonb)

- SHAKU

---

在这里提到的更新多个键的标准方法[https://dev59.com/C1kT5IYBdhLWcg3wPdKU]对我没有帮助，而这个答案对我的情况非常完美，因为我需要更新多个键，其中一个是普通值，另一个是jsonb数组。 - uLan

Answer 2

虽然你可以嵌套使用jsonb_set操作，但这样会变得非常难以阅读。

相反，您可以使用jsonb_object。

SET my_column = my_column || jsonb_object(
    array[ -- keys
        'age',
        'city',
        'visits'
    ],
    array[ -- values
        31::text,
        'Johannesburg'::text,
        ((my_column#>>'{visits}')::int + 1)::text -- increment
    ]
)

注意：由于它只处理文本字段，因此您将失去类型安全性，但是您可以进行部分更新（仅添加要更新的字段），如果您从另一种语言处理此问题，则通常很容易编程，前提是您的SQL抽象化不太规范。

- WiR3D

---

这对于文本值非常好用，但它不能处理JSON值。 - anakha

是的，您会失去类型安全。但即使是对于计数器，我发现它只是一个小的不便。这使我可以使用一个列来动态添加新的可变统计数据，而无需进行模式更新。 - WiR3D

Answer 3

PostgreSQL非常优秀。你还可以使用字符串连接运算符||

UPDATE wooden_table
SET doc = doc
    || '{"color" : "red"}' 
    || '{"hardness" : "1H"}';

该方法也适用于值内部包含 JSON 值的情况。

- anakha

Answer 4

使用SQLAlchemy：

from sqlalchemy import func, and_, any_, cast
from sqlalchemy.dialects.postgresql import JSONB


db.session.query(Model).filter(
    Model.id == any_(ids)
).update({
    Model.your_jsonb_field: cast(
        Model.your_jsonb_field,
        JSONB,
    ).concat(
        func.jsonb_build_object('key1', 'value1'),
    ).concat(
        func.jsonb_build_object('key2', 'value2'),
    )
}, synchronize_session='fetch')

- Nikolay Baluk

Answer 5

你可以使用jsonb_build_object。

jsonb_build_object('age', 30, 'city', 'new york city')

- jqgsninimo

Answer 6

 UPDATE tablename
 SET name = ?,
 jsonCOl = jsonCol::jsonb || '{"color" : "red"}'::jsonb
 WHERE id = ?

这个解决方案非常好，因为它将保存的JSON和覆盖的JSON都强制转换为JSONB，并通过替换右侧||运算符的所有键来保存它。

- Abdul Samea

Answer 7

在更新数据时，您可以使用jsonb列，并从您拥有的属性创建json格式数据，执行jsonb操作时需要记住以下四点：

1. 将第一个参数传递给jsonb_set函数作为目标（您要替换的位置）
2. json 键
3. json 值
4. 如果是新列，则设置为true

select jsonb_set(jsonb_set('{}'::jsonb,'{age}','30',true)::jsonb,'{city}',to_jsonb('hyd'::text),true)::jsonb;

- Shashi555