Postgres query result to json object
Solution 1
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:
Solution 2
Use json_build_object:
WITH foobar AS (
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
)
SELECT
json_build_object('payload', payload, 'rowcount', rowcount)
FROM
foobar
pravinbhogil
Updated on June 12, 2022Comments
-
pravinbhogil almost 2 years
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 isjsonb
-
Erwin Brandstetter about 6 yearsPlease post data as text, never as image. Always declare your Postgres version and the table definition. Most importantly, the data type of
payload
and can it be null? And explain the objective of the query in plain English. -
pravinbhogil about 6 yearsThanks for the guidance, Postgres version 10.3, payload data type is jsonb
-
pravinbhogil about 6 yearsAs it is going to be a list of a payload as am adding a limit to my query cause I want do paging. so I need count as well so json object with a list of payload and count
-
-
pravinbhogil about 6 yearsThanks for reply ..! but as mentioned in question I want one JSON object with payload array and another variable which will say how many rows query is going to return as am applying limit and offset so I can do pagination by row count. By using your query am getting the same result as I mentioned in question it just gives me in one JSON object
-
pravinbhogil about 6 yearsThanks for the reply but as I said in question this doesn't solve my problem. As your query doing the same what it is doing different than mine is instead of new column for rowcount it is returning in same object like {"payload":"","rowcount":"","payload":"",rowcount:n} what i am looking for all payload in one array and count in another variable like this {paload:[payload 1,payload2,payload3],"rowcaount":n}
-
Erwin Brandstetter about 6 years@user75904: Consider the updated solution for your updated question. Please edit the question to include the information from your comments.
-
Erwin Brandstetter about 6 years@pravinbhogil: If that answers your question, consider accepting it.
-
Erwin Brandstetter almost 6 years@pravinbhogil: How does accepting an answer work?