PostgreSQL 11 JSONB列提取数据返回NULL问题求助

核心问题 #

你的meta_data列虽然定义为jsonb类型，但实际存储的是字符串形式的JSON内容，而非原生的JSON对象。从你看到的转义输出"{\"description\":\"Payment\",...}"就能看出——这个内容是被引号包裹的字符串，里面才是真正的JSON结构，并非直接的JSON对象。

验证方法 #

执行以下查询确认数据类型：

SELECT id, jsonb_typeof(meta_data) AS data_type FROM payments WHERE id=1;

如果返回的data_type是string，就完全印证了这个问题。

解决步骤 #

1. 修复现有数据
   把存储的字符串转换为真正的jsonb对象：

   UPDATE payments
   SET meta_data = meta_data::text::jsonb
   WHERE jsonb_typeof(meta_data) = 'string';
   

   这里先将jsonb类型的字符串值转为text，再重新转为jsonb，就能得到原生的JSON对象。

2. 正常查询字段
   数据修复后，即可用你原本的语句提取字段：

   -- 返回jsonb类型的字段值
   SELECT id, meta_data->'description' AS desc FROM payments WHERE id=1;
   -- 返回文本类型的字段值
   SELECT id, meta_data->>'description' AS desc FROM payments WHERE id=1;
   

3. 避免后续重复问题
   插入数据时，确保直接传入JSON对象而非字符串形式的JSON。示例：

   -- 正确插入方式
   INSERT INTO payments (meta_data) VALUES ('{"description":"Payment","country":"US"}'::jsonb);
   -- 错误插入方式（会导致字符串存储）
   INSERT INTO payments (meta_data) VALUES ('"{\"description\":\"Payment\",\"country\":\"US\"}"'::jsonb);
   

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