How to convert result table to JSON array in MySQL
There are two "group by" functions for JSON called json_arrayagg, json_objectagg.
This problem can be solved with:
SELECT json_arrayagg(
json_merge(
json_object('name', name),
json_object('phone', phone)
)
) FROM person;
This requires MySQL 5.7+.
If you're stuck on MySQL 5.6 like me, try this:
SELECT
CONCAT(
'[',
GROUP_CONCAT(
CONCAT(
'{"name":"', name, '"',
',"phone":"', phone, '"}'
)
),
']'
) as json
FROM person
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.