Postgres - jsonb create and update new attribute in column

Question

I have such a column attributes:

{
  "a:value": "123",
  "a:origin": "abc"
}

I want to create a new attribute which should look like this:

"abcKey:value": {
    "value": "123ABC",
    "version": "1"
  }

So, in the end attributes should look like this:

{
      "a:value": "123",
      "a:origin": "abc",
      "abcKey:value": {
        "value": "123ABC",
        "version": "1"
      }
    }

How can I do this?

I tried this

update my_table
set attributes = jsonb_set(attributes, '{abcKey:value,value}', '"123ABC"')
attributes = jsonb_set(attributes, '{abcKey:value,version}', '"1"')
where ...;

But this does not work because I think, I have to create the new attribute at first. How can I create and update this new attribute (maybe in one step)?

Thank you very much!

Ramin Faracov · Accepted Answer · 2023-02-03 08:32:59Z

1

I wrote two samples for you:

-- if you have same objects are json 
with tb as (
    select 
    '{
      "a:value": "123",
      "a:origin": "abc"
    }'::jsonb a1,   
    
    '{"abcKey:value": {
        "value": "123ABC",
        "version": "1"
    }}'::jsonb a2 
) 
select a1, a2, a1||a2 from tb; -- you can concate json objects
    
-- if you can create json objects via using key value
with tb as (
    select 
    '{
      "a:value": "123",
      "a:origin": "abc"
    }'::jsonb a1   
) 
select a1 || jsonb_build_object('abcKey:value', jsonb_build_object('value', '123ABC', 'version', 1)) from tb;

answered Feb 3, 2023 at 8:32

Ramin Faracov

3,3331 gold badge6 silver badges10 bronze badges

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

1 Comment

Tobitor Over a year ago

Thank you very much! I tried the second option and this one works :)

Collectives™ on Stack Overflow

Postgres - jsonb create and update new attribute in column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related