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元素。使用

因为解决方案的执行计划不同，所以您必须选择最适合您的需求的解决方案。在大多数情况下，我会选择第一个选项。它更紧凑，更易于编写和维护。选择第二个的唯一原因是需要使用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