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"