json_norm_query.py

json_norm_query.py

answer_options_table = Q()\
    .tables(ModuleAnswerOption.s)\
    .fields(ModuleAnswerOption.s.pk, ModuleAnswerOption.s.answer, ModuleAnswerOption.s.is_correct) \
    .where(ModuleAnswerOption.s.question == ModuleQuestion.s.pk)\
    .as_table('g')

answer_options_json_q = Q() \
    .tables(answer_options_table) \
    .fields(func.json_agg(answer_options_table).as_('options'))

questions_q = Q()\
    .tables(ModuleQuestion.s) \
    .fields(
        ModuleQuestion.s.pk,
        ModuleQuestion.s.title,
        answer_options_json_q
    ) \
    .where(ModuleQuestion.s.module_id == module_id)

res = fetch_many(questions_q)
    
# Results
[
    {
        "id": 4,
        "title": "Q1",
        "options": [
            {
                "id": 7,
                "answer": "yeah",
                "is_correct": true
            },
            {
                "id": 8,
                "answer": "no",
                "is_correct": false
            }
        ]
    },
    {
        "id": 5,
        "title": "q2",
        "options": [
            {
                "id": 9,
                "answer": "yeah",
                "is_correct": true
            },
            {
                "id": 10,
                "answer": "no",
                "is_correct": false
            }
        ]
    }
]