postgresql - Postgres 9.6 - 更新存储在 JSONB 中的文本

Question

我有一个数据库，其中包含大量引用大量异 map 像的 JSON 数据 block 。

表格:

CREATE TABLE vendors (
    id uuid NOT NULL,
    name character varying(255) NOT NULL,
    images jsonb[],
    location jsonb,
    user_id uuid,
    created timestamp with time zone,
    updated timestamp with time zone
);

JSON:

{ 
    "url": "http://domain.com/eid/id/file.jpg", 
    "title": "foo", 
    "eid": "eid", 
    "id": "id"
}

这些图像现在已从 http://移至 https://，我想更新数据。我最终会完全删除该域，以便这些是相对路径，从而避免这种大屠杀!

我正在考虑做一些非常粗暴的事情，例如:

UPDATE vendors SET images = REPLACE(images, 'http://', 'https://');

但是我看到了以下错误:

LINE 1: UPDATE vendors SET images = REPLACE(images, 'http://','https...
                                    ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

我猜问题出在类型转换部分，但我想不通。

Answer 1

WITH https AS (
   SELECT v.id, array_agg(x.i) newimages
      FROM vendors v JOIN
           LATERAL jsonb_array_elements(
                      replace(to_jsonb(v.images)::text,
                              'http://',
                              'https://'
                      )::jsonb
                   ) x(i)
              ON TRUE
      GROUP BY v.id
)
UPDATE vendors v
   SET images = https.newimages
   FROM https
   WHERE v.id = https.id;