How Can I Convert a MySQL Result Table into a JSON Array Using SQL?

Creating JSON Arrays from Result Tables in MySQL

Consider the task of converting a result table into a JSON array using solely MySQL commands. For instance, given a table with the columns "name" and "phone":

| name | phone |
| Jack | 12345 |
| John | 23455 |

The desired JSON output would resemble:

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

Modern Solution:

Utilizing the JSON_ARRAYAGG() function, the following query accomplishes this conversion:

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

Legacy Solution:

Employing the GROUP_CONCAT() and JSON_OBJECT() functions, this query generates the JSON array:

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

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