PostgreSQL 15：基于匹配ID将照片表数据存入评论表数组

我有两张表，分别是reviews（评论表）和photos（照片表），需要通过两者共有的评论ID进行匹配，把照片数据以包含照片ID和URL的JSON对象形式，添加到reviews表的photos数组字段中。我是PostgreSQL和SQL新手，写不出正确的查询语句，以下是表结构和我的尝试：

CREATE TABLE IF NOT EXISTS public.reviews
(
    id integer NOT NULL,
    product_id integer NOT NULL,
    rating integer,
    date text COLLATE pg_catalog."default",
    summary text COLLATE pg_catalog."default" NOT NULL,
    body text COLLATE pg_catalog."default" NOT NULL,
    recommend boolean,
    reported boolean,
    reviewer_name text COLLATE pg_catalog."default" NOT NULL,
    reviewer_email text COLLATE pg_catalog."default" NOT NULL,
    response text COLLATE pg_catalog."default",
    helpfulness integer,
    photos text[] COLLATE pg_catalog."default",
    CONSTRAINT reviews_pkey PRIMARY KEY (id)
)

CREATE TABLE IF NOT EXISTS public.photos
(
    id integer,
    review_id integer,
    url text COLLATE pg_catalog."default"
)


update reviews 
set photos = array_append(photos, photos.url)
where photos.review_id = reviews.id;

原查询的问题 #

1. 未正确关联两张表：直接在WHERE子句引用photos表会触发语法错误，PostgreSQL的UPDATE语句需要通过FROM子句关联其他表
2. 不符合需求格式：仅添加了URL字符串，而非包含照片ID和URL的JSON对象
3. 未处理多照片场景：array_append只能单次添加一个元素，无法批量聚合同一评论下的所有照片

正确解决方案 #

先对photos表按review_id分组，将每组照片转为JSON字符串并聚合为数组，再关联reviews表完成更新：

覆盖原photos字段内容（适合初始化场景）

UPDATE public.reviews r
SET photos = p.photo_json_array
FROM (
    SELECT 
        review_id,
        array_agg(
            json_build_object('id', id, 'url', url)::text
        ) AS photo_json_array
    FROM public.photos
    GROUP BY review_id
) p
WHERE r.id = p.review_id;

追加到原photos字段末尾（保留已有内容）

UPDATE public.reviews r
SET photos = array_cat(r.photos, p.photo_json_array)
FROM (
    SELECT 
        review_id,
        array_agg(
            json_build_object('id', id, 'url', url)::text
        ) AS photo_json_array
    FROM public.photos
    GROUP BY review_id
) p
WHERE r.id = p.review_id
AND r.photos IS NOT NULL; -- 若原字段允许为空，可移除该条件避免遗漏更新

关键函数说明 #

• json_build_object('id', id, 'url', url)：将单张照片的ID和URL组装成JSON对象
• ::text：将JSON对象转为字符串，适配reviews.photos的text[]类型
• array_agg(...)：将同一评论下的所有照片JSON字符串聚合为一个数组
• array_cat(a, b)：将两个数组合并，实现追加已有数组的效果

内容的提问来源于stack exchange，提问作者maximosis