json - 计算所有行并在单个 json 对象中显示有限数量的行

Question

我的查询是

select array_to_json(array_agg(row_to_json(t))) from (
    select count(*) over() as total, id,title
    FROM notifications 
    WHERE  title ilike '%a%'
LIMIT 5 OFFSET 1)t

输出是

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

结果包括对象中的总数，但我想将其移到对象之外。

我期望的答案是

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

我也试过

with tmp_notifications as (
    SELECT id,title,description,img_url,link,created_on from notifications 
    where title ilike '%universi%'
    ) 
    select row_to_json(t) from (
        select count(*) as data_count,
        (json_agg(json_build_object('description',description,'title',title,'created_on',created_on,
        'link',link,'img_url',img_url,'id',id)) 
         ) as data from tmp_notifications limit 1
        )t

但限制在这里不起作用。

Answer 1

使用json_build_object()和 json_agg() :

select json_build_object('total', count(*), 'data', json_agg(to_json(t)))
from (
    select id, title
    from notifications 
    where  title ilike '%a%'
    ) t

请参阅 DbFiddle. 中的完整示例

---

您可以使用 WITH 语句来计算所有过滤的行，但只显示有限的行数，例如:

with filtered as (
    select id, title
    from notifications 
    where title ilike '%a%'
)

select json_build_object(
    'total', (select count(*) from filtered), 
    'data', json_agg(to_json(t)))
from (
    select id, title
    from filtered
    order by id
    limit 2
    ) t

DbFiddle.