YAML -> SQL

YAML

    

declare:
  select_many:
    field: coalesce(jsonb_agg($1), '[]')
    as: $1
  select_json:
    field: to_jsonb ("$1".*)
    as: result
  has_many:
    query:
      select:
      - apply: ["select_many", "result"]
      from: $1
      where:
      - operator: =
        left: $2        
        right: $3       
    alias: lateral_$1
    type: left_join_lateral
    on: true
  belongs_to:
    query:
      select:
      - field: to_jsonb ("$1".*)
        as: result
      from: "$1"
      where:
      - operator: =
        left: |
          $2
        right: |
          $3
      limit: 1
    alias: lateral_$1
    type: left_join_lateral
    on: true
  select_all:
    field: |
      to_jsonb ("$1".*) 
      || 
      jsonb_build_object(
        $2::text, 
        "lateral_$2".result, 
        $3::text, 
      "lateral_$3".result)
    as: result
main:
  select:
  - apply: ["select_many", "result"]
  from:
    alias: sq_books
    select:
    - apply: ["select_all", "books", "authors", "tags"]
    from: books  
    join:
    - apply: ["belongs_to", "authors", "id", "books.authorId"]
    - apply: ["has_many", "tags", "bookId", "books.id"]