PostgreSQL 9.6.1：如何展开JSON数组并显示元素顺序

嘿，这个需求用PostgreSQL自带的工具就能完美搞定！针对你的场景，我们可以用json_array_elements()结合WITH ORDINALITY子句实现，完全适配PostgreSQL 9.6.1版本。

具体SQL查询代码 #

SELECT
    bt.id,
    elem.ordinality AS "order",
    elem.value AS json_object
FROM
    base_table bt,
    json_array_elements(bt.json_array) WITH ORDINALITY AS elem;

代码说明 #

• json_array_elements(bt.json_array)：这个函数会把json_array列里的JSON数组拆分成单独的JSON对象行，每一行对应数组里的一个元素。
• WITH ORDINALITY：这是实现顺序标记的关键！它会给拆分出的每一行自动添加一个ordinality列，值就是该元素在原数组中的位置（从1开始计数），正好匹配你需要的「元素顺序」。
• 隐式交叉连接：SQL里的逗号在这里等价于CROSS JOIN，会把原表base_table的每一行和拆分后的元素行关联起来，最终得到每个id对应的所有数组元素及它们的顺序。

效果验证 #

运行这个查询后，会完全符合你的期望输出：

• id=1的行生成两行，order分别为1和2，对应原数组里的{..a..}和{..b..}
• id=2、3的行各生成一行，order为1，对应它们的单个JSON元素
• id=4的行生成两行，order为1和2，对应{..e..}和{..f..}

如果你的json_array列是jsonb类型（而非json），只需要把json_array_elements换成jsonb_array_elements即可，用法完全一致。

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