Update postgres json value by other attribute

Question

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 ?

Answer 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 || ...

Answer 2

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.