Array_agg on 2 columns with output not recognized as list

Question

I'm using array_agg in one of my Postgresql query in Python using psycopg2.

The issue I'm finding is when looping through the resulting rows. The generated column from the query doesn't understand that one column is a list of lists. Instead it thinks it's a list of str.

Here is an example of the database:

+---------+-------------+-----------------+
| student | grade_value |  grade_comment  |
+---------+-------------+-----------------+
| Paul    | 1           | Very good       |
| John    | 1           | Very good       |
| John    | 2           | Average         |
| Mark    | 1           | Very good       |
| Mark    | 3           | Could do better |
| Mark    | 1           | Very good       |
+---------+-------------+-----------------+

The query I'm making:

    connection = psycopg2.connect(ls.CONNECTION_STRING)

    cursor = connection.cursor(cursor_factory=RealDictCursor)

    cursor.execute(
        "SELECT student, array_agg('(' || grade_value || ',' || grade_comment || ')') as grades"
        "FROM random_table"
        "GROUP BY student"
    )

    students_grades = cursor.fetchall()
    # This returns something like: RealDictRow([('student', John), ('grades', ['(1,Very good)', '(2,Average)'])]), RealDictRow([('student', Paul), ('grades', ['(1,Very good)'])])

    for student in students_grades:
       for grade in student['grades']:
           print(grade)
           print(type(grade))

The values of print(grade) at the end have this format: (1,Very good) But the query says that the type is a string. As a result I can't access the grade comment by just typing grade[1]. It thinks the grade is a string.

Would you have any idea on how to fix this?

Answer 1

You dont need to concentrate a string in array_agg, just pass the columns into an array.
Psycopg2 will cast the correct types between postgres and python, as you can see in the output students_grade['grades'] is fetched as a list:

cursor.execute("""
select
    student,
    array_agg(array[grade_value, grade_comment]) as grades
from random_table
group by student""")

students_grades = cursor.fetchall()

for students_grade in students_grades:
    print(students_grade['student'])

    for grade in students_grade['grades']:
        print("%s %s" % (type(grade), grade))

Output:

Tom
<class 'list'> ['2', 'Good']
<class 'list'> ['3', 'Very good']
John
<class 'list'> ['2', 'Very good']

Edit:

In case you need to aggregate different types, you could aggregate into a JSON object:

cursor.execute("""
select
    abc as student,
    json_agg(json_build_object('grade', grade_value, 'comment', array[grade_comment])) as grades
from foo
group by student""")

Output:

Tom
<class 'dict'> {'grade': 2, 'comment': ['Good']}
<class 'dict'> {'grade': 3, 'comment': ['Very good']}
John
<class 'dict'> {'grade': 2, 'comment': ['Very good']}