如何从PostgreSQL jsonb列查询学生特定/全科目评估数据

我来帮你搞定这两个PostgreSQL JSONB查询需求，结合你的表结构和期望输出，直接上可行的SQL语句和解释：

1. 查询指定学生某一特定科目的评估数据 #

针对你需要查询student_id=2、subjectId=9、sessionId=3且sectionId=4的单个科目评估数据的需求，可以用以下SQL：

SELECT elem
FROM assessment_table,
     jsonb_array_elements(assessment) AS elem
WHERE sessionId = 3
  AND sectionId = 4
  AND subjectId = 9
  AND elem->>'student_id' = '2';

解释： #

• jsonb_array_elements(assessment)：把assessment列里的JSON数组拆分成独立的行元素，这样我们能单独访问每个学生的评估数据。
• 外层的sessionId、sectionId、subjectId筛选出目标科目的记录，再通过elem->>'student_id' = '2'匹配指定学生，最终返回该学生对应科目的评估JSON对象，和你期望的输出完全一致。

2. 查询指定学生所选全部科目的评估数据 #

如果需要获取student_id=2、sessionId=3且sectionId=4的所有科目评估数据，并用JSON数组形式返回，可以使用聚合函数json_agg：

SELECT json_agg(elem) AS student_assessments
FROM assessment_table,
     jsonb_array_elements(assessment) AS elem
WHERE sessionId = 3
  AND sectionId = 4
  AND elem->>'student_id' = '2';

解释： #

• 同样用jsonb_array_elements拆分评估数组，筛选出目标session和section下的所有科目记录，匹配指定学生后，json_agg(elem)会把该学生在所有科目的评估数据聚合为一个JSON数组，正好符合你期望的输出结构。

注意：将上述SQL中的assessment_table替换成你实际的表名即可。

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