I have an sql query to get users and total

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]
    })

And this query return users as array of objects with total in each user object

[
  {
    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'
  }
]

What i should to do to get something like that

{
   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,
      }
   ]
}

CodePudding user response：

you can use row_to_json & json_agg to convert all results to a json list of objects.

This won't give you exactly what you desire, but close. The query below should return results in the format [{total: xxx, users: [user-records]}], so in your application code you have to get the first row returned by the database query.

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

CodePudding user response：

As I understand, you generate JSON data on PostgreSQL. In your query, you didn't write JSON generation codes. Ok, I wrote two samples for you:

Sample 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 

This query results

[
  {
    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'
  }
]

Sample 2: (You needed)

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 

This query results in your needed format:

{
   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,
      }
   ]
}