How to convert result table to JSON array in MySQL
New solution:
Built using Your great comments, thanks!
SELECT JSON_ARRAYAGG(JSON_OBJECT('name', name, 'phone', phone)) from Person;Old solution:
With help from @Schwern I managed to put up this query, which seems to work!
SELECT CONCAT( '[', GROUP_CONCAT(JSON_OBJECT('name', name, 'phone', phone)), ']') FROM person;
You can use json_object to get rows as JSON objects.
SELECT json_object('name', name, 'phone', phone)FROM person;This won't put them in an array, or put commas between them. You'll have to do that in the code which is fetching them.
If you're stuck on MySQL 5.6 like me, try this:
SELECT CONCAT( '[', GROUP_CONCAT( CONCAT( '{"name":"', name, '"', ',"phone":"', phone, '"}' ) ), ']' ) as jsonFROM person