postgreAggregate函数调用不能嵌套.我如何在一个很好的json格式格式化这个查询? &g

Question

我试图创建一个psql查询，返回数据在一个很好的json格式。最初的项目，我们使用sequelize，我试图将其转换为纯psql。我正在寻找的格式如下-

...
vehicles:
[
{
services: 
[
{
name: "example"
duration: "example"
}
]
id: 1
registration: "example"
}
]
...

下面是我第一次尝试，但得到了错误。

SELECT
"Bookings"."id",
"Bookings"."startTime",
"Bookings"."code",
"Bookings"."statusId",
"Bookings"."address",
"Bookings"."latitude",
"Bookings"."longitude",
"Bookings"."createdAt",
"Bookings"."overridePrice",

json_build_object(
'fullName', "customer"."fullName",
'id', "customer"."id"
) AS "customer",

json_agg(json_build_object(
'fullName', "serviceProvider"."fullName", 
'id', "serviceProvider"."id"
)) AS "serviceProvider",

-- Array of vehicles, this is where the issue lies
json_agg(
json_build_object(
'services', json_agg(
json_build_object(
'name', "ServiceHistories"."name",
'duration', "ServiceHistories"."duration"
)
),
'id', "Vehicles"."id", 
'registration', "Vehicles"."registration"
)) AS "vehicles",
--  Everything else works fine

json_agg(
json_build_object(
'amount', "payments"."amount",
'amountFromGiftCard', "payments"."amountFromGiftCard"
)
) AS "payments"

FROM
"Bookings",
"Users" AS "customer",
"Users" AS "serviceProvider",
"BookingServiceProviders",
"BookingVehicleServices",
"BookingVehicles",
"Vehicles",
"Payments" AS "payments",
"ServiceHistories"
WHERE
"Bookings"."isDeleted" IS NOT true AND
"Bookings"."statusId" != 4 AND
"Bookings"."customerId" = "customer"."id" AND "customer"."userTypeId" = 1 AND
"Bookings"."id" = "BookingServiceProviders"."BookingId" AND
"BookingServiceProviders"."UserId" = "serviceProvider"."id" AND
"Bookings"."id" = "BookingVehicles"."BookingId" AND
"Vehicles"."id" = "BookingVehicles"."VehicleId" AND
"Bookings"."id" = "payments"."bookingId" AND
"BookingVehicles"."id" = "BookingVehicleServices"."BookingVehicleId" AND
"ServiceHistories"."bookingId" = "Bookings"."id"
GROUP BY 
"Bookings"."id",
"customer"."id",
"customer"."fullName"
Order by "startTime" ASC

在做一些谷歌搜索后，我发现我可能不得不为服务数组做一个子查询。下面是我尝试的，但是查询无限期地运行。

SELECT
"Bookings"."id",
"Bookings"."startTime",
"Bookings"."code",
"Bookings"."statusId",
"Bookings"."address",
"Bookings"."latitude",
"Bookings"."longitude",
"Bookings"."createdAt",
"Bookings"."overridePrice",

json_build_object(
'fullName', "customer"."fullName",
'id', "customer"."id"
) AS "customer",

json_agg(json_build_object(
'fullName', "serviceProvider"."fullName", 
'id', "serviceProvider"."id"
)) AS "serviceProvider",

-- Issue lies here
json_agg(
json_build_object(
'services', (
SELECT 
json_agg(json_build_object(
'name', "ServiceHistories"."name",
'duration', "ServiceHistories"."duration"
))
FROM
"ServiceHistories"
WHERE
"ServiceHistories"."bookingId" = "Bookings"."id"
),
'id', "Vehicles"."id", 
'registration', "Vehicles"."registration"
)) AS "vehicles",
-- End of issue

json_agg(
json_build_object(
'amount', "payments"."amount",
'amountFromGiftCard', "payments"."amountFromGiftCard"
)
) AS "payments"

