How to Convert MySQL Result Tables into JSON Arrays?

Converting Result Tables to JSON Arrays in MySQL

In MySQL, converting result tables to JSON arrays can be achieved using various methods.

One approach involves using the JSON_ARRAYAGG() function, which can be employed in conjunction with the JSON_OBJECT() function. The JSON_ARRAYAGG() function aggregates rows into a JSON array, while the JSON_OBJECT() function creates JSON objects for each row.

For instance, the following query converts the result table you provided into a JSON array:

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

Alternatively, you can use the following query:

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

This query combines the results of the GROUP_CONCAT() function, which concatenates the individual JSON objects, with the CONCAT() function to add the surrounding square brackets needed for a valid JSON array.

Both approaches produce the desired JSON output:

[
  {
    "name": "Jack",
    "phone": 12345
  },
  {
    "name": "John",
    "phone": 23455
  }
]

The above is the detailed content of How to Convert MySQL Result Tables into JSON Arrays?. For more information, please follow other related articles on the PHP Chinese website!