Geek Answers Handbook

How to update jsonb column field in PostgreSQL?

So, I wanted to try jsonbPostgreSQL. In my table, I have a column with a extrastype name jsonb.

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

I would like to change JSON only with sql statements. I want to do something like this:

Update param1fields extrasby adding 10 to its value if param2of extrasexceeds 12.

How can I write an SQL statement like this? I know that I can easily do this at the application level, but I would like to do it at the SQL level itself, since the number of rows that I could encounter would be huge, and I do not want to waste time on db-application - db roundtrip

+4
source share
2 answers

The type is jsonbdesigned to store entire documents. If you are changing any part of the document, you need to assign a new value to the column. Since Postgres has been keeping the old version for a while, this is an expensive operation.

With that in mind, here is an example of how not to update the columns 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;

Fingerprints:

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

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)
+4
source

More articles:


All Articles