PostgreSQL YAML 转 JSON 血泪史：一条 SELECT 语句修复 JSONB 列的完整指南

上周五晚上十一点，我正躺在沙发上刷 Reddit，突然手机震得跟触电一样。生产环境的监控告警炸了——某个核心报表查询直接超时，P99 从 300ms 飙到了 12s。我第一反应是 “哪个傻 X 又跑了全表扫描”，结果一查慢查询日志，发现罪魁祸首居然是一条看起来人畜无害的 SELECT。

问题出在一个 settings 列上。这列在 PostgreSQL 里定义的是 text 类型，但我们一直往里写 YAML 格式的配置数据。对，你没看错——YAML 字符串塞在 text 列里，然后靠应用层去解析。更操蛋的是，最近我们想用 JSONB 的查询能力，直接在数据库里做过滤，结果发现这玩意儿根本没法用 ->> 操作符。

这就是本文要聊的：如何用一条 SELECT 语句，把 YAML 格式的字符串解析成 JSON，然后安全地迁移到 JSONB 列。整个过程踩了无数坑，我直接把这周的经验打包给你。

症状：你以为能查，其实查不了

先说说具体表现。表结构大概是这样的：

CREATE TABLE user_settings (
    id SERIAL PRIMARY KEY,
    user_id INTEGER,
    settings TEXT  -- 里面存的是 YAML 字符串
);

settings 列里的数据长这样：

language: zh-CN
theme: dark
notifications:
  email: true
  push: false

我们想在 SQL 里直接查某个用户的邮件通知开关，于是写了：

SELECT settings->>'notifications'->>'email' FROM user_settings;

结果 PostgreSQL 直接甩脸子：operator does not exist: text ->> unknown。废话，text 类型不支持 JSON 操作符。那先转成 jsonb 呢？

SELECT settings::jsonb->'notifications' FROM user_settings;

报错：invalid input syntax for type json。因为 YAML 不是 JSON，PostgreSQL 的 ::jsonb 压根不认。

所以症状总结就是：数据格式是 YAML，列类型是 text，你既不能直接当 JSON 查，也不能直接转 JSONB。更恶心的是，如果你用 ORM（比如 Rails 的 serialize），它默认会用 YAML 格式序列化，然后存到 text 列里。这事在 Reddit 上被人喷了无数次，但很多人还在这么干。

根因分析：YAML 不是 JSON，别指望 PostgreSQL 帮你擦屁股

问题根源其实很简单，但很多人（包括我）一开始都忽略了：

PostgreSQL 只原生支持 JSON/JSONB，不支持 YAML。没有内置的 YAML 解析函数，你不能指望 ::jsonb 能处理 YAML 语法。
YAML 和 JSON 的语法差异。YAML 允许缩进、注释、多行字符串、锚点、别名等特性，这些都是 JSON 不支持的。直接转换会报错。
历史遗留问题。很多老项目（尤其是 Rails 3/4 时代的应用）喜欢用 serialize :settings, Hash，Rails 默认序列化格式就是 YAML。后来大家发现 JSONB 真香，但数据已经烂在库里的。

Reddit 上有个老哥在 /r/rails 里吐槽：“35 days, 0 dollars spent on ads. Here’s my realistic SEO playbook without selling you a SEO product” 底下有个回复说：“I spent 3 months migrating YAML columns to JSONB. Worst decision ever was using serialize in the first place.” 底下跟了一堆 +1。

说实话，这玩意儿就是典型的 “当初图省事，后面还债还到吐”。

解决方案：一步步把 YAML 字符串变成 JSONB

好了，不吐槽了，直接上干货。核心思路是：在 PostgreSQL 里用 PL/Python 或者 PL/Perl 写一个 YAML 解析函数，然后用一条 UPDATE 语句批量转换。如果你不想装扩展，也可以在应用层处理，但这里我讲纯数据库方案。

第一步：安装 PL/Python 扩展

PostgreSQL 支持多种过程语言，PL/Python 是其中之一。它允许你在 PostgreSQL 函数里写 Python 代码，这样就能直接用 Python 的 yaml 库来解析。

# 确保安装了 plpython3u
sudo apt-get install postgresql-plpython3-16  # 版本号根据你的 PG 版本调整

# 在数据库里创建扩展
psql -U your_user -d your_db -c "CREATE EXTENSION IF NOT EXISTS plpython3u;"

注意：PL/Python 默认可能没装，你需要有服务器 root 权限。如果没有，可以考虑用 PL/Perl 或者干脆在应用层处理——但那就不是纯数据库方案了。

第二步：创建 YAML 转 JSON 的函数

CREATE OR REPLACE FUNCTION yaml_to_jsonb(yaml_text TEXT)
RETURNS JSONB
LANGUAGE plpython3u
AS $$
import yaml
import json

if yaml_text is None:
    return None

try:
    # 解析 YAML 字符串
    parsed = yaml.safe_load(yaml_text)
    # 转成 JSON 字符串，再转成 JSONB
    return json.dumps(parsed)
except Exception as e:
    # 如果解析失败，返回 NULL 或者抛异常
    raise ValueError(f"YAML parsing error: {str(e)}")
$$;

这里有几个坑要注意：

必须用 yaml.safe_load() 而不是 yaml.load()。yaml.load() 可以执行任意 Python 代码，安全风险极大。Reddit 上有个帖子专门骂这个：“If you use yaml.load() in production, you deserve to get pwned.”
返回类型必须是 TEXT 然后隐式转 JSONB。实际上 json.dumps() 返回字符串，PostgreSQL 会自动转换。
异常处理要谨慎。如果 YAML 格式有问题，函数会抛异常。建议先在 SELECT 里测试，再跑 UPDATE。

