如何在PostgreSQL中从jsonb数组获取产品的最新版本

核心问题说明 #

你之前的操作存在3个典型错误：

• JSON路径配置错误：你的JSON结构中没有version字段，版本信息存储在name字段中，提取路径写错会导致返回结果异常
• 版本号直接字符串比较不符合语义规则：直接用max()对比版本字符串会出现1.10.0 < 1.9.0的错误排序结果
• 数组操作类型不匹配：jsonb_path_query_array返回的是JSONB类型数组，不是PostgreSQL原生数组，不能直接套ARRAY[]用unnest处理

可行解决方案 #

以下SQL保留你原表的每行记录，每行单独取该行对应版本数组的最高版本，支持正确的语义化版本排序：

SELECT 
    product,
    jsonb_build_array(version_name) AS version
FROM (
    SELECT 
        product,
        elem ->> 'name' AS version_name,
        -- 按原表行分区，每行内的版本按语义版本倒序排序
        ROW_NUMBER() OVER (
            PARTITION BY i.ctid 
            ORDER BY string_to_array(regexp_replace(elem ->> 'name', '^.* - ', ''), '.')::int[] DESC
        ) AS rn
    FROM product.issues i,
         -- 展开customfield_01的JSON数组为单行单元素
         jsonb_array_elements(i.fields -> 'customfield_01') AS elem
    -- 过滤没有customfield_01字段或字段为空的记录（比如CCC）
    WHERE i.fields ? 'customfield_01' AND jsonb_array_length(i.fields -> 'customfield_01') > 0
) t
-- 取每行排序第一的最高版本
WHERE rn = 1;

逻辑说明 #

• jsonb_array_elements：将customfield_01的JSON数组拆分为单行单元素，方便逐个处理版本
• regexp_replace(elem ->> 'name', '^.* - ', '')：提取出版本号纯文本，比如将AAA - 1.83.0转换为1.83.0
• string_to_array(..., '.')::int[]：将版本号拆分为整数数组，比较时按位对比，保证1.10.0 > 1.9.0的正确排序
• PARTITION BY i.ctid：按原表的物理行ID分区，保证相同product的不同行不会被合并，符合你的预期输出格式

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