12

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

Ranjith Ramachandra
  • 10,399
  • 14
  • 59
  • 96

2 Answers2

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)
Mikael Lepistö
  • 18,909
  • 3
  • 68
  • 70
0

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)
Andomar
  • 232,371
  • 49
  • 380
  • 404
  • 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. – Ranjith Ramachandra May 06 '15 at 14:22
  • 1
    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. – mbork May 16 '19 at 13:07