Postgres update JSON field

Question

I've got several Postgres 9.4 tables that contain data like this:

| id | data                                      |
|----|-------------------------------------------|
| 1  | {"user": "joe", "updated-time": 123}      |
| 2  | {"message": "hi", "updated-time": 321}    |

I need to transform the JSON column into something like this

| id | data                                                         |
|----|--------------------------------------------------------------|
| 1  | {"user": "joe", "updated-time": {123, "unit":"millis"}}      |
| 2  | {"message": "hi", "updated-time": {321, "unit":"millis"}}    |

Ideally it would be easy to apply the transformation to multiple tables. Tables that contain the JSON key data->'updated-time' should be updated, and ones that do not should be skipped. Thanks!

Do you have any queries that you've tried? Where are you stuck exactly? With the right operators / functions to use for the JSON manipulation? With the basic SQL for updating a selection of rows based on a condition? — IMSoP, May 05 '16 at 13:51
The provided expected json is not valid, you could use `{"value": 123, "unit":"millis"}` or `[123, {"unit":"millis"}]`. — pozs, May 05 '16 at 13:51
Possible duplicate of [How do I modify fields inside the new PostgreSQL JSON datatype?](http://stackoverflow.com/questions/18209625/how-do-i-modify-fields-inside-the-new-postgresql-json-datatype) — pozs, May 05 '16 at 13:52
If I was you I would just write a python script to do the conversion — , May 06 '16 at 03:20

score 1 · Accepted Answer · answered May 05 '16 at 13:51

1

You can use the || operator to merge two jsonb objects together.

select '{"foo":"bar"}'::jsonb || '{"baz":"bar"}'::jsonb;
= {"baz": "bar", "foo": "bar"}

answered May 05 '16 at 13:51

amphetamachine

27,620
12
60
72

Postgres update JSON field

1 Answers1