Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
2.1k views
in Technique[技术] by (71.8m points)

postgresql - postgres jsonb_set multiple keys update

I have DB table with jsonb column.

number  | data
    1   | {"name": "firstName", "city": "toronto", "province": "ON"}

I need a way to update data column. So my output should look like:

{"name": "firstName", "city": "ottawa", "province": "ON", "phone": "phonenum", "prefix": "prefixedName"}

Is it possible with json_set? I have added query like:

update table_name set data = jsonb_set(data, '{city}', '"ottawa"') where number = 1;

However, I need a way to add new key-value if it does not exists and update key value if it exists. Is it possible to achieve this in single query?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

The documentation says:

The || operator concatenates the elements at the top level of each of its operands. ... For example, if both operands are objects with a common key field name, the value of the field in the result will just be the value from the right hand operand.

So using your example data:

update table_name set
    data = data || '{"city": "ottawa", "phone": "phonenum", "prefix": "prefixedName"}'
    where number = 1;

Additionally if the object you want to edit is not at the top level - just combine the concatenation and jsonb_set function. For example, if the original data looks like

{"location": {"name": "firstName", "city": "toronto", "province": "ON"}}

then

...
data = jsonb_set(
    data, 
    '{location}', data->'location' || '{"city": "ottawa", "phone": "phonenum", "prefix": "prefixedName"}')
...

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...