PostgreSQL提取JSON字符串内容的替代方法 更易维护的JSON转换方案

原写法本质说明 #

你用到的#>> '{}'是PostgreSQL特有的JSON路径提取运算符，空路径数组{}代表提取JSON根节点的文本值，确实属于比较小众的语法，可读性很差。

易读替代方案 #

方案1（优先推荐，PostgreSQL 12+）

使用标准SQL/JSON函数json_value，显式指定根路径$，语义清晰，任何人一看就知道是取JSON根节点的文本内容：

-- 提取JSON字符串内部的文本内容
SELECT json_value('"{\"foo\":\"bar\"}"'::json, '$');
-- 输出结果：{"foo":"bar"}

-- 验证转换为JSON object类型
SELECT json_typeof(json_value('"{\"foo\":\"bar\"}"'::json, '$')::json);
-- 输出结果：object

方案2（全版本兼容）

使用显式函数json_extract_path_text替代运算符，函数名直接说明作用，和原写法完全等价：

-- 提取内容
SELECT json_extract_path_text('"{\"foo\":\"bar\"}"'::json, VARIADIC '{}'::text[]);

-- 转换为JSON object
SELECT json_typeof(json_extract_path_text('"{\"foo\":\"bar\"}"'::json, VARIADIC '{}'::text[])::json);

注：如果使用的是jsonb类型，将上述函数替换为jsonb_value、jsonb_extract_path_text即可，逻辑完全一致。

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