How to update a jsonb column's field in PostgreSQL?

Question

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

Duplicate ? stackoverflow.com/questions/18209625/…

Clément Prévost
– Clément Prévost

2015-05-06 11:22:25 +00:00
Commented May 6, 2015 at 11:22 — Clément Prévost
– Clément Prévost, Commented May 6, 2015 at 11:22
Sort of. Thanks for the link.

Ranjith Ramachandra
– Ranjith Ramachandra

2015-05-06 14:21:29 +00:00
Commented May 6, 2015 at 14:21 — Ranjith Ramachandra
– Ranjith Ramachandra, Commented May 6, 2015 at 14:21

Mikael Lepistö · Accepted Answer · 2016-01-11 12:12:04Z

15

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)

answered Jan 11, 2016 at 12:12

Mikael Lepistö

19.8k4 gold badges73 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andomar · Accepted Answer · 2015-05-06 14:13:17Z

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)

I tried your approach. The performance is pretty bad IMO. Worse than what I would do with mongodb + application layer. I won't be using this for my app unless jsonb has modification techniques of its own that are better than the above approach. In any case, thanks for the answer.
Using string concatenation to modify jsonb seems a very bad idea. Also, in current PostgreSQL versions one can use the ->> operator, making casting to text unnecessary.

Collectives™ on Stack Overflow

How to update a jsonb column's field in PostgreSQL?

2 Answers 2

Comments

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Linked

Related