在postgreSQL中獲取總計和行的欄位作為物件的兩個鍵

我有一個 sql 查詢來獲取用戶和總數

await db.query({
      text: `with cte as
                (select "id", "createdAt", "deletedAt", "role", "email", "name", "group" from "admin" 
                where (lower("name") like '%' || lower($1) || '%'))
                select * from (table cte
                order by
                    case when $2 = 'desc' then "createdAt" end desc,
                    case when $2 = 'asc' then "createdAt" end asc
                limit $3
                offset $4) sub
                right join (select count(*) from cte) c(total) on true`,
      values: [search, createdAt, limit, offset]
    })

并且此查詢將用戶作為物件陣列回傳，每個用戶物件中有總數

[
  {
    id: '135e8d05-4723-4dd7-9ae0-6e10626799f3',
    createdAt: 2022-03-27T08:34:37.636Z,
    deletedAt: null,
    role: 'root',
    email: '[email protected]',
    name: 'Test Name',
    group: 'Test Group,
    total: '4'
  }
]

我應該怎么做才能得到這樣的東西

{
   total: 4,
   users: [
      {
        id: '135e8d05-4723-4dd7-9ae0-6e10626799f3',
        createdAt: 2022-03-27T08:34:37.636Z,
        deletedAt: null,
        role: 'root',
        email: '[email protected]',
        name: 'Test Name',
        group: 'Test Group,
      }
   ]
}

uj5u.com熱心網友回復：

據我了解，您在 PostgreSQL 上生成 JSON 資料。在您的查詢中，您沒有撰寫 JSON 生成代碼。好的，我為你寫了兩個示例：

樣品 1：

with cte as
(
    select "id", "createdAt", "deletedAt", "role", "email", "name", "group" from "admin" 
    where (lower("name") like '%' || lower($1) || '%')
)
select jsonb_agg(row_to_json(t1)) from (
    select * from (table cte
    order by
        case when $2 = 'desc' then "createdAt" end desc,
        case when $2 = 'asc' then "createdAt" end asc
    limit $3
    offset $4) sub
    right join (select count(*) from cte) c(total) on true
) t1 

此查詢結果

[
  {
    id: '135e8d05-4723-4dd7-9ae0-6e10626799f3',
    createdAt: 2022-03-27T08:34:37.636Z,
    deletedAt: null,
    role: 'root',
    email: '[email protected]',
    name: 'Test Name',
    group: 'Test Group,
    total: '4'
  }
]

示例 2：（您需要）

with cte as
(
    select "id", "createdAt", "deletedAt", "role", "email", "name", "group" from "admin" 
    where (lower("name") like '%' || lower($1) || '%')
)
select row_to_json(t1) from (
    select 
        c.total,  
        jsonb_agg(row_to_json(sub)) as "users"
    from (table cte
    order by
        case when $2 = 'desc' then "createdAt" end desc,
        case when $2 = 'asc' then "createdAt" end asc
    limit $3
    offset $4) sub
    right join (select count(*) from cte) c(total) on true
    group by c.total 
) t1 

此查詢會產生您需要的格式：

{
   total: 4,
   users: [
      {
        id: '135e8d05-4723-4dd7-9ae0-6e10626799f3',
        createdAt: 2022-03-27T08:34:37.636Z,
        deletedAt: null,
        role: 'root',
        email: '[email protected]',
        name: 'Test Name',
        group: 'Test Group,
      }
   ]
}

uj5u.com熱心網友回復：

您可以使用 row_to_json 和 json_agg 將所有結果轉換為 json 物件串列。

這不會給你你想要的東西，但很接近。下面的查詢應該以 format 格式回傳結果[{total: xxx, users: [user-records]}]，因此在您的應用程式代碼中，您必須獲取資料庫查詢回傳的第一行。

with cte as (
  select 
    "id"
  , "createdAt"
  , "deletedAt"
  , "role"
  , "email"
  , "name"
  , "group" 
  from "admin" 
  where (lower("name") like '%' || lower($1) || '%')
)
select * 
from (
  select 
    json_agg(
      row_to_json(cte.*)
      order by 
        case
          when $2 = 'desc' then "createdAt" desc
          when $2 = 'asc' then "createdAt" asc
        end   
    ) users 
  from cte
  order by
    case 
      when $2 = 'desc' then "createdAt" desc
      when $2 = 'asc' then "createdAt" asc
    end
  limit $3
  offset $4
) sub
right join (
  select count(*) from cte
) c(total) 
        on true

標籤：sql PostgreSQL

上一篇：PostgreSQL：更新json問題

下一篇：PostgreSQL具有某些特定約束的多對多關系