PostgreSQL中如何从文本类型JSON列仅提取值？

现有表the_table的the_json列类型为text，存储JSON格式数据（示例：'{"foo": 42, "bar": 43}'），无法修改表结构，需提取JSON中的所有值（如42、43）并忽略键。

使用json_each时直接查询能得到键值对记录集，但子查询提取value列会提示“column 'value' does not exist”，这是因为json_each返回的是复合类型，并非拆分好的独立列，可通过以下方式处理：

方法1：横向关联展开（推荐） #

通过LATERAL JOIN（或简写为逗号分隔）将json_each的结果与原表关联，直接访问展开后的value列：

SELECT j.value
FROM the_table, json_each(the_json::json) j;

给json_each的结果起别名j，就能直接调用其内置的value字段。

方法2：拆分复合类型列 #

如果用子查询，需要先把复合类型拆分为独立列，再提取value：

SELECT value
FROM (
  SELECT (json_each(the_json::json)).*
  FROM the_table
) sub_query;

(json_each(...)).*会把复合类型的key和value拆成两个单独的列，外层查询即可直接取value。

方法3：直接提取值（PostgreSQL 12+） #

如果你的PostgreSQL版本是12及以上，可以用json_object_values函数直接获取所有值，无需处理键，语法更简洁：

SELECT json_object_values(the_json::json)
FROM the_table;

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