FROM
"Bookings",
"Users" AS "customer",
"Users" AS "serviceProvider",
"BookingServiceProviders",
"BookingVehicleServices",
"BookingVehicles",
"Vehicles",
"Payments" AS "payments"
WHERE
"Bookings"."isDeleted" IS NOT true AND
"Bookings"."statusId" != 4 AND
"Bookings"."customerId" = "customer"."id" AND "customer"."userTypeId" = 1 AND
"Bookings"."id" = "BookingServiceProviders"."BookingId" AND
"BookingServiceProviders"."UserId" = "serviceProvider"."id" AND
"Bookings"."id" = "BookingVehicles"."BookingId" AND
"Vehicles"."id" = "BookingVehicles"."VehicleId" AND
"Bookings"."id" = "payments"."bookingId" AND
"BookingVehicles"."id" = "BookingVehicleServices"."BookingVehicleId"
GROUP BY 
"Bookings"."id",
"customer"."id",
"customer"."fullName"
Order by "startTime" ASC

如果有人能帮助我或指给我正确的方向，我将不胜感激。

** EDIT **

我通过添加更多关于表如何关联的where子句来让它工作，现在服务在车辆数组中正确返回。不幸的是，它的速度非常慢。我让它工作得更快了，但是当它只应该是与特定预订相关的服务时，却出现了重复的服务。

SELECT
bookings."id",
bookings."startTime",
bookings."code",
bookings."statusId",
bookings."address",
bookings."latitude",
bookings."longitude",
bookings."createdAt",
bookings."overridePrice",

json_build_object(
'fullName', "customer"."fullName",
'id', "customer"."id"
) AS "customer",

json_agg(json_build_object(
'fullName', "serviceProvider"."fullName", 
'id', "serviceProvider"."id"
)) AS "serviceProvider",

json_agg(
json_build_object(
'amount', "payments"."amount",
'amountFromGiftCard', "payments"."amountFromGiftCard"
)
) AS "payments",

(
SELECT 
(nested_vehicles)
FROM (
SELECT
"Vehicles".id as vehicle_id,
"Vehicles".registration,
(
SELECT 
json_agg(nested_services)
FROM (
SELECT
"ServiceHistories"."id",
"ServiceHistories"."name",
"ServiceHistories"."duration"
FROM 
"ServiceHistories", "BookingVehicleServices", "BookingVehicles"
WHERE 
"ServiceHistories"."vehicleId" = "Vehicles"."id" AND 
"ServiceHistories"."id" = "BookingVehicleServices"."ServiceHistoryId" AND
bookings.id = "BookingVehicles"."BookingId" AND
"Vehicles"."id" = "BookingVehicles"."VehicleId" AND
"ServiceHistories"."bookingId" = bookings.id 

) AS nested_services
) AS services
FROM 
"Vehicles", "BookingVehicles"
WHERE "Vehicles"."id" = "BookingVehicles"."VehicleId" AND bookings.id = "BookingVehicles"."BookingId"
GROUP BY "Vehicles"."id"
) AS nested_vehicles
) AS vehicles
FROM 
"Bookings" as bookings,
"Users" AS "customer",
"Users" AS "serviceProvider",
"BookingServiceProviders",
"Payments" AS "payments"
WHERE
bookings."isDeleted" IS NOT true AND
bookings."statusId" != 4 AND
bookings."customerId" = "customer"."id" AND "customer"."userTypeId" = 1 AND
bookings."id" = "BookingServiceProviders"."BookingId" AND
"BookingServiceProviders"."UserId" = "serviceProvider"."id" AND
bookings."id" = "payments"."bookingId"
GROUP BY 
bookings."id",
"customer"."id",
"customer"."fullName"
Order by "startTime" ASC

我不知道该怎么做。我可能只是单独返回服务，然后在外部格式化它们:/

Answer 1

错误:聚合函数调用不能嵌套

不确定最佳实践，但我使用子查询或公共表表达式(CTE)。

第一次聚合函数调用来自子查询/CTE，第二次聚合函数调用来自查询。

-- Common Table Expressions
WITH regional_sales AS (
SELECT region, SUM(amount) AS total_sales
FROM orders
GROUP BY region
), top_regions AS (
SELECT region
FROM regional_sales
WHERE total_sales > (SELECT SUM(total_sales)/10 FROM regional_sales)
)
SELECT region,
product,
SUM(quantity) AS product_units,
SUM(amount) AS product_sales
FROM orders
WHERE region IN (SELECT region FROM top_regions)
GROUP BY region, product;