Update Postgresql table straight from a JSON object

Question

I use

select to_json(host) from host where id=3

to query data from my postgreSQL database the result is like:

{"id":3,"project_id":1,"name":"a","mac":"abc","info":"x"}

after altering the data in my application I want to update the table.

Is there a "json"-way to do this? Not doing a ordinary update like

update host set project_id=1, name='a', mac='abc',info='x' where id=1;

sure, but I want to know if there is a way to use the json directly in the update statement — user2071938
– user2071938, Commented Feb 16, 2017 at 12:48
Yes, that's possible. But without more details this is impossible to answer. Please Edit your question and add some sample data and the expected output based on that data. Formatted text please, no screen shots — user330315
– user330315, Commented Feb 16, 2017 at 13:20
There is no way you can avoid the set column = value syntax. UPDATE simply doesn't support that. — user330315
– user330315, Commented Feb 16, 2017 at 13:43

Clodoaldo Neto · Accepted Answer · 2017-02-16 15:28:31Z

9

Use jsonb_populate_record in the update from clause

update host
set
    (project_id, name, mac, info) =
    (j.project_id, j.name, j.mac, j.info)
from jsonb_populate_record ( null::host,
    '{"id":1,"project_id":1,"name":"a","mac":"abc","info":"x"}'::jsonb
) j
where host.id = j.id

edited Feb 16, 2017 at 15:28

answered Feb 16, 2017 at 14:41

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

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

2 Comments

Jay J Over a year ago

Just wondering why jsonb_populate_record over json_populate_record? Also, can either json update function somehow ignore a null field in the JSON, i.e. don't update if JSON is null?

Jan Sommer Over a year ago

A little late to the party, but in case someone else needs to remove null fields, check out jsonb_strip_nulls postgresql.org/docs/current/functions-json.html

Collectives™ on Stack Overflow

Update Postgresql table straight from a JSON object

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related