PostgreSQL查询优化：将统计总数total移至结果外层

Got it, let's adjust your query to get the exact output structure you're after. The core issue here is separating the total count from individual paginated records instead of embedding it in every row. Here's how to fix it:

Direct Query Solution #

SELECT json_build_object(
    'total', (SELECT COUNT(*) FROM notifications WHERE title ILIKE '%a%'),
    'data', array_to_json(array_agg(row_to_json(t)))
)
FROM (
    SELECT id, title 
    FROM notifications 
    WHERE title ILIKE '%a%' 
    LIMIT 5 OFFSET 1
) t;

Breakdown of how this works: #

• The subquery for the total key calculates the full count of matching records (ignoring LIMIT/OFFSET), so you get the real total number of entries matching your filter.
• The main subquery t fetches only the paginated id and title fields you need, without the total cluttering each row.
• array_agg(row_to_json(t)) converts the paginated rows into a clean JSON array, which we assign to the data key.
• json_build_object wraps everything into your desired outer structure, with total at the top level and data holding the list of records.

Efficient CTE Version (Reusing Filter Logic) #

If you want to avoid running the WHERE clause twice (better for performance with large datasets), use a Common Table Expression (CTE) to reuse the filtered dataset:

WITH filtered_notifications AS (
    SELECT id, title 
    FROM notifications 
    WHERE title ILIKE '%a%'
)
SELECT json_build_object(
    'total', (SELECT COUNT(*) FROM filtered_notifications),
    'data', array_to_json(array_agg(row_to_json(t)))
)
FROM (
    SELECT id, title 
    FROM filtered_notifications
    LIMIT 5 OFFSET 1
) t;

This will output exactly the format you want:

{ "total":16, "data":[ { "title": "Sartaj ", "id": 3 }] }

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