PostgreSQL中按名称而非索引提取JSON数组字段值

针对PostgreSQL中JSON数组按名称提取对应value的需求，你可以用以下几种方法实现，无需依赖元素的索引位置：

方法1：使用jsonb_array_elements配合横向连接 #

这种方法会将JSON数组展开为多行记录，再筛选出目标name对应的元素，提取其value：

SELECT elem ->> 'value' AS total_physical_memory
FROM mytable,
     jsonb_array_elements(system_properties::jsonb) AS elem
WHERE elem ->> 'name' = 'system.totalphysicalmemory';

如果单条记录中可能存在多个匹配system.totalphysicalmemory的元素，这条语句会返回多行结果；如果需要每个记录仅返回一个值（比如取第一个匹配项），可以加上DISTINCT ON（假设表有主键id）：

SELECT DISTINCT ON (mytable.id) elem ->> 'value' AS total_physical_memory
FROM mytable,
     jsonb_array_elements(system_properties::jsonb) AS elem
WHERE elem ->> 'name' = 'system.totalphysicalmemory'
ORDER BY mytable.id;

方法2：左连接确保所有记录返回结果 #

如果希望即使记录中没有system.totalphysicalmemory元素时也能返回默认值（比如N/A），可以使用LEFT JOIN LATERAL：

SELECT COALESCE(elem ->> 'value', 'N/A') AS total_physical_memory
FROM mytable
LEFT JOIN LATERAL jsonb_array_elements(system_properties::jsonb) AS elem
  ON elem ->> 'name' = 'system.totalphysicalmemory';

方法3：使用JSON路径查询（PostgreSQL 12+） #

PostgreSQL 12及以上版本支持JSON路径语法，可以更简洁地定位目标值：

SELECT jsonb_path_query(system_properties::jsonb, '$[*] ? (@.name == "system.totalphysicalmemory").value') #>> '{}' AS total_physical_memory
FROM mytable;

如果需要处理数组为NULL的情况，可以用COALESCE将NULL转为空数组，避免报错：

SELECT jsonb_path_query(COALESCE(system_properties::jsonb, '[]'::jsonb), '$[*] ? (@.name == "system.totalphysicalmemory").value') #>> '{}' AS total_physical_memory
FROM mytable;

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