Результат запроса Postgres к объекту json

Question

Мой запрос приведен ниже

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;

что дает мне такой результат:

Я хочу вместо {"payload:"[payload1,payload2,payload3],"rowcount":n},

---

Postgres версия 10.3, payload тип данных jsonb

Answer 1

COALESCE(NULL, w.delivery_date) сводится к просто w.delivery_date,

как следствие WHERE w.delivery_date = COALESCE(NULL, w.delivery_date) сводится к WHERE w.delivery_date IS NOT NULL,

Count('payload') OVER () AS ROWCOUNT это просто шумный способ сказать count(*) OVER () AS rowcount и возвращает общее количество строк результата.

Ваш текущий запрос упрощен:

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;

Чтобы получить объект JSON, как в вашем обновленном вопросе, содержащий один массив объектов JSON и общее количество строк:

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;

• Лучший способ получить счетчик результатов до применения LIMIT

Если вы имеете дело со многими строками, производительность с LIMIT / OFFSET деградирует. Рассмотрим более сложную технику нумерации страниц:

• Оптимизировать запрос с помощью OFFSET для большой таблицы

Answer 2

Используйте 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