YAML -> SQL

YAML

    
main:
  with:
  - apply: ["cte_without_join", "question"]
  - apply: ["cte", "section", "questions"]
  - apply: ["cte", "form", "sections"]
  select:
  - field: row_to_json(forms)
  from: forms
declare:
  cte_without_join:
    alias: $1s
    from: $1
    select:
    - field: $1.*
    group:
    - field: $1.id
    order:
    - field: $1.id
  cte:
    alias: "$1s"
    select:
    - field: "$1.*"
    - field: json_agg($2)
      as: "$2"
    from: "$1"
    join:
    - from: "$2"
      type: left_join
      on:
      - operator: =
        left: "$2.$1_id"
        right: "$1.id"
    group:
    - field: "$1.id"
    order:
    - field: "$1.id"

SQL

    
WITH "questions" AS (
  SELECT
    question.*
  FROM
    "question" "questions"
  GROUP BY
    question.id
  ORDER BY
    question.id
),
"sections" AS (
  SELECT
    section.*,
    json_agg(questions) AS questions
  FROM
    "section" "sections"
    LEFT JOIN "questions" ON questions.section_id = section.id
  GROUP BY
    section.id
  ORDER BY
    section.id
),
"forms" AS (
  SELECT
    form.*,
    json_agg(sections) AS sections
  FROM
    "form" "forms"
    LEFT JOIN "sections" ON sections.form_id = form.id
  GROUP BY
    form.id
  ORDER BY
    form.id
)
SELECT
  row_to_json(forms)
FROM
  "forms"