Postgres - jsonb create and update new attribute in column-CodePudding

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!

CodePudding user response：

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;