PostgreSQL实现带过滤属性统计的分页产品查询求助

现有表结构 #

CREATE TABLE product (
    product_id serial PRIMARY KEY,
    name VARCHAR ( 50 ),
    size VARCHAR ( 50 )
);

CREATE TABLE country (
    country_id serial PRIMARY KEY,
    name VARCHAR ( 50 ),
    product_id INT
);

CREATE TABLE color (
    color_id serial PRIMARY KEY,
    name VARCHAR ( 50 ),
    product_id INT
);

需求说明 #

• 查询结果需包含meta和result两个对象；
• result需按10条数据分页，meta需包含过滤后的产品总数、产品各属性的可选值及对应数量；
• 过滤国家/颜色时，需显示该属性的所有可选值及对应计数，而非仅当前过滤项；
• 过滤某一属性（如颜色）时，另一属性（如国家）仅显示该过滤条件下有效的选项；

期望返回结构示例：

{
    "meta": { 
        "count" : 200,
        "next_page": true,
        "colors": [
            {"id": 1, "name": "red", "count": 5}, 
            {"id": 2, "name": "white", "count": 10}
        ],
        "countries": [
            {"id": 1, "name": "Germany", "count": 120},
            {"id": 2, "name": "Albania", "count": 201}
        ],
        "sizes": [
            {"id": 1, "name": "Big", "count": 45},
            {"id": 2, "name": "Small", "count": 63}
        ]
    },
    "result": [
        {
            "product_name" : "Milk",
            "color": "White",
            "country": "Germany"
        },
        ...
    ]
}

已实现代码（不完整） #

WITH results as (
    SELECT 
        product.id,
        product.name,
        product.size,
        color.name,
        country.name
    FROM product
    LEFT JOIN color ON color.product_id = product.id
    LEFT JOIN country ON country.product_id = product.id
    WHERE color.name = ANY('{White}')
)
SELECT
(
    SELECT 
    jsonb_build_object(
        'count', count.full_count,
        'next_page', count.full_count - (1 * 10) > 0
    )
    FROM (SELECT count(id) AS full_count FROM results) AS count
) AS meta,
(
    SELECT jsonb_agg(result_rows)
    FROM 
        (SELECT * FROM results
        LIMIT 10
        OFFSET (1-1) * 10) AS result_rows
) AS result

问题描述 #

当前代码无法在meta中实现颜色、国家的名称及计数统计，请求补充完善SQL，使其满足类似Ebay搜索页的动态过滤属性计数效果，使用最新版PostgreSQL。

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