How to Convert a MySQL Result Table to a JSON Array Using Plain SQL Commands?

MySQL Conversion of Result Table to JSON Array Using Plain Commands

In MySQL, it is possible to convert a result table into a JSON array using built-in functions. Consider the following query:

SELECT name, phone FROM person;

Which returns the following result table:

name	phone
Jack	12345
John	23455

New Solution:

Utilizing JSON_ARRAYAGG and JSON_OBJECT, the following query achieves the desired JSON output:

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

The JSON_ARRAYAGG function aggregates multiple JSON objects into an array, while the JSON_OBJECT function creates an object with the specified key-value pairs.

Old Solution:

Alternatively, you can use the CONCAT, GROUP_CONCAT, and JSON_OBJECT functions to construct the JSON array:

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

The CONCAT function concatenates the strings, GROUP_CONCAT aggregates the JSON objects into a comma-separated list, and JSON_OBJECT creates the individual objects.

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