将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。

注意：请保留原有的html标签。

- pravinbhogil

---

请以文本的形式发布数据，而不是图像。始终声明您的Postgres版本和表定义。最重要的是，payload的数据类型是什么，它可以为空吗？并用简单的英语解释查询的目的。 - Erwin Brandstetter

感谢您的指导，Postgres版本为10.3，有效载荷数据类型为jsonb。 - pravinbhogil

由于我要分页，因此将查询限制为有效载荷列表。因此，我需要计数，因此 JSON 对象具有有效载荷和计数列表。 - pravinbhogil

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对象数组和行的总数： ```html 要获得一个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的查询

- Erwin Brandstetter

---

谢谢回复！但是正如问题中所提到的，我想要一个带有有效负载数组的JSON对象和另一个变量，该变量将说明查询将返回多少行，因为我正在应用限制和偏移量，所以我可以通过行计数进行分页。使用您的查询，我得到了与我在问题中提到的相同结果，它只是在一个JSON对象中给出了结果。 - pravinbhogil

@user75904：请考虑针对您更新后的问题的更新解决方案。请编辑问题以包含您评论中的信息。 - Erwin Brandstetter

如果这回答了你的问题，请考虑接受它。 - Erwin Brandstetter

@pravinbhogil: 接受答案的工作原理是什么？ - Erwin Brandstetter

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 

- Michel Milezzi

---

谢谢您的回复，但正如我在问题中所说，这并不能解决我的问题。您的查询与我的查询不同之处在于，它没有为行数创建新列，而是将其返回到同一对象中，例如 {"payload":"","rowcount":"","payload":"",rowcount:n}。我正在寻找的是将所有负载放入一个数组中，并将计数放入另一个变量中，就像这样 {paload:[payload 1,payload2,payload3],"rowcaount":n}。 - pravinbhogil