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.
CodePudding user response:
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.
CodePudding user response:
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