PostgreSQL中从JSON数组提取值并生成序号的查询问题

DO块是PostgreSQL中执行无返回值PL/pgSQL代码的结构，无法直接返回查询结果，这就是你报错的原因。要实现提取JSON数组值并生成序号的需求，直接用普通SELECT语句结合JSON处理函数即可，无需创建函数。

假设你的表名为device_table，存储JSON数组的文本字段为json_text，数组结构类似[{"sn":"DEV001"},{"sn":"DEV002"}]，可以用以下脚本：

SELECT
  row_number() OVER () AS 序号,
  (jsonb_array_elements(json_text::jsonb)->>'sn') AS 设备序列号
FROM device_table;

如果需要按设备序列号排序生成序号，只需在OVER()中添加排序规则：

SELECT
  row_number() OVER (ORDER BY (jsonb_array_elements(json_text::jsonb)->>'sn')) AS 序号,
  (jsonb_array_elements(json_text::jsonb)->>'sn') AS 设备序列号
FROM device_table;

关键说明 #

• json_text::jsonb：将文本字段转换为JSONB类型，方便后续数组解析
• jsonb_array_elements()：展开JSON数组为多行记录
• ->>'sn'：提取JSON对象中sn字段的文本值
• row_number() OVER ()：为每行生成连续序号

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