Postgres-json聚合函数调用不能嵌套

Question

我有这个select语句，我想用另一个嵌套的json-agregate函数将它们导出到json数组，但postgres说"聚合函数调用不能嵌套"；，我不知道该怎么做。

select json_agg(json_build_object(
'plan_number', plan.id,
'plan_carrier_code', carrier_plan.carrier_code,
'plan_name', plan.plan_name,
'plan_mac', mac.mac_name,
'plan_termination_date', plan.termination_date,
'plan_mod_start_date', plan.mod_start_date,
'plan_mod_user', plan.mod_user,
'plan_opt_brandcode_g_on_mn_as_generic_copay', plan.opt_brandcode_g_on_mn_as_generic_copay, 
'plan_opt_exclude_daw2_from_ded_calculations', plan.opt_exclude_daw2_from_ded_calculations,
'plan_opt_exclude_daw2_from_oop_calculations', plan.opt_exclude_daw2_from_oop_calculations,
'plan_opt_limit_patient_pay_to_pay', plan.opt_limit_patient_pay_to_pay, 
'plan_opt_only_pay_primary_claims', plan.opt_only_pay_primary_claims,
'plan_opt_allow_discontinued_drugs', plan.opt_allow_discontinued_drugs,
'plan_opt_allow_negative_payments_to_pharmacy', plan.opt_allow_negative_payments_to_pharmacy,   
'plan_opt_process_y_drugs_as_preferred', plan.opt_process_y_drugs_as_preferred,
'plan_opt_reject_otc', plan.opt_reject_otc,
'plan_opt_reject_repackaged_drugs', plan.opt_reject_repackaged_drugs,   
'plan_opt_test_only', plan.opt_test_only,
'plan_opt_cost_effective_pricing', plan.opt_cost_effective_pricing,
'plan_opt_original_mony_for_copay', plan.opt_original_mony_for_copay,
'plan_daw_differential', json_agg(json_build_object(
'plan_daw_differential_daw_code', plan_daw_differential.daw_code,
'plan_daw_differential_claim_type', plan_daw_differential.claim_type,
'plan_daw_differential_updated_at', plan_daw_differential.updated_at,
'plan_daw_differential_updated_by', plan_daw_differential.updated_by
))
)) as plan
from splan.groups_plan_list gpl
left join splan.plan plan on plan.id = gpl.plan_id
left join splan.carrier carrier_plan on carrier_plan.id = plan.carrier_id
left join splan.plan_daw_differential plan_daw_differential on plan_daw_differential.parent_id = plan.id
left join sdrug.mac mac on mac.id = plan.mac_id
where gpl.parent_id = 69;

但它给了我一个错误，说"；聚合函数调用不能嵌套"；

预期结果可以是：

[
{
"plan_number":1,
"plan_carrier_code":"lltest",
"plan_daw_differential":[
{
"plan_daw_differential_daw_code":"0505",
"plan_daw_differential_claim_type":"02"
},
{
"plan_daw_differential_daw_code":"0505",
"plan_daw_differential_claim_type":"02"
}
]
}
]

Answer 1

选项：

