如何更新JSONB Postgres列中的整个JSON对象，除1个字段

发布于 2025-02-12 17:00:41 字数 719 浏览 2 评论 0 原文

例如，我有一个表：

CREATE TABLE fruit(id bigint, data jsonb);

例如，一行是：

1,    
{
   "type": "pinapple",
   "store1": {
   "first_added": "<some timestamp>",
   "price": "10",
   "store_id": "1",
   "comments": "some comments..."
},
   "store2": {
   "first_added": "<some timestamp>",
   "price": "11",
   "store_id": "2",
   "comments": "some comments..."
},
   .... more stores
}

在更新的情况下，我有水果ID和存储数据：

1,
"store1": {
            "price": "12",
            "store_id": "1",
            "comments": "some comments...V2"
        }

除了第一个_ADDED字段外，我想在水果条目中更新整个商店对象（for Store1）。

是否知道如何通过JSONB运营商或功能来实现它？

谢谢

原文

for example I have a table:

CREATE TABLE fruit(id bigint, data jsonb);

and a row for example is:

1,    
{
   "type": "pinapple",
   "store1": {
   "first_added": "<some timestamp>",
   "price": "10",
   "store_id": "1",
   "comments": "some comments..."
},
   "store2": {
   "first_added": "<some timestamp>",
   "price": "11",
   "store_id": "2",
   "comments": "some comments..."
},
   .... more stores
}

In case of update I have the fruit id and store data :

1,
"store1": {
            "price": "12",
            "store_id": "1",
            "comments": "some comments...V2"
        }

I want to update entire store object in fruit entry (for store1), except the first_added field.

Any idea how I can accomplish it via JSONB operators or functions?

Thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

初见你 2025-02-19 17:00:41

您可以使用

UPDATE fruit
SET data = data || jsonb_set($1::jsonb, '{store1,first_added}', data#>'{store1,first_added}')
WHERE id = 1;

^{（ $ 1 设置为值 {“ store1”：{“ PRICE”：“ 12”，“ store_id”：“ 1”，“注释”：“一些注释.. .v2“}} 。}

或者，如果需要动态的密钥，请使用

UPDATE fruit
SET data = jsonb_set(data, ARRAY[$2::text], jsonb_set($1::jsonb, '{first_added}', data->$2->'first_added'))
WHERE id = 1;

^（

You can use

UPDATE fruit
SET data = data || jsonb_set($1::jsonb, '{store1,first_added}', data#>'{store1,first_added}')
WHERE id = 1;

^{(online demo)}
where the parameter $1 is set to the value {"store1": {"price": "12", "store_id": "1", "comments": "some comments...V2"}}.

Or if you need the key to be dynamic, use

UPDATE fruit
SET data = jsonb_set(data, ARRAY[$2::text], jsonb_set($1::jsonb, '{first_added}', data->$2->'first_added'))
WHERE id = 1;

^{(online demo)}

回复收藏 0 原文

月亮坠入山谷 2025-02-19 17:00:41

您可以使用 jsonb_set 函数来更改所需元素，然后使用 jsonb_build_object 函数创建新数据集，然后将数据与 || 操作员以保留其余数据（first_added，...）

update table1 
  set data = jsonb_set(data, '{store1}',  jsonb_build_object('first_added', data->'store1'->'first_added', 'price', 12, 'store_id', 1, 'comments', 'some comments...V2'))
where id  = 1;

演示 dbfiddle

You can use the jsonb_set function to change the desired element, then use the jsonb_build_object function to create a new dataset, then concatenate the data with the || operator to keep the rest of the data(first_added,...)

update table1 
  set data = jsonb_set(data, '{store1}',  jsonb_build_object('first_added', data->'store1'->'first_added', 'price', 12, 'store_id', 1, 'comments', 'some comments...V2'))
where id  = 1;

Demo in DBfiddle

回复收藏 0 原文

~没有更多了~