PostgreSQL：遍历JSONB数组提取insType=2对应的insOffset值

完全可以实现这个需求，PostgreSQL的JSONB函数和操作符能轻松处理这类场景。假设你的表名为your_table，可以用以下两种方式编写查询：

方式1：基础写法 #

SELECT
    t.UniqueID,
    jsonb_array_elements(t.Configuration)->>'insOffset' AS ins_offset
FROM
    your_table t
WHERE
    (jsonb_array_elements(t.Configuration)->>'insType')::int = 2;

方式2：更高效的LATERAL JOIN写法 #

SELECT
    t.UniqueID,
    elem->>'insOffset' AS ins_offset
FROM
    your_table t,
    LATERAL jsonb_array_elements(t.Configuration) elem
WHERE
    (elem->>'insType')::int = 2;

关键说明 #

• jsonb_array_elements(t.Configuration)会将JSONB数组拆分为单个独立的JSONB对象（别名elem）
• elem->>'insType'提取insType的字符串值，转为int后与2比较，保证类型匹配更严谨
• 若需要将insOffset转为数值类型，可使用(elem->>'insOffset')::numeric这类格式转换
• 如果部分数组对象可能缺失insOffset字段，可添加coalesce(elem->>'insOffset', '无值')来替换null结果

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