cannot pass more than 100 arguments to a function to json_build_object

Question

cannot pass more than 100 arguments to a function to json_build_object, trying to build json from columns of a table.but it is giving me error that cannot pass more than 100 arguments, but argument count not exceeded 100.

code as follows:

array_agg(json_build_object
(
    'QuotaName',quota_name,
    'QuotaId',quota_id,
    'CellId',COALESCE(cell_id,0),
    'ValidPanelistCountOtherMedias',COALESCE(valid_panelist_count,0) ,
    'ValidPanelistCountMM',COALESCE(mm_valid_panelist_count,0) ,
    'Gender',COALESCE(replace(replace(replace(gender,',',':'),']',''),'[',''),''),
    'Occupation',COALESCE(replace(replace(replace(occupation_id,',',':'),']',''),'[',''),''),
    'Industry',COALESCE(replace(replace(replace(industry_id,',',':'),']',''),'[',''),''),
    'Prefecture',COALESCE(replace(replace(replace(prefecture_id,',',':'),']',''),'[',''),''),
    'Age1',COALESCE(replace(replace(replace(age,',',':'),']',''),'[',''),''),
    'Age2',COALESCE(replace(replace(replace(age2,',',':'),']',''),'[',''),''),
    'MaritalStatus',COALESCE(replace(replace(replace(marital_status,',',':'),']',''),'[',''),''),
    'HouseHoldIncome',COALESCE(replace(replace(replace(house_income_id,',',':'),']',''),'[',''),''),
    'PersonalIncome',COALESCE(replace(replace(replace(personal_income_id,',',':'),']',''),'[',''),''),
    'hasChild',COALESCE(replace(replace(replace(has_child,',',':'),']',''),'[',''),''),
    'MediaId',COALESCE(replace(replace(replace(media_id,',',':'),']',''),'[',''),''),
    'DeviceUsed',COALESCE(replace(replace(replace(device_type,',',':'),']',''),'[',''),''),
    'PanelistStatus','',
    'IR1', COALESCE(ir_1,1) ,
    'IR2', COALESCE(ir_2,1) ,
    'IR3', COALESCE(ir_3,1) ,
    'Population',COALESCE(population,0),
    'MainSurveySampleHopes',  COALESCE(sample_hope_main_survey,0) ,
    'ScreeningSurveySampleHopes', COALESCE(sample_hope_main_scr,0),
    'ParticipateIntentionMM' ,COALESCE(participate_intention_mm,0) ,
    'ParticipateIntentionOthers' ,COALESCE(participate_intention,0) ,
    'AcquisitionRate', COALESCE(acquisition_rate,0) , 
    'PCEnvironment', COALESCE(case when survey_type >3 then 1 else pc_env end,0) ,
    'NetworkEnvironment',COALESCE(case when survey_type >3 then 1 else network_env  end,0) ,
    'PCEnvironmentMM',COALESCE(case when survey_type >3 then 1 else pc_env_mm  end,0),
    'NetworkEnvironmentMM',COALESCE(case when survey_type >3 then 1 else network_env_mm  end,0) ,
    'ControlQuotient',COALESCE(control_quotient,0)/100 ,
    'ResponseofSCR24' , COALESCE(res_of_scr_24,0),
    'ResponseofSCR48' ,COALESCE(res_of_scr_48,0) ,
    'ResponseofSCR72' ,COALESCE(res_of_scr_72,0) ,  
    'ResponseofSCR168' ,COALESCE(res_of_scr_168,0),     
    'ResponseofMAIN24' ,COALESCE(res_of_main_24,0) ,        
    'ResponseofMAIN48' , COALESCE(res_of_main_48,0) ,       
    'ResponseofMAIN72' , COALESCE(res_of_main_72,0) ,       
    'ResponseofMAIN168' , COALESCE(res_of_main_168,0),        
    'ResponseofSCR24MM' ,COALESCE(res_of_scr_24_mm,0) ,
    'ResponseofSCR48MM' , COALESCE(res_of_scr_48_mm,0),
    'ResponseofSCR72MM' , COALESCE(res_of_scr_72_mm,0) ,    
    'ResponseofSCR168MM' ,COALESCE(res_of_scr_168_mm,0) ,       
    'ResponseofMAIN24MM' ,COALESCE(res_of_main_24_mm,0),        
    'ResponseofMAIN48MM' ,COALESCE(res_of_main_48_mm,0),        
    'ResponseofMAIN72MM' ,COALESCE(res_of_main_72_mm,0),        
    'ResponseofMAIN168MM' ,COALESCE(res_of_main_168_mm,0),
    'ResponseofMAINIntegrationType',0.9,-- this value is based on answer_estimate_list_details_v3
    'ParticipationIntention',COALESCE(participate_intention,0),
    'MostRecentParticipation',COALESCE(most_recent_exclusions,0)

Answer 1

I had the exact same problem earlier today. After some research, I found that JSONB results can be concatenated. So you should use JSONB_BUILD_OBJECT instead of JSON_BUILD_OBJECT. Then, split things up so you have multiple JSONB_BUILD_OBJECT calls, which are combined with ||. You'll also need JSONB_AGG for converting the results into an array.

JSONB_AGG(
    JSONB_BUILD_OBJECT (
        'QuotaName',quota_name,
        'QuotaId',quota_id,
        'CellId',COALESCE(cell_id,0),
        'ValidPanelistCountOtherMedias',COALESCE(valid_panelist_count,0) ,
        'ValidPanelistCountMM',COALESCE(mm_valid_panelist_count,0) ,
        'Gender',COALESCE(replace(replace(replace(gender,',',':'),']',''),'[',''),''),
        'Occupation',COALESCE(replace(replace(replace(occupation_id,',',':'),']',''),'[',''),''),
        'Industry',COALESCE(replace(replace(replace(industry_id,',',':'),']',''),'[',''),''),
        'Prefecture',COALESCE(replace(replace(replace(prefecture_id,',',':'),']',''),'[',''),''),
        'Age1',COALESCE(replace(replace(replace(age,',',':'),']',''),'[',''),''),
        'Age2',COALESCE(replace(replace(replace(age2,',',':'),']',''),'[',''),''),
        'MaritalStatus',COALESCE(replace(replace(replace(marital_status,',',':'),']',''),'[',''),''),
        'HouseHoldIncome',COALESCE(replace(replace(replace(house_income_id,',',':'),']',''),'[',''),''),
        'PersonalIncome',COALESCE(replace(replace(replace(personal_income_id,',',':'),']',''),'[',''),''),
        'hasChild',COALESCE(replace(replace(replace(has_child,',',':'),']',''),'[',''),''),
        'MediaId',COALESCE(replace(replace(replace(media_id,',',':'),']',''),'[',''),''),
        'DeviceUsed',COALESCE(replace(replace(replace(device_type,',',':'),']',''),'[',''),''),
        'PanelistStatus','',
        'IR1', COALESCE(ir_1,1) ,
        'IR2', COALESCE(ir_2,1) ,
        'IR3', COALESCE(ir_3,1) ,
        'Population',COALESCE(population,0),
        'MainSurveySampleHopes',  COALESCE(sample_hope_main_survey,0) ,
        'ScreeningSurveySampleHopes', COALESCE(sample_hope_main_scr,0),
        'ParticipateIntentionMM' ,COALESCE(participate_intention_mm,0) ,
        'ParticipateIntentionOthers' ,COALESCE(participate_intention,0) ,
        'AcquisitionRate', COALESCE(acquisition_rate,0) , 
        'PCEnvironment', COALESCE(case when survey_type >3 then 1 else pc_env end,0) ,
        'NetworkEnvironment',COALESCE(case when survey_type >3 then 1 else network_env  end,0) ,
        'PCEnvironmentMM',COALESCE(case when survey_type >3 then 1 else pc_env_mm  end,0),
        'NetworkEnvironmentMM',COALESCE(case when survey_type >3 then 1 else network_env_mm  end,0) ,
        'ControlQuotient',COALESCE(control_quotient,0)/100 ,
        'ResponseofSCR24' , COALESCE(res_of_scr_24,0),
        'ResponseofSCR48' ,COALESCE(res_of_scr_48,0) ,
        'ResponseofSCR72' ,COALESCE(res_of_scr_72,0) ,  
        'ResponseofSCR168' ,COALESCE(res_of_scr_168,0),     
        'ResponseofMAIN24' ,COALESCE(res_of_main_24,0) ,        
        'ResponseofMAIN48' , COALESCE(res_of_main_48,0) ,       
        'ResponseofMAIN72' , COALESCE(res_of_main_72,0) ,       
        'ResponseofMAIN168' , COALESCE(res_of_main_168,0),        
        'ResponseofSCR24MM' ,COALESCE(res_of_scr_24_mm,0) ,
        'ResponseofSCR48MM' , COALESCE(res_of_scr_48_mm,0),
        'ResponseofSCR72MM' , COALESCE(res_of_scr_72_mm,0) ,    
        'ResponseofSCR168MM' ,COALESCE(res_of_scr_168_mm,0) ,       
        'ResponseofMAIN24MM' ,COALESCE(res_of_main_24_mm,0),        
        'ResponseofMAIN48MM' ,COALESCE(res_of_main_48_mm,0),        
        'ResponseofMAIN72MM' ,COALESCE(res_of_main_72_mm,0),        
        'ResponseofMAIN168MM' ,COALESCE(res_of_main_168_mm,0)
    ) ||
    JSONB_BUILD_OBJECT (
        'ResponseofMAINIntegrationType',0.9,-- this value is based on answer_estimate_list_details_v3
        'ParticipationIntention',COALESCE(participate_intention,0),
        'MostRecentParticipation',COALESCE(most_recent_exclusions,0)
    )
)

I got this from documentation here - https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSONB-OP-TABLE:~:text=jsonb%20%7C%7C%20jsonb%20%E2%86%92%20jsonb

Look for jsonb || jsonb