PostgreSQL：从嵌套JSONB数组筛选指定语言value并更新列

问题分析 #

需要从names字段（JSONB类型）的common数组里，筛选出language为en的元素对应的value，并将该值更新到en_name列中。之前的固定索引查询、@@操作符查询都无法正确获取目标值，需要使用更精准的JSON处理方法。

方法1：展开数组筛选查询 #

通过jsonb_array_elements将common数组展开为多行，再筛选符合条件的元素：

SELECT id, elem->>'value' AS en_name
FROM test, jsonb_array_elements(names->'common') AS elem
WHERE elem->>'language' = 'en';

如果存在多个language为en的条目，该语句会返回多行结果，可根据需求添加DISTINCT或LIMIT 1来获取单一值。

方法2：JSON路径直接提取 #

使用jsonb_path_query_first直接定位并返回第一个匹配条件的value，适合仅需单个结果的场景：

SELECT id, jsonb_path_query_first(names, '$.common[*] ? (@.language == "en").value')::text AS en_name
FROM test;

更新en_name列 #

基于JSON路径的更新语句 #

UPDATE test
SET en_name = jsonb_path_query_first(names, '$.common[*] ? (@.language == "en").value')::text
WHERE names @> '{"common": [{"language": "en"}]}';

添加WHERE条件可以避免对没有en语言条目的行进行空值覆盖。

基于数组展开的更新语句 #

UPDATE test t
SET en_name = sub.en_name
FROM (
    SELECT id, elem->>'value' AS en_name
    FROM test, jsonb_array_elements(names->'common') AS elem
    WHERE elem->>'language' = 'en'
) sub
WHERE t.id = sub.id;

错误方法说明 #

• 使用names->'common'->0->>'value'依赖固定索引，无法适配数组元素位置不固定的场景。
• 使用names @@ '$.common[*].value'返回的是布尔值（1/0），并非实际的value内容，不符合需求。

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