Optimising N+1 problem in PG with "json_agg"

pg_optimisation.sql

WITH question_options_f AS
  ( SELECT id,
           question_id,
           name,
           fixed,
           nota,
           name_html,
           name_raw
   FROM question_options
   WHERE deleted_at IS NULL
   ORDER BY ROW ASC),
     questions_with_options AS
  ( SELECT questions.*,
           to_char(questions.created_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MSZ') AS created_at,
           to_char(questions.updated_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MSZ') AS updated_at,
           json_agg(options_q.*) AS question_options
   FROM questions,

     (SELECT *
      FROM question_options_f) AS options_q
   WHERE options_q.question_id = questions.id
   GROUP BY questions.id),
     text_nodes_of AS
  ( SELECT tn.*,
           to_char(tn.created_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MSZ') AS created_at,
           to_char(tn.updated_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MSZ') AS updated_at
   FROM text_nodes tn),
     conditions_of AS
  ( SELECT inner_conditions.*,
           json_build_object(inner_conditions.operator, inner_conditions.human_operator) AS
   OPERATOR,
           concat( row_to_json(row(inner_conditions.source))->'f1'->'name_raw'::varchar ,' ',inner_conditions.human_operator,' ' ,row_to_json(row(inner_conditions.operand))->'f1'->'name_raw'::varchar ) AS equation ,
           json_build_array( row_to_json(row(inner_conditions.source))->'f1'->'name_raw'::varchar ,inner_conditions.human_operator::varchar ,row_to_json(row(inner_conditions.operand))->'f1'->'name_raw'::varchar ) AS equation_array
   FROM
     ( SELECT conditions.id,
              rule_id,
              element_id,
              element_type,
              element_type AS TYPE,
              uuid_element_id,
              uuid_element_type,
              coalesce(question_as_element.question_type) AS source_type,
              coalesce(value_type) AS operand_type,
              coalesce(question_as_element) AS SOURCE,
              coalesce(question_options_as_target) AS operand,
      OPERATOR,
              (CASE
                   WHEN conditions.operator = 'eq' THEN 'equals'
                   WHEN
                        OPERATOR = 'neq' THEN 'does not equal'
                   ELSE 'X'
               END) AS human_operator
      FROM conditions
      LEFT OUTER JOIN questions_with_options question_as_element ON (question_as_element.id = uuid_element_id
                                                                     AND uuid_element_type = 'Question')
      LEFT OUTER JOIN question_options question_options_as_target ON (question_options_as_target.id = uuid_target_id
                                                                      AND uuid_target_type = 'QuestionOption')
      ORDER BY conditions.row ASC ) AS inner_conditions),
     rules_of AS
  ( SELECT r.id,
           ROW,
           r.element_type,
           r.element_id,

     ( SELECT COALESCE(json_agg(condition.*) FILTER (
                                                     WHERE condition.id IS NOT NULL), '[]') AS conditions
      FROM
        (SELECT *
         FROM conditions_of
         WHERE conditions_of.rule_id = r.id) condition ) AS conditions
   FROM rules r
   GROUP BY r.id
   ORDER BY r.row ASC),
     nodes_of AS
  ( SELECT n.id,
           n.block_id,
           n.element_type,
           n.element_type AS TYPE,
           n.element_id,
           (CASE
                WHEN question_as_element IS NULL THEN row_to_json(text_node_as_element)
                ELSE row_to_json(question_as_element)
            END ) AS element,

     ( SELECT COALESCE(json_agg(rule.*) FILTER (
                                                WHERE rule.id IS NOT NULL), '[]') AS rules
      FROM
        (SELECT *
         FROM rules_of
         WHERE rules_of.element_type = 'Node'
           AND rules_of.element_id = n.id) RULE ) AS rules
   FROM nodes n
   LEFT OUTER JOIN text_nodes_of text_node_as_element ON (text_node_as_element.id = n.element_id
                                                          AND n.element_type = 'TextNode')
   LEFT OUTER JOIN questions_with_options question_as_element ON (question_as_element.id = n.element_id
                                                                  AND n.element_type != 'TextNode')
   WHERE n.deleted_at IS NULL
   ORDER BY n.row ASC),
     blocks_of AS
  ( SELECT b.id,
           b.list_id,
           b.name,
           b.row ,

     ( SELECT COALESCE(json_agg(rule.*) FILTER (
                                                WHERE rule.id IS NOT NULL), '[]') AS rules
      FROM
        (SELECT *
         FROM rules_of
         WHERE rules_of.element_type = 'Block'
           AND rules_of.element_id = b.id) RULE ) AS rules,

     ( SELECT json_agg(node.*) AS nodes
      FROM
        (SELECT *
         FROM nodes_of
         WHERE nodes_of.block_id = b.id) node ) AS nodes
   FROM blocks b
   WHERE b.deleted_at IS NULL
   GROUP BY b.id
   ORDER BY b.row ASC),
     categories AS
  ( SELECT id,
           name,
           slug
   FROM categories
   WHERE deleted_at IS NULL )
SELECT row_to_json(results) AS list
FROM
  ( SELECT l.id,
           l.slug,
           l.row,

     (SELECT CASE
                 WHEN l.state = 0 THEN 'draft'
                 WHEN l.state = 1 THEN 'active'
                 WHEN l.state = 2 THEN 'archived'
                 ELSE 'deleted'
             END)::varchar AS state,
           to_char(l.created_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MSZ') AS created_at,
           to_char(l.updated_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.MSZ') AS updated_at,
           l.has_random_order ,

     ( SELECT COALESCE(json_agg(block.*) FILTER (
                                                 WHERE block.id IS NOT NULL), '[]') AS blocks
      FROM
        (SELECT *
         FROM blocks_of
         WHERE blocks_of.list_id = l.id) BLOCK ) AS blocks ,

     ( SELECT COALESCE(json_agg(rule.*) FILTER (
                                                WHERE rule.id IS NOT NULL), '[]') AS rules
      FROM
        (SELECT *
         FROM rules_of
         WHERE rules_of.element_type = 'List'
           AND rules_of.element_id = l.id) RULE ) AS rules
   FROM LISTS l
   WHERE l.id = 61
     AND l.state IN (0,
                     1,
                     2)
     AND l.deleted_at IS NULL
   GROUP BY l.id
   LIMIT 1) AS results