CODEWITHSUNDEEP
sqlpostgresql
Mateusz Urbański
Mateusz Urbański

Reputation: 7862

PostgreSQL - Copying value from jsonb field have wrong format

I have the following schema:

CREATE TABLE survey_results (
    id integer NOT NULL,
    scores jsonb DEFAULT '{}'::jsonb
);

INSERT INTO survey_results (id, scores)
    VALUES (1, '{"total": 10}');

INSERT INTO survey_results (id, scores)
    VALUES (2, '{"total": 10}');

I want to copy value of total key to risk-score key which is done by the following query:

update survey_results set scores = jsonb_set(scores, '{risk-score}', to_jsonb(scores#>>'{total}'), true);

The problem is that the value copied is string instead of integer:

{"total": 10, "risk-score": "10"}

How can I fix that?

http://sqlfiddle.com/#!17/ea916/13

Upvotes: 1

Views: 355

Answers (3)

clemens
clemens

Reputation: 17721

You might create a second JSON dictionary and concatenate it with scores:

UPDATE survey_results SET scores = scores || FORMAT('{"risk-score":%s}', scores->>'total')::JSONB;

Upvotes: 1

klin
klin

Reputation: 121604

Use the -> operator:

update survey_results 
set scores = jsonb_set(scores, '{risk-score}', scores->'total', true);

SqlFiddle

Upvotes: 2

Jim Jones
Jim Jones

Reputation: 19623

Just change the operator to #>

update survey_results 
set scores = jsonb_set(scores, '{risk-score}', to_jsonb(scores#>'{total}'), true);

Upvotes: 2

Related Questions