PostgreSQL中解析字符串存储的JSON并查询指定字段

因为你的json_col是字符串形式存储的JSON数据，需要先把它转换成PostgreSQL支持的JSON类型，再提取目标字段。

方法1：使用JSON操作符（推荐） #

直接通过类型转换+链式操作符提取值：

SELECT 
  index,
  -- 先转JSON类型，再逐层提取beta的文本值
  json_col::json -> 'alpha' ->> 'beta' AS beta_value
FROM my_table;

• json_col::json：把字符串列转换为JSON数据类型
• -> 'alpha'：获取JSON对象里的alpha属性（返回JSON对象）
• ->> 'beta'：从alpha对象中提取beta的文本值（直接返回字符串）

针对你的示例数据，执行后会得到：

方法2：使用JSON提取函数 #

如果习惯用函数式写法，可以用json_extract_path_text：

SELECT 
  index,
  json_extract_path_text(json_col::json, 'alpha', 'beta') AS beta_value
FROM my_table;

处理可能的无效JSON #

如果json_col里存在格式错误的JSON字符串，PostgreSQL 16及以上版本可以用validate_json过滤无效数据：

SELECT 
  index,
  json_col::json -> 'alpha' ->> 'beta' AS beta_value
FROM my_table
-- 只保留能正常解析的JSON数据
WHERE validate_json(json_col) IS NULL;

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