PostgreSQL updating many keys in jsonb field.

Question

In my PostgreSQL database I have the following schema:

CREATE TABLE survey_results (
    id integer,
    data jsonb DEFAULT '{}'::jsonb
);

INSERT INTO survey_results (id, data)
    VALUES (1, '{"user": {}, "survey": {}}');

INSERT INTO survey_results (id, data)
    VALUES (2, '{"user": {}, "survey": {}}');

I want to update all records in survey_results table to have the following values in data column:

{"user":{"dob": '1995'},"survey":{"id": '1234'}}

How can I do that? I tried to do that with jsonb_set but I was not able to set all keys. Any ideas?

Here is sqlfiddle:

https://www.db-fiddle.com/f/i49SiaQn6qWcwiWDxVbHnn/8

this would be sooo easy with a properly normalized data model. — user330315
– user330315, Commented May 7, 2018 at 13:48

user330315 · Accepted Answer · 2018-05-07 13:59:55Z

1

I assume you oversimplified your question, because with your sample data you can achieve what you want by simply overwriting the value in the column:

update survey_results
  set data = '{"user": {"dob": 1995}, "survey": {"id": 1234}}'::jsonb

If you want to preserve potential other keys in the JSON document, and only update those two key, you need to nest the jsonb_set() calls for each key you want to change:

update survey_results
  set data = jsonb_set(jsonb_set(data, '{user}', '{"dob": 1995}', true), '{survey}', '{"id": 1234}', true);

answered May 7, 2018 at 13:59

user330315

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

Collectives™ on Stack Overflow

PostgreSQL updating many keys in jsonb field.

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related