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
– IMSoP, Commented May 5, 2016 at 13:51
The provided expected json is not valid, you could use {"value": 123, "unit":"millis"} or [123, {"unit":"millis"}]. — pozs
– pozs, Commented May 5, 2016 at 13:51
Possible duplicate of How do I modify fields inside the new PostgreSQL JSON datatype? — pozs
– pozs, Commented May 5, 2016 at 13:52
If I was you I would just write a python script to do the conversion — user827329
– user827329, Commented May 6, 2016 at 3:20

amphetamachine · Accepted Answer · 2016-05-05 13:51:37Z

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 5, 2016 at 13:51

amphetamachine

30.9k12 gold badges68 silver badges74 bronze badges

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

Collectives™ on Stack Overflow

Postgres update JSON field

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related