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.