How to Convert a MySQL Result Table to a JSON Array Using Only Built-in Functions?

Converting Result Table to JSON Array in MySQL with Plain Commands

You aim to transform the result table of a MySQL query into a JSON array without external dependencies. To achieve this, MySQL provides several functions that can assist you.

New Solution

By leveraging the power of JSON_ARRAYAGG() and JSON_OBJECT() functions, you can directly aggregate individual JSON objects for each row and group them into a single JSON array.

SELECT JSON_ARRAYAGG(JSON_OBJECT('name', name, 'phone', phone)) FROM Person;

Old Solution

Alternatively, you can use a combination of CONCAT() and GROUP_CONCAT() functions to construct the JSON array.

SELECT CONCAT(
    '[',
    GROUP_CONCAT(JSON_OBJECT('name', name, 'phone', phone)),
    ']'
)
FROM Person;

Both approaches effectively convert the result table into a valid JSON array that can be easily consumed by external applications or further processed within MySQL itself.

The above is the detailed content of How to Convert a MySQL Result Table to a JSON Array Using Only Built-in Functions?. For more information, please follow other related articles on the PHP Chinese website!