How can I pull out a row in a GROUP BY Django query?

user_13668802 0
Viewed 114

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?

mysql django-models django database
1 Answers

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;
user_51082200
Related