1. 使用子查询(或连接横向(为每个计划元素评估plan_daw_differential元素
2. 使用GROUP BY plan... gpl... carrier_plan... mac...

由于解决方案在执行计划上有所不同，您必须选择最符合您需求的解决方案。在大多数情况下，我会选择第一个选项。它更紧凑，更易于编写和维护。选择第二个的唯一原因是需要使用plan_daw_differential表中的数据筛选计划。

示例：

子查询：

select json_agg(json_build_object(
'plan_number', plan.id,
'plan_carrier_code', carrier_plan.carrier_code,
'plan_name', plan.plan_name,
'plan_mac', mac.mac_name,
'plan_termination_date', plan.termination_date,
'plan_mod_start_date', plan.mod_start_date,
'plan_mod_user', plan.mod_user,
'plan_opt_brandcode_g_on_mn_as_generic_copay', plan.opt_brandcode_g_on_mn_as_generic_copay, 
'plan_opt_exclude_daw2_from_ded_calculations', plan.opt_exclude_daw2_from_ded_calculations,
'plan_opt_exclude_daw2_from_oop_calculations', plan.opt_exclude_daw2_from_oop_calculations,
'plan_opt_limit_patient_pay_to_pay', plan.opt_limit_patient_pay_to_pay, 
'plan_opt_only_pay_primary_claims', plan.opt_only_pay_primary_claims,
'plan_opt_allow_discontinued_drugs', plan.opt_allow_discontinued_drugs,
'plan_opt_allow_negative_payments_to_pharmacy', plan.opt_allow_negative_payments_to_pharmacy,   
'plan_opt_process_y_drugs_as_preferred', plan.opt_process_y_drugs_as_preferred,
'plan_opt_reject_otc', plan.opt_reject_otc,
'plan_opt_reject_repackaged_drugs', plan.opt_reject_repackaged_drugs,   
'plan_opt_test_only', plan.opt_test_only,
'plan_opt_cost_effective_pricing', plan.opt_cost_effective_pricing,
'plan_opt_original_mony_for_copay', plan.opt_original_mony_for_copay,
'plan_daw_differential', ( 
SELECT
json_agg(json_build_object(
'plan_daw_differential_daw_code', plan_daw_differential.daw_code,
'plan_daw_differential_claim_type', plan_daw_differential.claim_type,
'plan_daw_differential_updated_at', plan_daw_differential.updated_at,
'plan_daw_differential_updated_by', plan_daw_differential.updated_by
))
FROM
splan.plan_daw_differential plan_daw_differential 
WHERE
plan_daw_differential.parent_id = plan.id )
)) as plan
from 
splan.groups_plan_list gpl
left join splan.plan plan on plan.id = gpl.plan_id
left join splan.carrier carrier_plan on carrier_plan.id = plan.carrier_id
left join sdrug.mac mac on mac.id = plan.mac_id
where
gpl.parent_id = 69;

分组：

select json_agg(json_build_object(
'plan_number', plan_number,
'plan_carrier_code', plan_carrier_code,
'plan_name', plan_name,
'plan_mac', plan_mac,
'plan_termination_date', plan_termination_date,
'plan_mod_start_date', plan_mod_start_date,
'plan_mod_user', plan_mod_user,
'plan_opt_brandcode_g_on_mn_as_generic_copay', plan_opt_brandcode_g_on_mn_as_generic_copay,
'plan_opt_exclude_daw2_from_ded_calculations', plan_opt_exclude_daw2_from_ded_calculations,
'plan_opt_exclude_daw2_from_oop_calculations', plan_opt_exclude_daw2_from_oop_calculations,
'plan_opt_limit_patient_pay_to_pay', plan_opt_limit_patient_pay_to_pay,
'plan_opt_only_pay_primary_claims', plan_opt_only_pay_primary_claims,
'plan_opt_allow_discontinued_drugs', plan_opt_allow_discontinued_drugs,
'plan_opt_allow_negative_payments_to_pharmacy', plan_opt_allow_negative_payments_to_pharmacy,
'plan_opt_process_y_drugs_as_preferred', plan_opt_process_y_drugs_as_preferred,
'plan_opt_reject_otc', plan_opt_reject_otc,
'plan_opt_reject_repackaged_drugs', plan_opt_reject_repackaged_drugs,
'plan_opt_test_only', plan_opt_test_only,
'plan_opt_cost_effective_pricing', plan_opt_cost_effective_pricing,
'plan_opt_original_mony_for_copay', plan_opt_original_mony_for_copay,
'plan_daw_differential', draw_differential_arr
)) as plan
FROM (
SELECT
plan.id AS plan_number,
carrier_plan.carrier_code AS plan_carrier_code,
plan.plan_name AS plan_name,
mac.mac_name AS plan_mac,
plan.termination_date AS plan_termination_date,
plan.mod_start_date AS plan_mod_start_date,
plan.mod_user AS plan_mod_user,
plan.opt_brandcode_g_on_mn_as_generic_copay AS plan_opt_brandcode_g_on_mn_as_generic_copay,
plan.opt_exclude_daw2_from_ded_calculations AS plan_opt_exclude_daw2_from_ded_calculations,
plan.opt_exclude_daw2_from_oop_calculations AS plan_opt_exclude_daw2_from_oop_calculations,
plan.opt_limit_patient_pay_to_pay AS plan_opt_limit_patient_pay_to_pay,
plan.opt_only_pay_primary_claims AS plan_opt_only_pay_primary_claims,
plan.opt_allow_discontinued_drugs AS plan_opt_allow_discontinued_drugs,
plan.opt_allow_negative_payments_to_pharmacy AS plan_opt_allow_negative_payments_to_pharmacy,
plan.opt_process_y_drugs_as_preferred AS plan_opt_process_y_drugs_as_preferred,
plan.opt_reject_otc AS plan_opt_reject_otc,
plan.opt_reject_repackaged_drugs AS plan_opt_reject_repackaged_drugs,
plan.opt_test_only AS plan_opt_test_only,
plan.opt_cost_effective_pricing AS plan_opt_cost_effective_pricing,
plan.opt_original_mony_for_copay AS plan_opt_original_mony_for_copay,
json_agg(json_build_object(
'plan_daw_differential_daw_code', plan_daw_differential.daw_code,
'plan_daw_differential_claim_type', plan_daw_differential.claim_type,
'plan_daw_differential_updated_at', plan_daw_differential.updated_at,
'plan_daw_differential_updated_by', plan_daw_differential.updated_by
)) as draw_differential_arr
FROM
splan.groups_plan_list gpl
left join splan.plan plan on plan.id = gpl.plan_id
left join splan.carrier carrier_plan on carrier_plan.id = plan.carrier_id
left join splan.plan_daw_differential plan_daw_differential on plan_daw_differential.parent_id = plan.id
left join sdrug.mac mac on mac.id = plan.mac_id
WHERE
gpl.parent_id = 69
GROUP BY
plan.id,
carrier_plan.carrier_code,
plan.plan_name,
mac.mac_name,
plan.termination_date,
plan.mod_start_date,
plan.mod_user,
plan.opt_brandcode_g_on_mn_as_generic_copay,
plan.opt_exclude_daw2_from_ded_calculations,
plan.opt_exclude_daw2_from_oop_calculations,
plan.opt_limit_patient_pay_to_pay,
plan.opt_only_pay_primary_claims,
plan.opt_allow_discontinued_drugs,
plan.opt_allow_negative_payments_to_pharmacy,
plan.opt_process_y_drugs_as_preferred,
plan.opt_reject_otc,
plan.opt_reject_repackaged_drugs,
plan.opt_test_only,
plan.opt_cost_effective_pricing,
plan.opt_original_mony_for_copay
) AS plan_row