CODEWITHSUNDEEP
postgresqlpostgresql-9.4
Feroz Siddiqui
Feroz Siddiqui

Reputation: 4116

Postgres Sql fetch total nos of record with limit in query

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

this is my table design

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');

Upvotes: 1

Views: 295

Answers (1)

Ivan Mogila
Ivan Mogila

Reputation: 467

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

Upvotes: 2

Related Questions