PostgreSQL - Set JSONB property from column value

Question

I have a numeric column total_price and I want to move it to a jsonb property named price_detail. For example, if the total_price value is 1000, the expected price_detail value is {"totalPrice": 1000}

I've tried using jsonb_set but the result is empty object {}

UPDATE public.orders SET price_detail =
       jsonb_set('{}'
               , '{}'
               , jsonb_build_object('totalPrice', total_price::numeric))

If I set the path,

UPDATE public.orders SET price_detail =
       jsonb_set('{}'
               , '{totalPrice}'
               , jsonb_build_object('totalPrice', total_price::numeric))

The result is {"totalPrice":{"totalPrice":1000}} which is unexpected

How can I set the totalPrice property correctly?

Nick · Accepted Answer · 2020-04-05 12:45:26Z

1

You don't need the JSONB_SET, just use JSONB_BUILD_OBJECT:

UPDATE orders 
SET price_detail = JSONB_BUILD_OBJECT('totalPrice', total_price::numeric)

Demo on dbfiddle

answered Apr 5, 2020 at 12:45

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL - Set JSONB property from column value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related