PostgreSQL JSONB字段中提取所有层级嵌套的value键对应数值的实现方法

当然可以实现啦！针对你这种在PostgreSQL JSONB字段里嵌套多层value键的场景，我给你两种实用的解决方案，分别适配不同版本的PostgreSQL：

方法一：使用JSON路径查询（PostgreSQL 12及以上推荐） #

PostgreSQL 12引入了JSON路径查询功能，这是处理这类嵌套JSON最简洁的方式。假设你的表名为test，存储JSON数组的JSONB字段名为data，可以用下面的查询语句：

SELECT jsonb_path_query(data, '$[*].value.** ? (@.type() == "number")')::numeric AS extracted_value
FROM test;

语句解释： #

• $[*]：遍历JSON数组中的每一个元素
• .value：取出每个元素下的value键对应的值
• .**：递归遍历当前节点下所有层级的子节点
• ? (@.type() == "number")：筛选出类型为数字的节点，确保只返回我们需要的数值
• ::numeric：把JSON类型的数值转换为PostgreSQL的numeric类型，方便后续处理

方法二：递归CTE（兼容PostgreSQL 11及以下版本） #

如果你的PostgreSQL版本低于12，没法用JSON路径的话，可以用递归CTE（公共表表达式）来逐层提取嵌套的value：

WITH RECURSIVE extract_values AS (
  -- 初始步骤：拆分JSON数组，取出每个元素的value
  SELECT 
    data->'value' AS json_node
  FROM test, jsonb_array_elements(test.data) AS data
  UNION ALL
  -- 递归步骤：如果当前节点是对象，继续提取它的value，直到得到数字
  SELECT 
    ev.json_node->'value' AS json_node
  FROM extract_values ev
  WHERE jsonb_typeof(ev.json_node) = 'object'
)
-- 最终筛选出所有数字类型的节点
SELECT json_node::numeric AS extracted_value
FROM extract_values
WHERE jsonb_typeof(json_node) = 'number';

语句解释： #

1. 初始CTE：用jsonb_array_elements把JSON数组拆分成单个对象，然后取出每个对象的value节点
2. 递归步骤：如果当前节点是JSON对象（说明还嵌套了value），就继续提取它的value，直到节点类型不是对象为止
3. 最终查询：从递归结果中筛选出类型为数字的节点，转换为numeric类型得到最终数值

你可以把上述语句中的表名和字段名替换成你实际使用的，运行后就能得到所有层级嵌套的value对应的数值啦！

备注：内容来源于stack exchange，提问作者Technaton