如何在PostgreSQL中更新jsonb列的字段?

问题描述

所以我想尝试PostgreSQL的jsonb.在我的表中，有一列名为jsonb的extras列.

extras中的样本数据看起来像{"param1": 10, "param2": 15}

我只想使用sql语句修改JSON.我想做这样的事情:

如果extras的param2超过12，则通过在其值上加10来更新extras字段的param1.

如何编写这样的SQL语句?我知道我可以轻松地在应用程序层中执行此操作，但是我想在SQL层本身中执行此操作，因为我可能要处理的行数将是巨大的，并且我不想浪费时间在db-application-数据库往返

解决方案

jsonb类型用于存储整个文档.如果您更改文档的任何部分，则需要为该列分配一个新值.因为Postgres将旧版本保留了一段时间，所以这是一项昂贵的操作.

牢记这一点，这是一个不更新jsonb列的示例:

create table t1 (doc jsonb);

insert into t1 values
    ('{"param1": 10, "param2": 15}'),
    ('{"param1": 10, "param2": 5}');

update  t1
set     doc = ('{"param1": ' ||
        ((doc->'param1')::text::int + 10)::text ||
        ', "param2": ' ||
        (doc->'param2')::text ||
        '}')::jsonb
where   (doc->'param2')::text::int > 12;

select * from t1;

此打印:

            doc
------------------------------
 {"param1": 10, "param2": 5}
 {"param1": 20, "param2": 15}
(2 rows)

So I wanted to try jsonb of PostgreSQL. In my table, I have a column called extras of jsonb type.

Sample data in extras looks like {"param1": 10, "param2": 15}

I would like to modify the JSON using sql statements only. I want to do something like this:

Update param1 of extras field by adding 10 to its value if param2 of extras exceeds 12.

How can I write a SQL statement like this? I know I can easily do this in the application layer but I would like to do this in the SQL layer itself as the number of rows I would be potentially dealing with would be huge and I do not want to waste time in db-application-db roundtrip

解决方案

The jsonb type is meant to store whole documents. If you change any part of the document, you'll need to assign a new value to the column. Because Postgres keeps the old version around for a while that is an expensive operation.

With that in mind, here's an example of how not to update jsonb columns:

create table t1 (doc jsonb);

insert into t1 values
    ('{"param1": 10, "param2": 15}'),
    ('{"param1": 10, "param2": 5}');

update  t1
set     doc = ('{"param1": ' ||
        ((doc->'param1')::text::int + 10)::text ||
        ', "param2": ' ||
        (doc->'param2')::text ||
        '}')::jsonb
where   (doc->'param2')::text::int > 12;

select * from t1;

This prints:

            doc
------------------------------
 {"param1": 10, "param2": 5}
 {"param1": 20, "param2": 15}
(2 rows)

这篇关于如何在PostgreSQL中更新jsonb列的字段?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！