PostgreSQL中如何提取匹配authority_*模式的可变名称JSON键？

原代码里用srvc_req.form_data->>$$authority_*$$的写法无法实现通配符匹配，PostgreSQL的JSON操作符不支持这种模糊键名查询。要匹配authority_前缀加任意整数的键，需要通过JSON函数遍历键值对并结合正则筛选，以下是修改后的代码：

if json_array_length(tms_hlpr_usr_has_authority_fr_srvc_requests(usr_id_,org_id_)) > 0 then
    _extra_where = _extra_where || 
        ' and EXISTS (
            SELECT 1 
            FROM json_each_text(srvc_req.form_data) j(key, value)
            WHERE j.key ~ ''^authority_\d+$'' 
              AND j.value = ' || quote_literal(usr_id_) || '
        ) AND srvc_req.is_deleted IS NOT true ';
end if;

关键逻辑说明： #

• json_each_text(srvc_req.form_data)：将JSON对象展开为键值对的行集合，每一行包含key（键名）和value（对应字符串值）两个字段。
• j.key ~ ''^authority_\d+$''：使用正则表达式筛选键名，确保键名以authority_开头，后续为一个或多个数字（\d+匹配任意整数）。
• EXISTS子查询：只要存在符合条件的键值对，就返回true，这样能高效判断目标ID是否存在于指定格式的键对应值中。

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