My query is given below
SELECT w.payload,
Count('payload') OVER () AS ROWCOUNT
FROM wholesale_confirmation.wholesale_order_confirmation w
WHERE w.delivery_date = COALESCE(NULL, w.delivery_date)
AND w.ship_to_location_id = COALESCE(NULL, w.ship_to_location_id)
AND w.order_raised_date = COALESCE(NULL, w.order_raised_date)
AND w.ship_from_location_id = COALESCE(NULL, w.ship_from_location_id)
LIMIT 10
OFFSET 0;
which is giving me result like this:
I want instead {"payload:"[payload1,payload2,payload3],"rowcount":n}
.
Postgres version 10.3, payload
data type is jsonb
COALESCE(NULL, w.delivery_date)
boils down to just w.delivery_date
.
Consequently WHERE w.delivery_date = COALESCE(NULL, w.delivery_date)
boils down to WHERE w.delivery_date IS NOT NULL
.
Count('payload') OVER () AS ROWCOUNT
is just a noisy way of saying count(*) OVER () AS rowcount
and returns the total row count of the result.
Your current query, simplified:
SELECT payload, count(*) OVER () AS rowcount
FROM wholesale_confirmation.wholesale_order_confirmation
WHERE delivery_date IS NOT NULL
AND ship_to_location_id IS NOT NULL
AND order_raised_date IS NOT NULL
AND ship_from_location_id IS NOT NULL
LIMIT 10;
To get a JSON object like in your updated question, containing one array of JSON objects and the total count of rows:
SELECT json_build_object('payload', jsonb_agg(payload), 'rowcount', min(rowcount))
FROM (
SELECT payload, count(*) OVER () AS rowcount
FROM wholesale_confirmation.wholesale_order_confirmation
WHERE delivery_date IS NOT NULL
AND ship_to_location_id IS NOT NULL
AND order_raised_date IS NOT NULL
AND ship_from_location_id IS NOT NULL
LIMIT 10
) sub;
If you are dealing with many rows, the performance with LIMIT
/ OFFSET
degrades. Consider a more sophisticated pagination technique: