PostgreSQL 15.7无函数创建权限时，如何在WHEN中校验合法JSON？

由于PostgreSQL 15.7还未引入IS JSON语法，且无法创建自定义函数，可通过以下几种方式实现JSON合法性校验：

方案1：正则表达式初步校验（适合大多数常规场景） #

虽然正则无法100%覆盖所有JSON规范场景，但能处理绝大多数常见的JSON对象/数组结构，适合对校验精度要求不极端严格的场景：

SELECT
    CASE
        WHEN XML_IS_WELL_FORMED(text_column)
        THEN ARRAY_TO_STRING(XPATH('A/B/C/text()', text_column), ',')
        -- 匹配前后带空白的JSON对象或数组，同时排除未转义的双引号
        WHEN text_column ~ '^\s*(\{.*\}|\[.*\])\s*$' 
             AND text_column NOT LIKE '%[^\\]"%'
        THEN (text_column::json)->'A'->'B'->>'C'
    END AS extracted_element
FROM some_table

注：这个正则只是基础校验，无法处理所有复杂转义场景，比如嵌套转义字符的情况。

方案2：临时函数校验（若允许创建临时函数） #

如果你的账号有创建临时函数的权限（临时函数仅当前会话有效，不会影响数据库全局），可以创建一个临时的校验函数来捕获JSON转换异常：

-- 创建临时校验函数
CREATE TEMPORARY FUNCTION is_json_valid(text) RETURNS boolean AS $$
BEGIN
    PERFORM $1::jsonb;
    RETURN true;
EXCEPTION
    WHEN others THEN
        RETURN false;
END;
$$ LANGUAGE plpgsql;

-- 实际查询使用
SELECT
    CASE
        WHEN XML_IS_WELL_FORMED(text_column)
        THEN ARRAY_TO_STRING(XPATH('A/B/C/text()', text_column), ',')
        WHEN is_json_valid(text_column)
        THEN (text_column::json)->'A'->'B'->>'C'
    END AS extracted_element
FROM some_table;

如果完全没有创建任何函数的权限，这个方案无法使用。

方案3：利用pg_eval（不推荐，安全风险高） #

如果数据库开启了pg_eval扩展（生产环境通常不建议开启），可以用它来捕获转换异常，但此方法存在安全隐患，仅作应急参考：

SELECT
    CASE
        WHEN XML_IS_WELL_FORMED(text_column)
        THEN ARRAY_TO_STRING(XPATH('A/B/C/text()', text_column), ',')
        WHEN pg_eval('SELECT $1::jsonb', text_column) IS NOT NULL
        THEN (text_column::json)->'A'->'B'->>'C'
    END AS extracted_element
FROM some_table;

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