如何在PostgreSQL的jsonb列中查询与更新数组内的nationality字段

一、查询nationality为'CertainValue'的记录 #

方式1：使用@>操作符（推荐，支持索引优化） #

直接判断travellers数组是否包含符合条件的对象，SQL语句：

SELECT *
FROM BookingDetails
WHERE info->'travellers' @> '[{"nationality": "CertainValue"}]'::jsonb;

如果需要频繁按该条件查询，可创建GIN索引提升性能：

CREATE INDEX idx_bookingdetails_travellers ON BookingDetails USING GIN (info->'travellers');

方式2：展开数组后过滤 #

通过jsonb_array_elements将数组展开为行，再筛选目标记录：

SELECT DISTINCT bd.*
FROM BookingDetails bd
JOIN jsonb_array_elements(bd.info->'travellers') AS t
ON t->>'nationality' = 'CertainValue';

这种方式适合需同时处理数组元素的场景，但性能不如方式1（除非针对性建索引）。

二、将nationality为'CertainValue'的记录更新为'AnotherValue' #

通过重新构建travellers数组替换目标值，SQL语句：

UPDATE BookingDetails
SET info = jsonb_set(
    info,
    '{travellers}',
    (
        SELECT jsonb_agg(
            CASE WHEN t->>'nationality' = 'CertainValue'
                 THEN t || '{"nationality": "AnotherValue"}'::jsonb
                 ELSE t
            END
        )
        FROM jsonb_array_elements(info->'travellers') AS t
    )
)
WHERE info->'travellers' @> '[{"nationality": "CertainValue"}]'::jsonb;

说明：

• 用jsonb_array_elements展开travellers数组为单个元素行
• 通过CASE语句判断并替换符合条件的nationality值
• 用jsonb_agg将处理后的元素重新聚合为数组
• 最后用jsonb_set把新数组替换回info字段的travellers路径
• WHERE条件过滤出需更新的记录，避免无意义的全表扫描

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