Postgres Sql fetch total nos of record with limit in query

Question

I have created a table and trying to create a json array from table records in which it will return JSON Array as {"total_record":2222,data[{row 1 data},{row 2 data}]}

below is the query for the same

SELECT row_to_json(selected_records)::text AS data
FROM  (   
   SELECT min(total_count) AS total_count
        , array_to_json(array_agg(row_to_json(ROW(name,id,gender)))) AS data
   FROM (
      SELECT *
           , count(*) over () AS total_count  -- shouldn't show up in result
      FROM   test

      GROUP  BY ID
      LIMIT  1
      ) records
   ) selected_records;

but some how i am getting a json like this

{"total_count":277938,"data":[{"f1":"Carol Rau","f2":1,"f3":"M"}]}

i need column field name instead of f1,f2,f3

{"total_count":277938,"data":[{"name":"Carol Rau","id":1,"gender":"M"}]}

DDL for same:

create table public.test (name varchar, email varchar, phone bigint, address varchar, gender varchar, id serial primary key) 
INSERT INTO "public"."test" ("name", "email", "phone", "address", "gender", "id") VALUES ('Carol Rau', 'Computers, Electronics & Games', '1000000000', 'Suite 843 489 Annabell Islands, North Katlynland, UT 90089-0425', 'M', '1');
INSERT INTO "public"."test" ("name", "email", "phone", "address", "gender", "id") VALUES ('Vicente Sipes', 'Games', '1000000000', 'Suite 870 21754 Dare Ville, Thielstad, WA 97243-0588', 'M', '2');
INSERT INTO "public"."test" ("name", "email", "phone", "address", "gender", "id") VALUES ('Jadyn Kris', 'Kids', '1000000000', '5136 Harmon Dale, Jeramiemouth, VT 17688', 'M', '21');
INSERT INTO "public"."test" ("name", "email", "phone", "address", "gender", "id") VALUES ('Melyna Lueilwitz', 'Automotive, Music & Outdoors', '1000000000', 'Suite 578 0765 Paucek Meadow, Torphyborough, AR 41921', 'M', '22');
INSERT INTO "public"."test" ("name", "email", "phone", "address", "gender", "id") VALUES ('Phoebe Herzog Sr.', 'Movies', '1000000000', '82869 Carrie Ridges, East Tavaresmouth, AR 62150', 'M', '23');
INSERT INTO "public"."test" ("name", "email", "phone", "address", "gender", "id") VALUES ('Cierra Luettgen', 'Toys', '1000000000', '326 Maida Pine, Aylamouth, IL 03742', 'M', '90');

please provide DDL and sample, or better build it in db-fiddle or elsewhere — Vao Tsun
– Vao Tsun, Commented Feb 15, 2018 at 9:32

Ivan Mogila · Accepted Answer · 2018-02-15 10:02:08Z

2

EDITED

select row_to_json(t) from (
  select
    (select count(*) from public.test) as total_count,
    (select json_agg(row_to_json(tt))
       from (select name, id, gender from public.test limit 1) tt) as data
) t

edited Feb 15, 2018 at 10:02

answered Feb 15, 2018 at 9:40

Ivan Mogila

4672 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres Sql fetch total nos of record with limit in query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related