How do you write MySQL query using JSON_OBJECTAGG on SQLAlchemy's SQL Expression Language?

Question

I'm using SQLAlchemy on MySQL 8.0. I'm trying to compose a query which uses JSON_OBJECTAGG function on the SQL Expression Language. Since it is predefined function in MySQL, I'm wondering if there is canonical way for achieving this purpose.

Question

• What is the recommended way to compose a query using JSON_OBJECTAGG function on SQL Expression Language?

Answer 1

There are no restrictions¹ to what SQL functions can be called using func. Given the example from MySQL documentation:

mysql> SELECT o_id, attribute, value FROM t3;

+------+-----------+--------+
| o_id | attribute | value  |
+------+-----------+--------+
|    2 | color     | red    |
|    2 | fabric    | silk   |
|    3 | color     | green  |
|    3 | shape     | square |
+------+-----------+--------+
4 rows in set (0.00 sec)

the query:

SELECT o_id, JSON_OBJECTAGG(attribute, value)

   FROM t3 GROUP BY o_id;

would be expressed in SQLAlchemy SQL Expression Language as:

select([t3.c.o_id, func.json_objectagg(t3.c.attribute, t3.c.value)]).\
    group_by(t3.c.o_id)

---

¹ In practice there are some restrictions. func cannot be used to generate function calls using named notation easily, for example.