Postgresql jsonb_agg 子查询排序

使用PostgreSQL的jsonb_agg函数可以将子查询的结果按照指定的排序方式进行聚合，并生成一个包含所有结果的JSON数组。这个函数在处理包含JSON数据的表时非常有用，可以将多个行的JSON数据合并为一个数组，方便后续的处理和分析。

sql
SELECT jsonb_agg(student)
FROM (
    SELECT jsonb_build_object('name', name, 'total_score', total_score) AS student
    FROM students
) AS subquery;

sql
SELECT jsonb_agg(student)
FROM (
    SELECT jsonb_build_object('name', name, 'total_score', total_score) AS student
    FROM students
    ORDER BY total_score DESC
) AS subquery;

sql
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    scores JSONB
);
INSERT INTO students (name, scores)
VALUES
    ('Alice', '[{"course": "Math", "score": 90}, {"course": "English", "score": 85}]'),
    ('Bob', '[{"course": "Math", "score": 95}, {"course": "English", "score": 80}]'),
    ('Charlie', '[{"course": "Math", "score": 85}, {"course": "English", "score": 90}]');
SELECT jsonb_agg(student)
FROM (
    SELECT jsonb_build_object('name', name, 'total_score', total_score) AS student
    FROM (
        SELECT name, SUM((scores->>'score')::INT) AS total_score
        FROM students
        GROUP BY name
    ) AS subquery
    ORDER BY total_score DESC
) AS subquery;