I have a table like this:
EVENT
-------------
user_id thumbnail_name
0 event/abc.jpg
0 event/efg.jpg
0 event/xyz.jpg
1 event/lmn.jpg
1 event/opq.jpg
2 event/rsf.jpg
How can I GROUP BY user_id and retrieve an arbitrary thumbnail? Expected output:
[ {user_id: 0, thumbnail_name: event/abc.jpg},
{user_id: 1, thumbnail_name: event/lmn.jpg},
{user_id: 2, thumbnail_name: event/rsf.jpg},
]
Is this even possible in SQL much less Django ORM?
Use FOR JSON AUTO in SQl Server
SELECT *
FROM EVENT
FOR JSON AUTO
If you expect to return just a single row, then you can add Without_Array_Wrapper after FOR JSON AUTO with a comma after the AUTO keyword, and you will get an object instead of an array:
SELECT *
FROM EVENT
FOR JSON AUTO, Without_Array_Wrapper;
in MySQL
SELECT json_object('user_id', user_id, 'thumbnail_name', thumbnail_name)
FROM EVENT;
Fuhrer Modifications
SELECT JSON_ARRAYAGG(JSON_OBJECT('user_id', user_id, 'thumbnail_name', thumbnail_name)) from EVENT;