How to update a jsonb column's field in Postgr

问题:

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

回答1:

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)

回答2:

This should do it with PostgreSQL 9.5:

create table t (extras jsonb);
insert into t values
    ('{"param1": 10, "param2": 15}'),
    ('{"param1": 10, "param2": 5}');

UPDATE t 
  SET extras = jsonb_set(extras, '{param1}', ((extras->>'param1')::real + 10)::text::jsonb) 
  WHERE (extras#>>'{param2}')::real > 12;

select * from t;
            extras            
------------------------------
 {"param1": 10, "param2": 5}
 {"param1": 20, "param2": 15}
(2 rows)