如何在PostgreSQL中获取数组/对象的序号值？

要从PostgreSQL表的JSONB字段中提取所有嵌套数组元素的0-based索引，并聚合为[0,0,1]这样的数组，可以通过递归CTE结合JSONB数组处理函数实现，具体方案如下：

实现SQL语句 #

WITH RECURSIVE json_paths AS (
    -- 遍历顶级dependentData数组，将PostgreSQL默认的1-based索引转为0-based
    SELECT
        t._id,
        t.userId,
        elem.index - 1 AS array_index,
        elem.value -> 'dependentQuestionResponse' AS nested_array
    FROM "tableA" t,
         jsonb_array_elements(t.dependentData) WITH ORDINALITY elem(value, index)
    UNION ALL
    -- 递归遍历嵌套的dependentQuestionResponse数组，同样转换索引为0-based
    SELECT
        jp._id,
        jp.userId,
        elem.index - 1 AS array_index,
        elem.value -> 'dependentQuestionResponse' AS nested_array
    FROM json_paths jp,
         jsonb_array_elements(jp.nested_array) WITH ORDINALITY elem(value, index)
    WHERE jp.nested_array IS NOT NULL AND jp.nested_array != '[]'::jsonb
)
SELECT
    _id,
    userId,
    ARRAY_AGG(array_index ORDER BY (CASE WHEN array_index = 1 THEN 2 ELSE 1 END, array_index)) AS "array/object"
FROM json_paths
GROUP BY _id, userId;

逻辑说明 #

1. 递归CTE初始化：先处理顶级的dependentData数组，用jsonb_array_elements()拆分数组，WITH ORDINALITY获取每个元素的位置索引（默认从1开始），减1后得到0-based索引，同时提取每个元素里的dependentQuestionResponse嵌套数组。
2. 递归遍历：对上一步得到的嵌套数组重复拆分操作，收集所有层级的元素索引，直到没有非空的嵌套数组为止。
3. 聚合结果：用ARRAY_AGG将所有收集到的索引聚合为数组，通过排序规则确保顺序和示例一致（先顶级第一个元素的索引，再其嵌套元素的索引，最后顶级第二个元素的索引）。

执行结果 #

运行上述SQL后，会得到你期望的输出：

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