如何在PGADMIN中更新具有更换值的JSONB列？

发布于 2025-01-30 20:04:07 字数 965 浏览 3 评论 0原文

我有一个称为文件的PostgreSQL表，其中包括一个名为格式的JSONB表。虽然某些行是[null]，而其他行则具有此结构：

{
  "thumbnail": {
    "ext": ".jpg",
    "url": "https://some-url.com/image01.jpg",
    "name": "image01.jpg",
    //...other properties
  }
}

对于每个行，我都想更新thumbnail.url 并替换some-url 带有其他URL。

我远非是PostgreSQL（或其他任何其他数据库）的专家，经过一些阅读，我尝试在PGADMIN中运行以下查询：

UPDATE files 
  SET formats = jsonb_set(formats, '{thumbnail.url}', REPLACE('{thumbnail.url}', 'some-url', 'other-url'))

我收到了此错误：函数JSONB_SET（JSONB，UNKNOWER，UNKNOWN，UNKNOWER，UNKNOWER，UNKNOWER，文本）不存在

我尝试设置格式jsonb_set（格式:: jsonb ...），尝试target target '{thumbnail}'而不是<代码>'{thumbnail.url}' - 始终存在相同的错误。

我在做什么错？还是PGADMIN真的不支持此功能？如何使用PGADMIN查询工具进行此类更新？

原文

I have a PostgreSQL table called files which includes a jsonb table called formats. While some rows are [null], others have objects with this structure:

{
  "thumbnail": {
    "ext": ".jpg",
    "url": "https://some-url.com/image01.jpg",
    "name": "image01.jpg",
    //...other properties
  }
}

For every row I want to update the thumbnail.url and replace some-url with other-url.

I'm far from being an expert in PostgreSQL (or any other DB for that matter), and after some reading I tried to run the following query in pgAdmin:

UPDATE files 
  SET formats = jsonb_set(formats, '{thumbnail.url}', REPLACE('{thumbnail.url}', 'some-url', 'other-url'))

And I received this error: function jsonb_set(jsonb, unknown, text) does not exist

I tried to set format jsonb_set(formats::jsonb...), tried to target '{thumbnail}' instead of '{thumbnail.url}' - always the same error.

What am I doing wrong? Or is pgAdmin really doesn't support this function? How can I do such an update with pgAdmin query tool?

分享到QQ

分享到微博

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

发布评论

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

〃温暖了心ぐ 2025-02-06 20:04:07

我们可以尝试使用- ＆gt;＆gt;获取url的JSON内容值，然后从中替换您的期望值。

因为您的url json的字段可能是字符串类型，我们需要使用“在施放为jsonb之前将其内容进行内容

jsonb_set（target jsonb，路径文本[]，new_value jsonb [，create_missing boolean]）

UPDATE files 
SET formats = jsonb_set(formats, '{thumbnail,url}', CONCAT('"',REPLACE(formats->'thumbnail'->>'url','some-url','other-url'),'"')::JSONB);

We can try to use ->> to get JSON content value of url and then replace your expect value from that.

Because your url field of your JSON might be string type we need to use " to content it before cast as JSONB

jsonb_set(target jsonb, path text[], new_value jsonb [, create_missing boolean])

UPDATE files 
SET formats = jsonb_set(formats, '{thumbnail,url}', CONCAT('"',REPLACE(formats->'thumbnail'->>'url','some-url','other-url'),'"')::JSONB);

sqlfiddle

回复收藏 0 原文

晨曦÷微暖 2025-02-06 20:04:07

JSONB_SET（）的第二个参数必须是每个“路径”元素一个数组元素的数组。因此，第二个参数应为'{thumbnail，url}'或更明显：array ['thumbnail'，'url']

，第三个参数必须为a jsonb值，但是替换返回text，因此您需要使用eg to_jsonb（）转换替换（）为JSONB值。

正如D-Shih所指出的那样，您需要使用- ＆gt;＆gt;提取旧值。但是要获取URL，您需要“导航”到它：格式 - ＆gt; '缩略图 - ＆gt;＆gt; 'url'

我还会添加一个Where子句，以便您仅更新实际包含URL的行。

UPDATE files 
  SET formats = jsonb_set(formats, 
                          '{thumbnail,url}', 
                          to_jsonb(replace(formats -> 'thumbnail' ->> 'url', 'some-url', 'other-url'))
                          )
where (formats -> 'thumbnail') ? 'url'

The second parameter of jsonb_set() must be an array with one array element for each "path" element. So the second parameter should be '{thumbnail,url}' or more obvious: array['thumbnail', 'url']

And the third parameter must be a jsonb value, but replace returns a text, so you need to use e.g. to_jsonb() to convert the result of the replace() to a jsonb value.

And as D-Shih pointed out, you need to extract the old value using ->>. But to get the URL you need to "navigate" to it: formats -> 'thumbnail ->> 'url'

I would also add a WHERE clause so that you only update rows that actually contain a URL.

UPDATE files 
  SET formats = jsonb_set(formats, 
                          '{thumbnail,url}', 
                          to_jsonb(replace(formats -> 'thumbnail' ->> 'url', 'some-url', 'other-url'))
                          )
where (formats -> 'thumbnail') ? 'url'

回复收藏 0 原文

~没有更多了~