Postgres query result to json object

Question

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

Please 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. — Erwin Brandstetter, Apr 08 '18 at 03:21
Thanks for the guidance, Postgres version 10.3, payload data type is jsonb — pravinbhogil, Apr 08 '18 at 11:49
As 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, Apr 08 '18 at 11:59

score 4 · Accepted Answer · edited Jun 20 '20 at 09:12

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;

Best way to get result count before LIMIT was applied

If you are dealing with many rows, the performance with LIMIT / OFFSET degrades. Consider a more sophisticated pagination technique:

Optimize query with OFFSET on large table

Thanks 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, Apr 08 '18 at 10:00
@user75904: Consider the updated solution for your updated question. Please edit the question to include the information from your comments. — Erwin Brandstetter, Apr 08 '18 at 13:07
@pravinbhogil: If that answers your question, consider accepting it. — Erwin Brandstetter, Apr 13 '18 at 23:39
@pravinbhogil: [How does accepting an answer work?](https://meta.stackexchange.com/q/5234/169168) — Erwin Brandstetter, May 17 '18 at 00:21

score 1 · Answer 2 · answered Apr 08 '18 at 00:43

1

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

answered Apr 08 '18 at 00:43

Michel Milezzi

10,087
3
21
36

Thanks 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} – pravinbhogil Apr 08 '18 at 10:10

Postgres query result to json object

2 Answers2