0

I'm using PostgreSQL 9.6 and I got the following table:

Cars

ID          Name        Data 
 1           abc        {"color":"red","wheels":4,"my-value":false}
 2           def        {"color":"blue","wheels":8}
 3           xyz        {"color":"yellow","my-value":true}

I want to extend the Data JSON object by adding another boolean attribute called "new-attr" which should get the exact value of "my-value" of the JSON field.

for example, if "my-value" in ID 1 is false, then "new-attr" will be false. And if not set, do not add it at all (as in number 2).

I tried the following but it didn't help:

UPDATE cars SET data=jsonb_set(data,'{new-attr}',data->'my-value')

I expect to get the following JSON :

 ID          Name        Data
1           abc        {"color":"red","wheels":4,"my-value":false,"new-attr":false} // same value
2           def        {"color":"blue","wheels":8} // no change
3           xyz        {"color":"yellow","my-value":true,"new-attr":true} // same value true

Any suggestions ?

1

The concatenation operator || will do that:

update the_table
  set data = data || jsonb_build_object('new-attr', data -> 'my-value')
where data ? 'my-value'; --<< only update rows that contain the key

The ? operator tests if the key is present in the JSON value.


If your column is defined as json (rather than jsonb which it should be), you probably need to cast it, to make the || operator work properly: data::jsonb || ...

1

You could keep your current UPDATE statement, but add WHERE data ? 'my-value' to it to avoid updating rows which have nothing you want to update.

When v13 is released, you could use the new jsonb_set_lax function:

UPDATE cars SET data=jsonb_set_lax(
    data,
    '{new-attr}',
    data->'my-value',
    true,
    'return_target');

But it seems like more work for you and more work for the server and harder to read than just adding a WHERE clause.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.