Question 115147 in PostgreSQL Question posted in
The official documentation can be found here.

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

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search