PostgreSQL：从JSONB字段的版本键中提取最旧版本的查询方法

问题描述 #

有如下表结构：

create table subscriptions (id int, data jsonb);

已插入数据：

insert into subscriptions (id, data) 
values 
  (1, '{"versions": {"10.2.3": 3, "9.2.3": 4, "12.2.3": 5, "1.2.3": 5}}'),
  (2, '{"versions": {"0.2.3": 3, "2.2.3": 4, "3.2.3": 5}}');

需要编写SQL查询，得到每个id对应的最小版本号：

-----------------
| id | minVersion |
 -----------------
| 1  | 1.2.3      |
| 2  | 0.2.3      |
 -----------------

问题分析 #

你尝试的语句仅提取了版本号的第一段，无法完整比较整个语义化版本号的大小，因此无法得到正确结果。要解决这个问题，需要先将JSONB中的版本号键值对展开，再按语义化版本规则找到最小值。

解决方案 #

方法1：正确处理语义化版本号（推荐） #

该方法将版本号转换为整数数组，按照数组排序规则找到最小版本，能正确处理诸如1.10.3与1.2.3这类字符串排序会出错的场景：

SELECT 
    id,
    (
        SELECT kv.key
        FROM jsonb_each(data->'versions') kv
        ORDER BY string_to_array(kv.key, '.')::int[] ASC
        LIMIT 1
    ) AS minVersion
FROM subscriptions;

方法2：字符串排序（仅适用于简单版本格式） #

如果你的版本号第一段不会出现多位数（如示例中的情况），可以直接用字符串排序取最小值，但这种方法在复杂版本号场景下会失效：

SELECT 
    id,
    min(kv.key) AS minVersion
FROM subscriptions s
CROSS JOIN jsonb_each(s.data->'versions') kv
GROUP BY id
ORDER BY id;

说明 #

• jsonb_each(data->'versions')：将JSONB对象中的键值对展开为多行记录，每个版本号成为一行的key字段。
• string_to_array(kv.key, '.')::int[]：将版本号字符串按.分割，转换为整数数组，这样就能按照语义化版本规则比较大小（数组元素逐个比较）。

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