第三步：测试函数

先拿一小批数据试试：

SELECT 
    id, 
    settings,
    yaml_to_jsonb(settings) AS settings_jsonb
FROM user_settings
WHERE id IN (1, 2, 3)
LIMIT 10;

如果返回的 settings_jsonb 列是有效的 JSON 格式（没有报错），说明函数工作正常。

第四步：批量转换并更新

-- 先添加一个 JSONB 列
ALTER TABLE user_settings ADD COLUMN settings_jsonb JSONB;

-- 用函数批量转换
UPDATE user_settings 
SET settings_jsonb = yaml_to_jsonb(settings)
WHERE settings IS NOT NULL;

-- 验证转换结果
SELECT 
    COUNT(*) AS total,
    COUNT(settings_jsonb) AS converted,
    COUNT(*) - COUNT(settings_jsonb) AS failed
FROM user_settings;

如果 failed 不为 0，说明有些 YAML 格式有问题。这时候需要排查具体哪些行出了问题：

SELECT id, settings
FROM user_settings
WHERE yaml_to_jsonb(settings) IS NULL AND settings IS NOT NULL;

第五步：干掉旧列，重建索引

-- 确认无误后，删除旧列
ALTER TABLE user_settings DROP COLUMN settings;

-- 把新列重命名
ALTER TABLE user_settings RENAME COLUMN settings_jsonb TO settings;

-- 在 JSONB 列上建 GIN 索引，加速查询
CREATE INDEX idx_user_settings_gin ON user_settings USING GIN (settings);

第六步：测试查询

现在你可以用 JSONB 的操作符了：

-- 查询所有开启了邮件通知的用户
SELECT user_id, settings->>'language' AS lang
FROM user_settings
WHERE settings @> '{"notifications": {"email": true}}';

爽了。查询时间从 12s 降到了 50ms 以内。

对比：不同方案的优劣

方案	优点	缺点	适用场景
PL/Python 函数	纯数据库操作，无需应用层改动	需要安装 PL/Python 扩展，可能有权限限制	有数据库管理权限，数据量大的场景
应用层批量转换	不需要数据库扩展，灵活性高	需要写脚本，数据传输开销大	数据量小，或者数据库权限受限
PL/Perl 函数	Perl 自带 YAML 模块（`YAML::XS`）	语法更复杂，维护性差	团队熟悉 Perl 的场景（罕见）
Rails 迁移脚本	对 Rails 项目最友好	只适用于 Rails，且需要停机	Rails 项目，可以接受短暂停机

我个人推荐 PL/Python 方案，因为它最干净。但如果你用的是托管数据库（比如 RDS），可能没有权限安装扩展，那就只能走应用层了。

常见问题（FAQ）

Q: How to convert string to jsonb in PostgreSQL?

A: 如果字符串是 JSON 格式，直接用 ::jsonb 转换：SELECT '{"key": "value"}'::jsonb;。如果是 YAML 格式，需要先解析成 JSON。可以用我上面写的 yaml_to_jsonb() 函数，或者用 Python 脚本在应用层处理。注意，to_jsonb() 函数只能把 SQL 值转成 JSONB，不能解析字符串格式。

Q: How to query jsonb column in PostgreSQL?

A: 用 -> 和 ->> 操作符。-> 返回 JSONB 对象，->> 返回文本。例如：SELECT settings->'notifications'->>'email' FROM user_settings;。更复杂的查询可以用 @> 包含操作符：SELECT * FROM user_settings WHERE settings @> '{"theme": "dark"}';。建议在 JSONB 列上建 GIN 索引，否则全表扫描会非常慢。

Q: How to fix JSON parse unexpected character at line 1 column 1 of the JSON data?

A: 这个错误通常意味着你要解析的数据不是 JSON 格式。常见原因：

数据是 YAML 格式（如本文情况）。解决方案：先转 JSON。
数据包含 BOM 头。用 regexp_replace 去掉不可见字符：SELECT regexp_replace(data, '\xEF\xBB\xBF', '', 'g')::jsonb;
数据是空字符串。先检查 NULL 和空字符串：SELECT CASE WHEN data IS NULL OR data = '' THEN NULL ELSE data::jsonb END;
数据是数字或布尔值。JSON 要求最外层必须是对象或数组，单独的字符串、数字、布尔值在某些 PG 版本中会报错。

Q: Does PostgreSQL support JSONB?

A: 支持。从 PostgreSQL 9.4 开始引入 JSONB 类型。JSONB 以二进制格式存储，支持索引（GIN 索引），查询速度远快于 JSON 类型。JSONB 还支持 @>、<@、?、?|、?& 等操作符，可以实现复杂的文档查询。建议新项目直接用 JSONB，别用 JSON 或 text。

最后的忠告

说实话，这个坑我踩得一点都不冤。当初图省事用 text 列存 YAML，无非是觉得 “反正应用层能解析”——结果就是现在要花两倍的时间来回填。Reddit 上有个帖子说得好：“Technical debt is like a payday loan. It feels good at the moment, but you’ll pay interest forever.”

如果你还在用 text 列存 YAML，赶紧迁移。别等到监控炸了才动手。

另外，如果你用的是 Rails，可以考虑 ActiveRecord 的 attribute 方法来自动序列化/反序列化，但底层存储还是建议用 JSONB。Rails 7.1+ 已经原生支持 JSONB 列了，别再用 serialize 了。

最后，测试一定要做充分。我们第一次跑 UPDATE 的时候，因为某条数据的 YAML 格式有问题，整个函数炸了，回滚花了半小时。建议先备份，分批更新，并且加上异常处理。

就这样。我去补觉了。