PostgreSQL查询结果格式调整：将多行payload转为数组格式需求

嘿，我来帮你把查询改成你想要的{"payload": [payload1,payload2,...]}格式！

你的原查询会每行返回单个payload和总行数，但我们需要把所有符合条件的payload聚合到一个数组里，再包装成目标JSON结构。这里有个直接的解决方案：

SELECT json_build_object('payload', array_agg(payload)) AS result
FROM (
  -- 先获取符合条件的前10条payload数据
  SELECT w.payload
  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
) AS filtered_payloads;

逻辑说明： #

1. 子查询部分：先筛选出符合你WHERE条件的前10条payload数据（通过LIMIT 10 OFFSET 0控制返回数量）。
2. 外层查询：用array_agg(payload)把所有子查询返回的payload合并成一个数组，再通过json_build_object创建一个键为"payload"的JSON对象，正好匹配你需要的格式。

另外提个小细节：你原WHERE条件里的COALESCE(NULL, 列名)其实等价于直接写列名 = 列名——因为当第一个参数是NULL时，COALESCE会返回第二个参数。如果这是你故意写的逻辑没问题，但如果你的实际需求是当传入参数为NULL时忽略该列的筛选，那应该把NULL换成实际的参数变量（比如COALESCE(:delivery_date_param, w.delivery_date)），这样参数为NULL时就不会过滤该列啦。

内容的提问来源于stack exchange，提问作者pravinbhogil