PostgreSQL JSONB列值均为字符串，如何单语句转为JSONB对象？

可以通过PostgreSQL的JSONB操作符和类型转换实现单语句更新，核心思路是先提取JSONB字符串内部的文本内容，再将其解析为JSONB对象。

通用写法（兼容所有PostgreSQL版本） #

UPDATE checklist_item
SET specifications = (specifications #>> '{}')::jsonb
WHERE jsonb_typeof(specifications) = 'string';

代码说明 #

• specifications #>> '{}'：提取JSONB列中存储的原始字符串内容（比如把JSONB值"{}"转为纯文本{}，把"{\"x\": 2, \"y\": \"z\"}"转为纯文本{"x": 2, "y": "z"}）
• ::jsonb：将提取到的纯文本解析为JSONB对象
• WHERE jsonb_typeof(specifications) = 'string'：仅处理类型为字符串的行，避免干扰已经是JSONB对象的记录

PostgreSQL 12+ 简化写法 #

PostgreSQL 12及以上版本提供了jsonb_parse_text函数，可直接完成文本到JSONB对象的解析：

UPDATE checklist_item
SET specifications = jsonb_parse_text(specifications #>> '{}')
WHERE jsonb_typeof(specifications) = 'string';

为什么to_jsonb(specifications)无效？ #

to_jsonb函数的作用是将输入值转换为JSONB类型，但不会解析输入值内部的字符串内容。比如输入是JSONB字符串"{}"，to_jsonb返回的还是JSONB字符串"{}"，而非目标JSONB对象{}。

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