CODEWITHSUNDEEP
sqlpostgresql
Mateusz Urbański
Mateusz Urbański

Reputation: 7862

Copy data from integer field to jsonb field

I have the following schema

CREATE TABLE survey_results (
    id integer NOT NULL,
    raw jsonb DEFAULT '{}'::jsonb,
    survey_id integer NOT NULL,
    created_at timestamp without time zone,
    updated_at timestamp without time zone  
);

INSERT INTO survey_results (id, survey_id, raw, created_at, updated_at)
    VALUES (1, 10, '{"survey": {}}', '2018-01-10', '2018-01-11');

INSERT INTO survey_results (id, survey_id, raw, created_at, updated_at)
    VALUES (2, 20, '{"survey": {}}', '2018-01-12', '2018-01-12');

I want to copy survey_id value to raw->survey->survey_id key. I've tried to do that in the following way but that didn't work:

UPDATE survey_results SET raw#>>'{survey, survey_id}' = survey_id;

Is there way of doing that in PostgreSQL?

http://sqlfiddle.com/#!17/ed50f/1

Upvotes: 0

Views: 45

Answers (1)

user330315
user330315

Reputation:

You need to use jsonb_set()

update survey_results 
   set raw = jsonb_set(raw, '{survey, survey_id}', to_jsonb(survey_id), true);

Online example: http://rextester.com/GTBTY13915

Upvotes: 1

Related Questions