How to use scalar to copy value from one field to another?

user_9223327 0
Viewed 26

I have table with jsonb column "Details":

{"Type": "VIP", "Quantity": 1}

I want update column, using query that copies value from "Type" field to new one "Subscription" and change value "VIP"->2. Exp below:

{"Type": 2, "Quantity": 1, "Subscription": "VIP"}

I have tried script but received error "Can't extract scalar value"

Update "Media" set "Details" = jsonb_set("Details", '{Subscription}'::text[],
            (select res.value from jsonb_array_elements("Details" -> 'Type') as res), true)

What is wrong? Please, help to find a solution.

jsonb postgresql sql
2 Answers

Use the concatenation operator:

update "Media" 
set "Details" = 
    "Details" || 
    jsonb_build_object('Subscription', "Details"->'Type') || 
    '{"Type": 2}';

Test it in db<>fiddle.

According to the documentation:

Concatenating two objects generates an object containing the union of their keys, taking the second object's value when there are duplicate keys.

Thus when you want to modify a value of a key, you can concatenate the key with a new value.

user_19957380

if you have a newwer Version of postgres || is the right tools fo r tha

CREATE TABLE "Media" ("Details" jsonb)

INSERT INTO "Media" VALUES('{"Type": "VIP", "Quantity": 1}')

Update "Media" set "Details" = "Details"
|| ('{"Subscription": "' || ("Details"->>'Type')::Text || '"}')::jsonb
||'{"Type":2}'::jsonb

SELECT * FROM "Media"

| Details                                           |
| :------------------------------------------------ |
| {"Type": 2, "Quantity": 1, "Subscription": "VIP"} |

db<>fiddle here

user_51935360
Related