PostgreSQL中将JSON数组转成行：提取AA+等级学生数据

针对你的students_data表结构和嵌套JSON数据，需要逐层展开嵌套的数组和对象，才能精准筛选出rank为AA+的学生。以下是可行的SQL实现：

SELECT
    sd.doc_id,
    section.key AS class_section,
    student->>'student_name' AS student_name,
    (student->>'avg_marks')::INT AS avg_marks,
    student->>'rank' AS student_rank
FROM
    students_data sd,
    -- 展开grades数组（此处grades为单元素数组，拆转为单行）
    json_array_elements(sd.doc_data->'data'->'grades') AS grade,
    -- 将sections对象拆为键值对：键是班级名称，值是对应学生数组
    json_each(grade->'sections') AS section,
    -- 展开每个班级的学生数组，将单个学生对象拆为独立行
    json_array_elements(section.value) AS student
WHERE
    -- 筛选rank为AA+的学生记录
    student->>'rank' = 'AA+';

执行逻辑说明 #

1. 展开grades数组：通过sd.doc_data->'data'->'grades'定位到目标数组，json_array_elements将数组元素拆分为独立的数据行。
2. 拆分sections对象：grade->'sections'是包含多个班级的JSON对象，json_each将其转换为(key, value)格式的行数据，其中key为班级标识（如1A_students），value为该班级的学生数组。
3. 展开学生数组：section.value对应单个班级的学生数组，json_array_elements将每个学生对象拆分为单独的数据行。
4. 筛选与字段提取：通过WHERE子句过滤出rank为AA+的记录，同时提取所需字段，注意将avg_marks从JSON字符串转换为整数类型。

查询结果 #

执行后会返回以下符合条件的学生数据：

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