nested_a.sql

-- name: get_notes
-- record_class: NoteProduct
SELECT note.product_id, note.name , product_name, manufacturer
FROM nested.product
         join nested.note on product.note = note.product_id;

-- name: get_notes_json
select coalesce(array_to_json(array_agg(row_to_json(response))),'[]')::character varying AS BODYFROM from (
        SELECT (note.product_id , note.name) as note , product_name, manufacturer
FROM nested.product
         join nested.note on product.note = note.product_id
) response

nested_models.py

import asyncio
import json

import aiosql
import asyncpg
from asyncpg import Connection
from pydantic import BaseModel


class Note(BaseModel):
    product_id: int
    name: str

    
class Product(BaseModel):
    note: Note
    product_name: str
    manufacturer: str


class NoteProduct(Note):
    product_name: str
    manufacturer: str


class NoteJson(BaseModel):
    f1: int
    f2: str


class ProductJson(BaseModel):
    note: NoteJson
    product_name: str
    manufacturer: str


class NoteProductJson(NoteJson):
    product_name: str
    manufacturer: str


async def main():
    q = aiosql.from_path("./a.sql", "asyncpg", record_classes={"NoteProduct": NoteProduct})
    conn: Connection = await asyncpg.connect("postgresql://postgres:postgres@localhost:5438/postgres")

    response = await q.get_notes(conn)
    notes = [Note(product_id=np.product_id, name=np.name) for np in response]
    products = [Product(note=note, product_name=r.product_name, manufacturer=r.manufacturer) for note, r in zip(notes, response)]
    print(products)

    response = await q.get_notes_json(conn)
    d = json.loads(response[0]["bodyfrom"])
    productsjson = [ProductJson(note=NoteJson(**d[i]["note"]), product_name=d[i]["product_name"], manufacturer=d[i]["manufacturer"]) for i, v in enumerate(d)]
    print(productsjson)
    await conn.close()

if __name__ == '__main__':
    asyncio.run(main())

nested_schema.sql

create schema nested;
create table nested.product
(
    note    int,
    product_name text,
    manufacturer text
);

create table nested.note
(
    product_id    int primary key,
    name text
);

alter table nested.product
	add constraint product_note_product_id_fk
		foreign key (note) references nested.note (product_id);