[PERFORM] array size exceeds the maximum allowed (1073741823) when building a json

[Nicolas Paris]

Hello,

I run a query transforming huge tables to a json document based on a period.
It works great for a modest period (little dataset).
However, when increasing the period (huge dataset) I get this error:

SQL ERROR[54000]
ERROR: array size exceeds the maximum allowed (1073741823)


Thanks by advance,

Informations:

postgresql 9.4
shared_buffers = 55GB
64bit Red Hat Enterprise Linux Server release 6.7

the query:
 WITH sel AS
  (SELECT ids_pat,
          ids_nda
   FROM eds.nda
   WHERE (dt_deb_nda >= '20150101'
          AND dt_deb_nda <= '20150401')),
      diag AS
  ( SELECT ids_nda_rum,
           json_agg(diago) AS diago,
           count(1) AS total
   FROM
     (SELECT ids_nda_rum,
             json_build_object( 'cd_cim', cd_cim,
'lib_cim',lib_typ_diag_tr, 'dt_cim',dt_exec) AS diago
      FROM eds.fait_diag_tr
      WHERE ids_nda IN
          (SELECT ids_nda
           FROM sel)
      ORDER BY dt_exec) AS diago2
   GROUP BY ids_nda_rum),
      act AS
  ( SELECT ids_nda_rum,
           json_agg(acto) AS acto,
           count(1) AS total
   FROM
     ( SELECT ids_nda_rum,
              json_build_object( 'cd_act',cd_ccam, 'dt_act',dt_exec) AS acto
      FROM eds.fait_act_tr
      WHERE ids_nda IN
          (SELECT ids_nda
           FROM sel)
      ORDER BY dt_exec) AS acto2
   GROUP BY ids_nda_rum ),
      ghm AS
  ( SELECT ids_nda_rum,
           json_agg(ghmo) AS ghmo,
           count(1) AS total
   FROM
     ( SELECT ids_nda_rum,
              json_build_object( 'cd_ghm',cd_ghm, 'cd_ghs',cd_ghs,
'status',lib_statut_tr, 'dt_maj_rum_ghm',dt_maj_rum_ghm) AS ghmo
      FROM eds.nda_rum_ghm_tr
      LEFT JOIN eds.nda_rum_tr rum USING (ids_nda_rum)
      WHERE nda_rum_ghm_tr.ids_nda IN
          (SELECT ids_nda
           FROM sel)
        AND rum.cd_rum = 'RSS'
      ORDER BY dt_maj_rum_ghm) AS ghmo
   GROUP BY ids_nda_rum ),
      lab AS
  (SELECT ids_nda,
          json_agg(lab) AS labo,
          count(1) AS total
   FROM
     (SELECT ids_nda,
             json_build_object( 'valeur_type_tr',valeur_type_tr,
'dt_fait', dt_fait, 'unite',unite, 'cd_test_lab',cd_test_lab,
'valeur_sign_tr',valeur_sign_tr, 'valeur_num_tr',valeur_num_tr,
'valeur_text_tr',valeur_text_tr,
'valeur_abnormal_tr',valeur_abnormal_tr) AS lab
      FROM eds.fait_lab_tr
      WHERE ids_nda IN
          (SELECT ids_nda
           FROM sel)
      ORDER BY dt_fait) AS labo
   GROUP BY ids_nda),
      rum AS
  ( SELECT ids_nda,
           json_agg(rum) AS rumo,
           count(1) AS total
   FROM
     ( SELECT ids_nda,
              json_build_object( 'cd_rum',cd_rum, 'dt_deb_rum',
dt_deb_rum, 'dt_fin_rum', dt_fin_rum, 'diag',
json_build_object('total',diag.total,'diag',diag.diago), 'act',
json_build_object('total',act.total,'act',act.acto) ) AS rum
      FROM eds.nda_rum_tr
      LEFT JOIN diag USING (ids_nda_rum)
      LEFT JOIN act USING (ids_nda_rum)
      WHERE ids_nda IN
          (SELECT ids_nda
           FROM sel)
        AND cd_rum = 'RUM' ) AS rumo
   GROUP BY ids_nda),
      rss AS
  ( SELECT ids_nda,
           json_agg(rss) AS rsso,
           count(1) AS total
   FROM
     ( SELECT ids_nda,
              json_build_object( 'cd_rum',cd_rum, 'dt_deb_rss',
dt_deb_rum, 'dt_fin_rss', dt_fin_rum, 'ghm',
json_build_object('total',ghm.total,'ghm',ghm.ghmo), 'rum',
json_build_object('total',rum.total, 'rum',rum.rumo) ) AS rss
      FROM eds.nda_rum_tr
      LEFT JOIN ghm USING (ids_nda_rum)
      LEFT JOIN rum USING (ids_nda)
      WHERE ids_nda IN
          (SELECT ids_nda
           FROM sel)
        AND cd_rum = 'RSS' ) AS rss
   GROUP BY ids_nda),
      enc AS
  (SELECT 'Encounter' AS "resourceType",
          cd_nda AS "identifier",
          duree_hospit AS "length",
          lib_statut_nda_tr AS "status",
          lib_type_nda_tr AS "type",
          ids_pat,
          json_build_object('start', dt_deb_nda,'end', dt_fin_nda) AS
"appointment",
          json_build_object('total',lab.total, 'lab',lab.labo) AS lab,
          json_build_object('total',rss.total, 'rss',rss.rsso) AS rss
   FROM eds.nda_tr
   LEFT JOIN lab USING (ids_nda)
   LEFT JOIN rss USING (ids_nda)
   WHERE ids_nda IN
       (SELECT ids_nda
        FROM sel)
   ORDER BY dt_deb_nda ASC)
SELECT 'Bundle' AS "resourceType",
       count(1) AS total,
       array_to_json(array_agg(ROW)) AS encounter
FROM
  (SELECT 'Patient' AS "resourceType",
          ipp AS "identifier",
          nom AS "name",
          cd_sex_tr AS "gender",
          dt_nais AS "birthDate",
          json_build_array(enc.*) AS encounters
   FROM eds.patient_tr
   INNER JOIN enc USING (ids_pat) ) ROW;